How to store sheetnames in an array and copy from array to summary sheet?

[waimea]

Hi, I am trying to create a summary worksheet with data from 26 separate tabs.

I want to exclude the header in the 26 data sheets and copy from sheet1 to summary sheet, from sheet2 to summary sheet etc without the header.

Please Login or Register  to view this content.

All suggestions are welcome.

1. Store the 26 sheet names in an array
2. Loop through the array and copy items from sheet1 to summary sheet, sheet2 to summary etc
3. Apply advanced filter on the summary

[waimea]

I guess 1. is

Please Login or Register  to view this content.

And 2. could be something like?

Please Login or Register  to view this content.

[Fluff13]

How about

Please Login or Register  to view this content.

[waimea]

Hi Fluff,

thank you for your reply and your code, it works great but the "format" of the values is not copied?

[Fluff13]

What do you mean the formats are not copied?
Using copy destination copies everything.

[waimea]

Hi Fluff,

I have a date in column B and after the copy it shows as "43862"?

[waimea]

Nevermind, I was wrong, I had used "remove formats".

My bad!

[Fluff13]

Glad to help & thanks for the feedback.