
Dashboards
Power Query

November 15, 2017

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

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

VIP

Trusted Members

June 25, 2016

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


VIP

Trusted Members

June 25, 2016

1 Guest(s)
