Can a macro create a successively numbered copy of a worksheets? 3

[awomack]

Is there a macro or any other way to create a copy of a sheet (eg. Page1) with a successive page number (eg. Page2) everytime the macro is run?

Also, is there a way to reference a worksheet only if the sheet exists?

i.e.
='P-to-P #1'!A62+'P-to-P #2'!A62+'P-to-P #3'!A62+'P-to-P #4'!A62

but only if page P-to-P #X is actualy there?

Thank you for reading and/or replying

 

[handleman]

Short answer to question 1:

Yes

Short answer to question 2:

Yes.

Long answer to question 2:

ERROR.TYPE worksheet function to test if the sheet is there. Such as:

IF(ERROR.TYPE(Sheet4!C5) = 4,"Sheet missing!",Sheet4!C5)

This checks for a #REF error on cell C5 of Sheet4. The ERROR.TYPE function returns a value of 4 for #REF errors. If the sheet is not there, the cell will say "Sheet missing!". Otherwise it will show the value of C5 on Sheet4.

 

[TDAA]

For #1 you should be able to use the typical commands, or enter by mouse click, into the macro.

If you do not have a particular naming set up for the pages, there is an easy cheat to make the numbers go in succession: Name the first page "Page (1)". When you copy, Excel will add one to the number each time you copy.

 

[joerd]

TDAA is right, that is the way to do it.
For your second question: you can easily sum across sheets using the following:
[tt]=SUM('Page (1)age (99)'!A62)[/tt]
You can set up your workbook so that Page (1) is the first sheet, and Page (99) the last sheet (which may be a placeholder). If you copy Page (1) and the new sheet Page (2), as TDAA explained, is in between Page (1) and Page (99), the range A62 will be summed for all sheets including Page (2).

Cheers,
Joerd

Please see FAQ731-376 for tips on how to make the best use of Eng-Tips.