copy Activesheet multiple times in same workbook, name from list

**jomili** · 11-01-2012, 05:58 PM

Okay, I'm tired of going through this manually. I KNOW there's gotta be an easier way!

Situation: We have a worksheet formatted the way we want it for general use for a Program, or a Strategy, or whatever. The worksheet is great. Now, we need a different worksheet for each Strategy, Program, or whatever that we're working with that day, but different only in that it's named for that particular program,etc. So we have to make 10, 20, 100 copies of that worksheet in the same workbook, and rename each sheet as "Program 1", "Program 2", "Program 100", etc, and change the Header or title cell to the new name. What a pain!

What I want is to copy the activesheet as many times as I have entries in my list, renaming each tab (and maybe a header in the worksheet) with the list name. Does anyone have a macro that'll do that?

UPDATE: Never mind. Sometimes I just need to think it through. I looked back at an existing macro I'd made, and discovered it was easier to modify than I thought. Code is below if someone can find use for it. But it STILL doesn't add the header name. I'll try to come up with a solution for that if I can find the time.

HTML Code:

**Norie** · 11-01-2012, 06:31 PM

What do you mean by the 'header'?

Is it actually the header or a cell on the worksheet?

Where does the header come from?

**jomili** · 11-01-2012, 07:34 PM

It depends on the worksheet. Sometimes there's an actual header on the page (IE Header and Footer), sometimes there's just a cell (usually on the top row) that contains the worksheet title. Ideally I'd like to select either the header cell or tell the macro that there's an actual header (99.9% of the time it's the center header) and have the macro paste the Worksheet name there.

**Norie** · 11-01-2012, 07:39 PM

You can add a header with the shee nam like this.

Please Login or Register  to view this content.

**jomili** · 11-02-2012, 10:33 AM

So, in my current code, after the line

Please Login or Register  to view this content.

I add your code, and for each sheet added the Sheetname would go in the center header? If that works, sounds like all I need to do now is at the front of my macro ask the question,"Do you want the sheet name added to the center header?", and route the macro accordingly. Could I add some font criteria to the "CenterHeader" line?

**Norie** · 11-02-2012, 10:40 AM

Yes you can add formatting to the header but it is a little tricky, well I think it is anyway.

Here's an example of the string you would use:

.CenterHeader = "&""Arial,Regular""&12&A"

This sets the font to be Arial, Regular and size 12.

So I suppose the general syntax is kind of like this.

Please Login or Register  to view this content.

**jomili** · 11-05-2012, 05:32 PM

Sorry it's taken so long to get back. WAY too many distractions (I call that "my job").

I had to make a change; "Sheets(Sheets.Count)." wasn't working, so changed it to "Activesheet". Also added some screenupdating code to speed it up. Other than that the code is roughly the same as before, but now adds a formatted title. Thanks so much Norie!

Please Login or Register  to view this content.

**Norie** · 11-05-2012, 06:01 PM

It should be Sheets.Count, but ActiveSheet should work too.

copy Activesheet multiple times in same workbook, name from list

LinkBack

Thread Tools

Rate This Thread

Display

copy Activesheet multiple times in same workbook, name from list

Re: copy Activesheet multiple times in same workbook, name from list

Re: copy Activesheet multiple times in same workbook, name from list

Re: copy Activesheet multiple times in same workbook, name from list

Re: copy Activesheet multiple times in same workbook, name from list

Re: copy Activesheet multiple times in same workbook, name from list

Re: copy Activesheet multiple times in same workbook, name from list

Re: copy Activesheet multiple times in same workbook, name from list

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions