×
INTELLIGENT WORK FORUMS
FOR ENGINEERING PROFESSIONALS

Log In

Come Join Us!

Are you an
Engineering professional?
Join Eng-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Eng-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Jobs

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

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

RE: Can a macro create a successively numbered copy of a worksheets?

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.

RE: Can a macro create a successively numbered copy of a worksheets?

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.

RE: Can a macro create a successively numbered copy of a worksheets?

TDAA is right, that is the way to do it.
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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Eng-Tips Forums free from inappropriate posts.
The Eng-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Eng-Tips forums is a member-only feature.

Click Here to join Eng-Tips and talk with other members!


Resources