• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 78
  • Last Modified:

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

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
Bharat Guru
Asked:
Bharat Guru
1 Solution
 
NorieVBA ExpertCommented:
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
 
Bharat GuruAuthor Commented:
Thanks
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now