Open all workbooks to perform an action

[camandab]

I need to a workbook in a specified folder, copy any sheet that contains "Dec" to a new workbook, save and repeat for all worksbooks in a folder

Here is the code I have so far and it only opens all the files in the folder I specified but doesn't take the action to copy the sheets I have specified. Any ideas??

Dim lCount As Long
Dim wbResults As Workbook
Dim wbCodeBook As Workbook


Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableEvents = False

On Error Resume Next
    Set wbCodeBook = ThisWorkbook
        With Application.FileSearch
            .NewSearch
            'Change path to suit
            .LookIn = "C:\...\My Documents\...Recs 2009"
            .FileType = msoFileTypeExcelWorkbooks
            'Optional filter with wildcard
            .Filename = "MOC*.xls"
                If .Execute > 0 Then 'Workbooks in folder
                    For lCount = 1 To .FoundFiles.Count 'Loop through all
                        'Open Workbook x and Set a Workbook variable to it
                        Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                        

'2.Copy Dec worksheets into new wb

Dim ws As Worksheet
Dim arrShts()
Dim I As Long
 
    For Each ws In Worksheets
        If LCase(ws.Name) Like "*dec*" Then
            ReDim Preserve arrShts(I)
            arrShts(I) = ws.Name
            I = I + 1
        End If
    Next ws
    
    Worksheets(arrShts()).Copy

Next
End If
End With

MsgBox ("Done")


'
End Sub

 

[pboltonchina]

Try changing

Worksheets(arrShts()).Copy

to

Worksheets(arrShts()).Copy After:=Workbooks(wbResults).Worksheets(Workbooks(wbResults).Worksheets.Count)
                    Windows(wbCodeBook).Activate

 

[camandab]

Thanks for the quick reply. It still did not copy the sheets to a new workbook. Any other suggestions? Thanks!!

 

[pboltonchina]

I assume that you have the 2 workbooks wbResults and wbCodeBook open together? Or do they have different names?

 

[camandab]

They have different names

 

[pboltonchina]

You need to use the correct workbook names in the macro instead of wbResults and wbCodeBook or the macro can't rference them.