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.
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
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
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
RE: Using macro to add new sheets and change file name
It was something that was wrong with the workbook i was using.
Thanks for your help!