Create Multiple Worksheets from Filtered Data and Rename the Workook

[RHTexas]

Hi All,

Any help will be greatly appreciated.

The below code works great for one site filter, copy and paste into one new worksheet and renaming it to the Site value.

I would like this to loop and create a worksheet for each Different Site and rename the workbook to the site value.

I believe a do until loop would be great but I am not sure how to pass the site name to a new variable and assign it to the filter criteria for each loop iteration.

Please find the basic code below.

Sub filter_copy_insert_worksheet()
'
' filter_copy_insert_worksheet Macro
'

'
Cells.Select
Selection.AutoFilter
ActiveSheet.Range("$A$1:$W$65000").AutoFilter Field:=3, Criteria1:="HX0042"
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.AutoFit
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "HX0042"

End Sub


Here is the data I am using for this.

DATETIME BSC SITE CELL VOICE_ACCESSIBILITY VOICE_RETAINABILITY VOICE_ERLANGS
1/7/2012 HSTB10A HX0042 HX00427 99.72899729 95.92391304 6.686111111
1/7/2012 HSTB10A HX0042 HX00428 99.44598338 99.44289694 1.702777778
1/7/2012 HSTB10A HX0042 HX00429 100 98.85714286 19.76944444
1/7/2012 HSTB10A HX0046 HX00467 100 99.44289694 33.56666667
1/7/2012 HSTB10A HX0046 HX00468 100 99.24433249 21.42222222
1/7/2012 HSTB10A HX0046 HX00469 99.59677419 98.65047233 18.14722222
1/7/2012 HSTB10A HX0087 HX00877 100 97.64918626 33
1/7/2012 HSTB10A HX0087 HX00878 99.93666878 99.61977186 22.05277778
1/7/2012 HSTB10A HX0087 HX00879 99.90697674 98.88268156 21.73888889
1/7/2012 HSTB10A HX0108 HX01087 99.46771581 99.62773383 79.94166667
1/7/2012 HSTB10A HX0108 HX01088 100 95.23809524 7.094444444
1/7/2012 HSTB10A HX0108 HX01089 100 99.75550122 22.275
Once again thank you very much for looking at this one.

Ray

 

[SkipVought]

hi,

Why would you destroy your data source by chopping it up and severely reducing its usefullness?

What's the problem with using the auto filter to view each site in isolation?

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[RHTexas]

Good Morning Skip,

Thanks for the question.

That is a very good question by the way.

The final goal I am trying to get to is to have one worksheet per site with multiple plots on it showing the data.

I realize that a pivot and filter is a good way as well...I'm a newbie to VBA but I use Pivot Tables all the time. I am not sure though if you can have multiple charts on one pivot chart or not...I seem to only get one at a time.

Knowing this, if I can get the data on one worksheet for each site I could then create the plots on that worksheet showing all the data for that one site.

Am I making any since? I've been up all night reading about VBA.

Now if there is a way, which I know there must be because you guys are awesome, if I could create a plot for every column heading for each site on one worksheet and leave my data in the current format I am all ears as well.


Thanks for helping out a Newbie.

Have a great day!

Ray

 

[SkipVought]

Of course PivotTable(s) might be a solution. Depends on what 'story' you need to tell. Could be QueryTable(s) also, or aggregation formulas.

I'd consider leaving the source data as is and on a SUMMARY sheet, doing either one or more PivotTables or QueryTables. If you use the PT, put the SITE in the Page or Filter area. You could drive multiple PT from ONE PT SITE selection, if you need multiple charts displayed in the SUMMARY.

As a rule of thumb, each sheet/table/chart that you add to your workbook adds to maintenance. So rather than 10 SITE sheets and 20 or 30 PivotTables/Charts, you would have 1 SUMMARY sheet with 2 or 3 PivotTables/Charts: a much simpler and better design.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]