Can a macro create a successively numbered copy of a worksheets?
Can a macro create a successively numbered copy of a worksheets?
(OP)
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
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





RE: Can a macro create a successively numbered copy of a worksheets?
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.
RE: Can a macro create a successively numbered copy of a worksheets?
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.
RE: Can a macro create a successively numbered copy of a worksheets?
For your second question: you can easily sum across sheets using the following:
=SUM('Page (1):Page (99)'!A62)
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.