Copying Sheets from Unopened Workbooks

[BrittKnee]

Hi All. I am trying to copy the sheet containing "EEs" from multiple workbooks in one folder to the active workbook. The code I have runs, but doesn't actually copy the sheets. Any and all help is appreciated!

Option Explicit

Sub Combine_Dept_Files()

Dim FolderPath, Filename As String
Dim ws As Worksheet
Dim wkb As Workbook
Dim var As Variant

Application.ScreenUpdating = False
Application.DisplayAlerts = False


'Copy Worksheets to Master File

    FolderPath = "S:\9Box_Output\Department Reports\"
    Filename = Dir(FolderPath & "*.xlsm*")
    Do While Filename <> ""
    Workbooks.Open Filename:=FolderPath & Filename, ReadOnly:=True
        For Each ws In ActiveWorkbook.Sheets
            For Each var In Array("EEs*")
                If InStr(ws.Name, CStr(var)) Then ws.Copy After:=ThisWorkbook.Sheets(2)
            Next var
    Next ws
    Workbooks(Filename).Close
    Filename = Dir()
    Loop

End Sub

 

[jkpieterse]

Do the files contain at least one worksheet which has a name starting with "EEs" (case sensitive!)?

 

[I_know_nuffin]

what is this supposed to do?


For Each var In Array("EEs*")
If InStr(ws.Name, CStr(var)) Then ws.Copy After:=ThisWorkbook.Sheets(2)
Next var


surely

If InStr(ws.Name, CStr(var)) Then ws.Copy After:=ThisWorkbook.Sheets(2)

would work without the loop

 

[I_know_nuffin]

what is this supposed to do?


For Each var In Array("EEs*")
If InStr(ws.Name, CStr(var)) Then ws.Copy After:=ThisWorkbook.Sheets(2)
Next var


surely

If InStr(ws.Name, CStr(var)) Then ws.Copy After:=ThisWorkbook.Sheets(2)

would work without the loop

sorry it should be something like below
If InStr(ws.Name , "EEs*") > 0 Then ws.Copy After:=ThisWorkbook.Sheets(2)

 

[BrittKnee]

Do the files contain at least one worksheet which has a name starting with "EEs" (case sensitive!)?

Yup, just double checked to make sure. I even tried naming them all the same name and hard coding the sheet name to see if it had something to do with that. Thanks in advance!

 

[BrittKnee]

sorry it should be something like below
If InStr(ws.Name , "EEs*") > 0 Then ws.Copy After:=ThisWorkbook.Sheets(2)

It's still not copying the sheets in. There's no error, so I am at a loss.

 

[jkpieterse]

Change this line:

If InStr(ws.Name, CStr(var)) Then ws.Copy After:=ThisWorkbook.Sheets(2)

to:

If ws.Name Like CStr(var) Then ws.Copy After:=ThisWorkbook.Sheets(2)

 

[I_know_nuffin]

have you tried stepping through the code to see what happens ?

 

[BrittKnee]

Change this line:

If InStr(ws.Name, CStr(var)) Then ws.Copy After:=ThisWorkbook.Sheets(2)

to:

If ws.Name Like CStr(var) Then ws.Copy After:=ThisWorkbook.Sheets(2)

This worked! Thanks.