Bestway to create MULTIPLE worksheets that are IDENTICAL accept for their name

[JamesGoulding85]

I have some code that create 5 different works sheets "sheet 1", "Sheet 2",...... on each of these sheets I want to copy a header. I also have a number of variables that refer to each individual sheet, so Check1, Check2,.....

Could I use an array or a simple loop to create these sheets to avoid replicating all of the data whilst maintain my individual variable references??

Please Login or Register  to view this content.

Thanking you in advance

Jim

[HaHoBe]

Hi, Jim,

itīs not clear how your Checkx work and whatīs behind them. Maybe you can have a look at the code here which does fine for me using a loop:

Please Login or Register  to view this content.

I would prefer to set objects to the sheets, especially for Sheets(2) which doesnīt look like a very sound way to me (Sheets may be anything inclduign Charts which might be positioned as the second sheet in the workbook).

Ciao,
Holger

[JBeaucaire]

One approach:

Please Login or Register  to view this content.

[HaHoBe]

Hi, Jerry,

the basic idea was my first thought too (no, definitely not the array but to put the copying into the If-Statement as well). Thinking about it any change on Shets(2) would not have been copied over if the sheet in quesiton already existed so I put that piece outside to get that point covered as well.

Just my thoughts about that point.

Ciao,
Holger

[JBeaucaire]

Good point, but I read his original macro as an incorrect approach since his IFERROR test didn't activate the already existing sheet, thus it was just copying the headers onto whatever the activesheet was, again. I figured that was a mistake and he simply wanted to create sheets if they were missing.

[HaHoBe]

Hi, Jerry,

good point as well (I would have preferred a template sheet to copy instead of adding empty sheets and copy rows over). Let Jim explain which one fits in better for his needs.

Ciao,
Holger

[JamesGoulding85]

Hi Folks,

sorry for the late response! I like both suggestions and both is better than what I have. ive attached a sheet that shows what im trying to accomplish, its very messy and im sure a few loops would tidy it all up but im unsure where and how to input them effectively.

The idea is that when you click the button it:

a) condenses the other sheets into a summary page and organises them in ascending order D4 - A1 think that's descending actually lol.
b) then divides that summary sheet into separate years according what year there is a value present. obv some are repeated several times.

there are also lines to copy headers and to ensure that certain headers are labelled according to the year. I want the module to be flexible so that I could copy it into other surveys with different years and more sites.

Thank you for your help everyone