VBA- To copy Filtered data to Worksheets in the same workbook

[chiswickbridge]

Hi Guys,

I have a Dynamic data in a sheet named "Data"

Rows 1 to 9 are left empty for Macro Icons. Row 10 is the header ( which has a filter at times or no Filter )

Dynamic Raw but Formatted Data is from A11 to AZ999, consisting of all variables ( Text, Numbers... etc).

Column C ( 11 to 999 ) has the following data ( without any Blanks ), "Active", "Redeemed", "Switched", "Purchase", "Passive", "UnNamed1", "UnNamed2","UnNamed3","UnNamed4" and "UnNamed5". The Unnamed is for future additional filtering.

I can to do this by Advanced filtering, but time consuming as the data changes every week

Need a VBA macro, which can filter by "Active" and create a New Worksheet, and Copy / Paste the formatted data from A11 on wards, and then by "Redeemed" and so forth till "UnNamed5", for the first time.

But the catch is... if "Active" sheet and other sheets are existing ( which will happen the 2nd week ), the VBA must simply Copy / Paste, by clearing the Contents from A11 to AZ199.

Also, the Sheets order will unchanged, namely in the mentioned order as "Active", "Redeemed", "Switched", "Purchase", "Passive", "UnNamed1", "UnNamed2","UnNamed3","UnNamed4" and "UnNamed5"... commencing from the First Sheet in the Woorkbook

Thanks you Guys in Advance for your Help...

 

[vcoolio]

Hello Chiswickbridge,

If you replace this section of code:-

 With sht.[A10].CurrentRegion
              .AutoFilter 3, key
              .Columns("A:AZ").Copy ws.[A1]
              .AutoFilter
        End With

with this:-

 With sht.Range("C10", sht.Range("C" & sht.Rows.Count).End(xlUp))
              .AutoFilter 1, key
              .Columns("A:AZ").Copy ws.[A10]
              .AutoFilter
 End With

it should all fall into place for you.

I hope that this helps.

Cheerio,
vcoolio.

 

[vcoolio]

Sorry Chiswickbridge,

I've left out a little section of code in my last post. The replacement should read as follows:-

With sht.Range("C10", sht.Range("C" & sht.Rows.Count).End(xlUp))
              .AutoFilter 1, key
              .Columns("A:AZ").Offset(, -2).Copy ws.[A10]
              .AutoFilter
   End With

Cheerio,
vcoolio.