Macro - Copy all worksheets in C:\Test & Paste 2 New Wb

[MikeL]

Hi,

I have about 20 .XLS workbooks in the folder C:\Test each with varying degree of worksheets in them (typically 2 to 5 sheets).

Can VBA be executed to copy all sheets in the workbooks and paste them all into a New Workbook?

Thanks in advance,
Mike

 

[Norie]

Yes.

Perhaps something like this:

Dim I As Integer
Dim wbDest As Workbook
Dim wbSource As Workbook
Dim ws As Worksheet
    
    Application.ScreenUpdating = False

    Set wbDest = ThisWorkbook
    ' or alternativwly Set wbDest = Workbooks.Add

     With Application.FileSearch
        .NewSearch
        .LookIn = "C:\TEST"
        .FileType = msoFileTypeExcelWorkbooks
        .Execute
        
        For I = 1 To .FoundFiles.Count
        
            Set wbSource = Workbooks.Open(.FoundFiles(I))
            
            For Each ws In wbSource.Worksheets
                
                ws.Copy After:=wbDest.Worksheets(wbDest.Worksheets.Count)
                
            Next ws
            
            wbSource.Close
        Next I
    End With
    Application.ScreenUpdating = True

 

[MikeL]

thanks!

Works great! Thank you very much Norie.