Log In
Register
Home
Dashboards
Power Query
November 15, 2017
Offline
I am trying to copy a new sheet based on the active sheet and rename it using an input box. I then want to copy a sheet named "January" and copy and paste that data into the worksheet I just created, but it needs to be a variable named worksheet as I add a new sheet each month.
In the code below, "March" in this case will be named to April for the following month.
I hope this explanation makes sense. I can provide the workbook if needed.
Can someone please help. Here is what I have so far.
Thanks in advance,
Ken Mc
Sub InsertDuplicateSheet()
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = InputBox("Please insert the name of the new sheet.", "Rename Sheet")
Sheets("January").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Sheets("March").Select
Range("A1").Select
ActiveSheet.Paste
Range("B4").Select
End Sub
VIP
Trusted Members
June 25, 2016
Offline
Hi Kenneth
Try this. I added the code in red.
Sub InsertDuplicateSheet()
ActiveSheet.Copy After:=ActiveSheet
ActiveSheet.Name = InputBox("Please insert the name of the new sheet.", "Rename Sheet")
'Save the newly created sheet name to a variable
NewShtName = ActiveSheet.Name
Sheets("January").Select
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
'Copy to the newly created sheet
Sheets(NewShtName).Select
Range("A1").Select
ActiveSheet.Paste
Range("B4").Select
End Sub
Hope this helps.
Sunny
December 5, 2016
Offline
Cannot run the VB, prompt syntax error
Pls try store to a worksheet and run to see if problem can be fixed
VIP
Trusted Members
June 25, 2016
Offline
Hi David
You need 1 default sheet and another sheet named January.
The modified code is based on my understanding on what the OP wanted and also what the original code is doing.
Without the actual file with some sample data, this is all that I can do (making a guess and hope for the best 
)
Sunny
Dashboards
Power Query
November 15, 2017
Offline
Thanks David and Sunny!!
I really appreciate your help. I added your variable as a string and changed:
NewShtName = ActiveSheet.Name
and everything worked perfectly.
Thanks again,
Ken Mc
VIP
Trusted Members
June 25, 2016
Offline
Thanks for the feedback Kenneth.
Glad we are able to help.
Sunny
December 5, 2016
Offline
Ken,
If you don't mind can you upload the Worksheet with the VB for our reference , of course all sensitive or cret data to be
covered-up or twisted as purely for testing application objective.
Dashboards
Power Query
November 15, 2017
Offline
Of course not David. It's just chart I made to track the problem I was having with my feet and hands. I'm happy to say since the doc put me on a steroid, I've made great progress since late November. 🙂
1 Guest(s)
