Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations TugboatEng on being selected by the Eng-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

awomack

Electrical
Joined
Jul 11, 2006
Messages
6
Location
US
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
 
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.
 
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.
 
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):Page (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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top