Copy and Paste Macro

[motherteresa]

I have a workbook with 3 worksheets in it. Each worksheet needs a various number of duplicate pages. In other words, the info on the page is like a template that needs to be copied downward. The page contains formulas and a macro attached to a button.

I have the Workbook created, with 1 page in each of those 3 worksheets. Our school consultants will then use their copy of the workbook and, depending on the client's needs, will need each worksheet's page copied and pasted a number of times. For example:

Ann needs
Wrksht 1 - 10 pages
Wrksht 2 - 15 pages
Wrksht 3 - 7 pages

Sue needs
Wrksht 1 - 30 pages
Wrksht 2 - 35 pages
Wrksht 3 - 15 pages

I could just create, say, 50 pages in each worksheet and then distribute the workbook to each consultant. But I was wondering if a macro could be written to copy everything on a page and then paste it x number of times...x being somehow defined by the user for each case.

So, for worksheet 1, the macro would copy rows 1-42 and then paste it to cell A43, A85, A127 and so on depending on how many copies were needed. This macro would be attached to a form(?) where a number indicating the number of copies desired could be input.

Thanks for any help. Hope I've explained it thoroughly enough.

 

[AlphaFrog]

Merge is the scourge of Excel.

I would suggest you use Alignment: Center Across Selection instead of Merged cells.

 

[motherteresa]

Yeah, I know. Bummer!! Because of how certain headings have to be aligned, I don't think the Center Across Selection option is going to work. Was hoping there was a way to paste the sheets saving all the formatting AND pasting the control button on the first pages that runs a "clear data" macro.

Hey, thanks for helping out.

 

[AlphaFrog]

Instead of merged cells, you could try using a text box object from the Drawing toolbar. They would be duplicated with the macro and you can make them look just like merged cells.

 

[motherteresa]

AlphaFrog (and Smitty, if you're out there)-

You're very kind and patient. Thank you. After the Merge issue, I decided to give Smitty's code another look. Turns out it didn't work (I'm guessing) because his range and offset was set for:

 Range("A1:I42").Copy Cells(Rows.Count, "A").End(xlUp).Offset(1)

When I changed it to:

 Range("A1:M42").Copy Cells(Rows.Count, "A").End(xlUp).Offset(42)

it copy and pasted just fine...except...(arrrgghhh...there's always something) the formulas didn't progress. What I mean is, a formula like this:

=IF(ISBLANK(INVOICE!$E$7),"",IF(J4="Annual",EDATE(J3,12),IF(J4="Semi-Annual",EDATE(J3,6),IF(J4="Quarterly",EDATE(J3,3),""))))

stayed that way instead of becoming:

=IF(ISBLANK(INVOICE!$E$7),"",IF(J46="Annual",EDATE(J45,12),IF(J46="Semi-Annual",EDATE(J45,6),IF(J46="Quarterly",EDATE(J45,3),""))))

and so on down the worksheet

Smitty's is not as elaborate of a solution because it would have to be activated from each page for that page. But, if I could get the formula issue worked out I think it would work.

Again, any help is SO appreciated. But, I understand if you have invested enough time already.

PS - Not trying to be difficult, but the text box thing won't work for a variety of reasons.

 

[AlphaFrog]

This seemed to work.

From my macro above, change this line...

rng(i).Copy Destination:=rng(i).Resize(rng(i).Rows.Count * pgs(i))

To this...

rng(i).Copy Destination:=rng(i).Offset(rng(i).Rows.Count).Resize(rng(i).Rows.Count * (pgs(i) - 1))

 

[motherteresa]

Oh My Gosh...it's so close. Here's the weird thing that happened.

It copied and pasted beautifully with formatting, the dreaded merged cells, progressing formulas...BUT

No matter how many copies I choose, it copied that macro button I have on the page and pasted it to only the first two pages. So, Page 1 and 2 wound up having the button, but not 3 4, and 5 if I chose 5 copies.

The button that did paste worked though.