Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 81
  • 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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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