Combining parts of 3 workbooks

[Sandler]

I have 3 different workbooks created on a monthly basis that shows payroll.
They each have a title with the last word in the title being the month.
Hypothetically for May, the 3 workbooks will be named "ABC May", "DEF May", "GHI May"

Column M (13th), shows the payroll code. I need column M to be filtered by the word "Incentive"
Each workbook has only 1 sheet, which has a tab title of "ersRequestHandler"
I need to pull all the "Incentive"s into a another workbook.

The 3 workbooks are placed in a separate folder monthly, so in the code I would need to make reference to that specific folder.

[Sandler]

I now have the shell to this code that loops through all the workbooks in the directory.


Sub CommentLength()
Dim wb As Workbook, wb2 As Workbook
Dim myPath As String, myFile As String, myExtension As String
Dim FldrPicker As FileDialog


' Optimize Macro Speed
Application.ScreenUpdating = False
Application.EnableEvents = False
Application.Calculation = xlCalculationManual


' Sets wb as the master workbook
Set wb = ThisWorkbook


' Retrieve Target Folder Path From User
Set FldrPicker = Application.FileDialog(msoFileDialogFolderPicker)


With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With


' In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings


' Target File Extension (must include wildcard "*")
myExtension = "*.xlsm"


' Target Path with Ending Extention
myFile = Dir(myPath & myExtension)


' Loop through each Excel file in folder
Do While myFile <> ""
' Set variable equal to opened workbook
Set wb2 = Workbooks.Open(FileName:=myPath & myFile)

' With Sheet1 of the new workbook
With wb2.Sheets(1)

****************************************
THE CODE TO RUN PER WORKBOOK GOES HERE
****************************************

End With
' Close new workbook without saving
wb2.Close False
' Get next file name
myFile = Dir
' Next workbook in folder
Loop


' Message Box when tasks are completed
MsgBox "Task Complete!"


ResetSettings:
' Reset Macro Optimization Settings
Application.EnableEvents = True
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True


End Sub

[Sandler]

I should be getting this answered shortly. So, feel free to skip this question.

Thanks :)

[SamT]

Answered where? What is multiposting? (http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3)

You can consolidate

With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo NextCode
myPath = .SelectedItems(1) & "\"
End With


' In Case of Cancel
NextCode:
myPath = myPath
If myPath = "" Then GoTo ResetSettings


With FldrPicker
.Title = "Select A Target Folder"
.AllowMultiSelect = False
If .Show <> -1 Then GoTo ResetSettings ' In Case of Cancel
myPath = .SelectedItems(1) & "\"
End With
I usually use GracefulExit as the line label before restoring Application settings. That term includes killing some objects and other good practices



' Target File Extension (must include wildcard "*")
myExtension = "*.xlsm"

' Target Path with Ending Extention
myFile = Dir(myPath & myExtension)
Can be

'Dim Mon as String
Mon = Format(DateAdd("m", Date, -1), "mmm") Returns 3 letter name of month before now.
'Don't use if you might skip a month

' Target Path with Ending Extention
myFile = Dir(myPath & "*" & Mon & ".xls*") 'Returns xls, xlsm, xlsx, xlsb files
'Or just
myFile = Dir(myPath & "*.xlsm")


"ThisWorkbook" is the standard CodeName for the workbook Object in an Excel file. It can be changed in an given workbook. "Me" is the VBA Workbook variable that always points to that workbook Object when used in ThisWorkbook or a standard Module.

Me.SaveAs . . . . Used in a UserForm, "Me" points to the UserForm, and in a Class Module, it points to the Object instantiated from that Class Module.


One question: where do we put the filtered data?

[Sandler]

Thanks SamT, you are awesome. The information would go to a blank workbook. Let's say the name of that workbook is SamT. The months may be fully spelled out, can I put a wildcard after the first 3 letters of the month? Thanks Again :)

[SamT]

The information would go to a blank workbook.
You don't need "ThisWorkbook" in any form in your code.


can I put a wildcard after the first 3 letters of the month?
Yes, you can put a wildcard anywhere the pattern of the name varies.

Name = "Payroll Report for the*Qtr Of*.xls*"

[snb]

Create 3 querytables to the respective files.