Solved

Excel VBA How to copy sheet2 from one file into Output.xls

Posted on 2016-10-19
2
45 Views
Last Modified: 2016-10-19
How can I loop through all files in folder and read all excel files and copy "sheet2" and copy it to Output excel file as new sheet.  New sheet name will be the same as file name it copy from.

For Example
/File1.xls   has three sheets (sheet1 , sheet2 and sheet3)
/File2.xls   has three sheets (sheet1 , sheet2 and sheet3)
/File3.xls   has three sheets (sheet1 , sheet2 and sheet3)

Output
Output.xls  has three sheets (File1, File2, File3)

Output.xls (SheetName will be File1, File2, File3)
0
Comment
Question by:Bharat Guru
2 Comments
 
LVL 33

Accepted Solution

by:
Norie earned 500 total points
ID: 41850700
Perhaps something like this will get you started.
Sub ImportSheet2()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim strPath As String
Dim strFilename As String

    Set wbDst = ThisWorkbook
    
    strPath = "C:\Test\" ' change to name of folder with files you want to import from
    
    strFilename = Dir(strPath & "*.xls*")
    
    While Len(strFilename) <> 0
        Set wbSrc = Workbooks.Open(strPath & strFilename)
        
        wbSrc.Sheets("Sheet2").Copy After:=wbDst.Sheets(wbDst.Sheets.Count)
        
        wbDst.Sheets(wbDst.Sheets.Count).Name = wbSrc.Name
        
        wbSrc.Close SaveChanges:=False
        
        strFilename = Dir
    Wend
    
End Sub

Open in new window

0
 

Author Closing Comment

by:Bharat Guru
ID: 41850781
Thanks
0

Featured Post

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

PaperPort has a feature called the "Send To Bar". It provides a convenient, drag-and-drop interface for using other installed software, such as Microsoft Office. However, this article shows that the latest Office 2016 apps (installed with an Office …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question