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.


Code:
--------------------

--------------------
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
Code:
--------------------

--------------------



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.


--
hachiroku
------------------------------------------------------------------------
hachiroku's Profile: http://www.excelforum.com/member.php...o&userid=34039
View this thread: http://www.excelforum.com/showthread...hreadid=543653