Do until code attempt..

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.

Please Login or Register  to view this content.

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

Please Login or Register  to view this content.

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.

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.