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

You may want to look at the way Ron de Bruin and Debra Dalgleish approached it:

Ron de Bruin's EasyFilter addin:
http://www.rondebruin.nl/easyfilter.htm

Code from Debra Dalgleish's site:
http://www.contextures.com/excelfiles.html

Create New Sheets from Filtered List -- uses an Advanced Filter to create
separate sheet of orders for each sales rep visible in a filtered list; macro
automates the filter. AdvFilterRepFiltered.xls 35 kb

Update Sheets from Master -- uses an Advanced Filter to send data from
Master sheet to individual worksheets -- replaces old data with current.
AdvFilterCity.xls 55 kb

hachiroku wrote:

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

--

Dave Peterson

THanks for your reply I will try this when I am back at work.


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