itipu
07-27-2007, 01:37 PM
Hi All,
I have a worksheet Main Report as attached with 11 Columns.
11th Column is a "Region", filtering on a region will show all the records in that region.
My goal is to have a script to loop through all the regions, and for each create a new worksheet and copy into it all relevant records.
So far I came up with:
Selection.AutoFilter Field:=11, Criteria1:="AH-SE"
sizer = ActiveSheet.UserRange.Rows.Count
Range("A1:K$" & sizer).Select
Selection.Copy
Sheets("Main Report").Select
Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "AH-SE"
ActiveSheet.Paste
Sheets("Main Report").Select
Selection.AutoFilter Field:=11, Criteria1:="EPA-TCI"
This works but:
This method hardcodes names of new pages and filtering criterias... I would much rather have some kind of a loop through all possible criterias (unique values in Column 11) and pages should be named based on criteria...
I attached my workbook as an example!
Again, Thanks a lot, greately appreciate your help!!
Mike
I have a worksheet Main Report as attached with 11 Columns.
11th Column is a "Region", filtering on a region will show all the records in that region.
My goal is to have a script to loop through all the regions, and for each create a new worksheet and copy into it all relevant records.
So far I came up with:
Selection.AutoFilter Field:=11, Criteria1:="AH-SE"
sizer = ActiveSheet.UserRange.Rows.Count
Range("A1:K$" & sizer).Select
Selection.Copy
Sheets("Main Report").Select
Sheets.Add
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "AH-SE"
ActiveSheet.Paste
Sheets("Main Report").Select
Selection.AutoFilter Field:=11, Criteria1:="EPA-TCI"
This works but:
This method hardcodes names of new pages and filtering criterias... I would much rather have some kind of a loop through all possible criterias (unique values in Column 11) and pages should be named based on criteria...
I attached my workbook as an example!
Again, Thanks a lot, greately appreciate your help!!
Mike