Solved

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

Posted on 2016-10-19
2
26 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

759 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now