Autofilter & Copy Macro

[Harlequin]

Hello

I have a main workbook with 40,000 lines of data for various locations. Column A shows the locations. They all have the same fields in column B to N

I am looking for a macro that will filter on column A (Location name) & for every location in that’s in there,
Copy it, open a new worksheet, paste the data for that location into it, plus, name the tab the same as the location name that’s been pasted in there.

I attach a workbook, of desired results.

Any help would be much appreciated

[ilovedurango]

Do you want this to be an automatic macro, or just a macro?

[Harlequin]

Hello.

I need it to be a macro.

Cheers

[ilovedurango]

Hello,

I've started working on your problem. I have the macro that copies and pastes, I just want to clarify - you are not always shipping to the five cities you have listed, correct? There will be more cities, and when more cities come, you need those worksheets to be automatically created, correct?

[Harlequin]

Yes, that is correct.

Some new locations may come on board, & some currant ones may not have data in some periods.

Cheers

[ilovedurango]

I have completed the macro!

Please Login or Register  to view this content.

That macro calls this macro to make it run

Please Login or Register  to view this content.

I'm still new at this, but this definitely works. The way I have it set up is for you to select the city and then run the macro. I didn't really focus on filtering it, but I can do that as well. Let me know if you have problems.

Katie

[Harlequin]

Hello Katie.

Can you send me back the worksheet you worked on, because when I put in the code it doesn't work?

Regards

[ilovedurango]

Here you are

[royUK]

You don't need to Select or Activate for such procedures. This code uses AdvancedFilter to create a list of unique names in a remote Column (256). There is a Function to check if a worksheet exists to pevent errors. Then it Filters the data creating a new sheet for each addition & overwriting existing data in sheets created earlier.

Please Login or Register  to view this content.

[Harlequin]

Hello.

That works, but it's only bringing in the headings for each tab (A1:D1). It doesn't bring in the data associated with each destination.

Regards

[royUK]

It's helpful to say whose code you are referring to. Yhe code Iposted needed a little tweaking for your spreadsheet. The Field should have been 1 not 2.

Please Login or Register  to view this content.

[Harlequin]

Many thanks Katie & RoyUK for your input.

RoyUK, your code worked to exactly what I wanted it to do.

Many thanks.

[royUK]

Glad it helped.

Katie, just a tip. When working with VBA it is rarely necessary to Select ranges and by doing so your code wil be slower.