×
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

Using macro to add new sheets and change file name

Using macro to add new sheets and change file name

Using macro to add new sheets and change file name

(OP)
Im trying to see if the following problem is even do-able.

When doing traffic forecasting, i need to add/copy 2 new sheets in excel each time an additional intersection is started. By using the macro recorder, i have been successful in doing the following;

-copying from 2 previous sheets to make the new sheets.
-rename the new sheets to a generic name
-change the formulas in the new sheet 2 to ref back to new sheet 1 using the new generic name.

My question is can i add an extra step while the macro is running, so an input box can pop up, allwoing me to manual input what i want the sheets to be named(and also apply that name to the formula changes)??

currently i have to wait till it is done running and then change the sheet names and formluas by hand to the correct names i want, making the macro seem worthless.

RE: Using macro to add new sheets and change file name

Try this - need to adapt obviously for your file naming protocol!

Sub Macro1()

Message = "Enter new sheet name"
Title = "Input"
Myvalue = InputBox(Message, Title, "")

Sheets("Sheet2").Copy After:=Sheets("Sheet2")
Sheets("Sheet1").Copy After:=Sheets("Sheet2")
   
Sheets("Sheet1 (2)").Name = Myvalue + "_A"
Sheets("Sheet2 (2)").Name = Myvalue + "_B"
    
Sheets(Myvalue + "_B").Select
Cells.Replace What:="Sheet1", Replacement:=Myvalue + "_A", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

End Sub

RE: Using macro to add new sheets and change file name

(OP)
I ran the script and it would only copy the sheets and then stop.

However, if i break it into two different macros, i can get the copying and the renaming to each work seperatly.

RE: Using macro to add new sheets and change file name

Strange! No reason for that - I have put the test one I wrote onto www.langleyventures.co.za/addnewsheet.xls - download and see if works on your Excel as it works on mine!

RE: Using macro to add new sheets and change file name

(OP)
I got it to work now!

It was something that was wrong with the workbook i was using.

Thanks for your help!

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