?
Solved

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

Posted on 2016-10-19
2
Medium Priority
?
62 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 34

Accepted Solution

by:
Norie earned 2000 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
My attempt to use PowerShell and other great resources found online to simplify the deployment of Office 365 ProPlus client components to any workstation that needs it, regardless of existing Office components that may be needing attention.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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