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

chiswickbridge

Board Regular
Joined
Feb 2, 2013
Messages
124
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

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Hello Chiswickbridge,

If you replace this section of code:-

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

with this:-

VBA Code:
 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.
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

vcoolio

Well-known Member
Joined
Jun 29, 2014
Messages
1,079
Sorry Chiswickbridge,

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

VBA Code:
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,129,791
Messages
5,638,340
Members
417,021
Latest member
moon miner

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top