Setting Up a Macro that will work with files of different names

[HobbesNYC]

Hello All,

I'm trying to build a Macro that will automate the process of pulling information from another workbook. Say "August Accounts" from "August Returns". The information is always in the same place, however, each month, when the new books come out, I don't want through the code line by line by line renaming each file. I have the code set up as so (it repeats some 40 times)


Range("N6").Select
Windows("Draft Copy of CAP-AWM_201206.xlsx").Activate
ActiveSheet.Range("$A$1:$BY$2432").AutoFilter Field:=75, Criteria1:=Workbooks("Draft Copy of Business Reporting_Jun2012_lg (3).xlsx").Sheets("PB ECR Exceptions by Region").Range("$A$6")
Columns("BS:BS").Select
Selection.End(xlDown).Select
Selection.Copy
Windows("Draft Copy of Business Reporting_Jun2012_lg (3).xlsx").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False



Any ideas? I'm thinking maybe I should give a message box asking to name the file and use that as a variable or something but I don't know how to do that.

Thank you!!

 

[John Davis]

You can identify the name of the workbooks as follows, if only the dates change:

Sub HobbesNYC()

Range("N6").Select
Workbooks("Draft Copy of CAP-AWM_" & Format(DateAdd("m", 0, Date), "yyyymm") & ".xls").Activate
ActiveSheet.Range("$A$1:$BY$2432").AutoFilter Field:=75, Criteria1:=Workbooks("Draft Copy of Business Reporting_Jun2012_lg (3).xlsx").Sheets("PB ECR Exceptions by Region").Range("$A$6")
Columns("BS:BS").Select
Selection.End(xlDown).Select
Selection.Copy
Workbooks("Draft Copy of Business Reporting_" & Format(DateAdd("m", 0, Date), "mmmyyyy") & "_lg (3).xls").Activate
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False

End Sub

Note the 0 in the DateAdd format indicates the current month, -1 would mean the previous month, 1 next month and so on.

 

[HobbesNYC]

Wow, perfect. Thank you very much

 

[John Davis]

You're welcome. Glad to help out.