Using macro to add new sheets and change file name 1

[farnye]

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.

 

[Mutt]

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

Sub Macro1()

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

Sheets("Sheet2&quot.Copy After:=Sheets("Sheet2&quot
Sheets("Sheet1&quot.Copy After:=Sheets("Sheet2&quot

Sheets("Sheet1 (2)&quot.Name = Myvalue + "_A"
Sheets("Sheet2 (2)&quot.Name = Myvalue + "_B"

Sheets(Myvalue + "_B&quot.Select
Cells.Replace What:="Sheet1", Replacement:=Myvalue + "_A", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

End Sub

 

[farnye]

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.

 

[Mutt]

Strange! No reason for that - I have put the test one I wrote onto

http://www.langleyventures.co.za/addnewsheet.xls

- download and see if works on your Excel as it works on mine!

 

[farnye]

I got it to work now!

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

Thanks for your help!