I have attempted to create a macro that will - for each selection under auto-filter in column A9 - post the corresponding information for 'the selection' and copy and paste it into a template on another workbook then save it.
the selections in auto filter in A9 are FBA to FBP
and each selection will have their own new report made by pasting on the template.
Here i have posted a part of my attempted code
, which shows FBA and FBB, the rest of the code just looks like the second part, but with changed FB*. It works fine up to FBG, which pastes everything from FBA to FBG onto the new template.
Sub Macro2()
'
' Macro2 Macro
'
' Keyboard Shortcut: Ctrl+a
'
Dim curbook As Workbook
Set curbook = ActiveWorkbook
Selection.AutoFilter Field:=1, Criteria1:="FBA"
Range("A10:F50").Select
Selection.Copy
Windows("PERSONAL.XLS").Activate
Windows("Large Amount Report By individual ARM Templete.xls").Activate
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir _
"S:\Wkgrps\Cbbfs\Product Development - Deposit\Large Amount Report\By ARM\new"
ActiveWorkbook.SaveAs Filename:= _
"S:\Wkgrps\Cbbfs\Product Development - Deposit\Large Amount Report\By ARM\new\Large Amount Report FBA.xls" _
, FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
curbook.Activate
Selection.AutoFilter Field:=1, Criteria1:="FBB"
Range("A10:F50").Select
Selection.Copy
Windows("Large Amount Report FBA.xls").Activate
Range("A10").Select
ActiveSheet.Paste
Application.CutCopyMode = False
ChDir _
"S:\Wkgrps\Cbbfs\Product Development - Deposit\Large Amount Report\By ARM\new"
ActiveWorkbook.SaveAs Filename:= _
"S:\Wkgrps\Cbbfs\Product Development - Deposit\Large Amount Report\By ARM\new\Large Amount Report FBB.xls" _
, FileFormat:=xlExcel9795, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False
I am wondering what is the best way to rewrite this code, and also have it ignore selections when they are not available for the current months, for example FBD was not available this month but it will be next month. Right now this code will spit out an FBD report with all the selections even though, there is no FBD to select in autofilter column.
Bookmarks