VBA to copy a new sheet based on active sheet

[kwesmc]

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

[yujin]

You copy all of the cells in the sheet "January" to the new sheet, so why don't you copy the sheet "January" right from the beginning?

[kwesmc]

Because I add a sheet each month and I don't want to have to go in and change the code to "April", "May", and so on, thus, I wanted to know if there was a way to avoid that.

[Paul_Hossler]

How about checking to see if "February" exists and then copy "January" as "March", etc.?

[kwesmc]

Thanks Paul but I'm not understanding you. Yes, "Feb" exists, but I would still have to modify my code, right?

[Paul_Hossler]

Like this

Option Explicit

Sub etc()
    Dim sNextMonth As String, sMonth As String
    Dim i As Long, n As Long
    
    
    For i = 1 To 12
        sMonth = Format(DateSerial(2018, i, 1), "mmmm")
        n = -1
        On Error Resume Next
        n = Worksheets(sMonth).Index
        On Error GoTo 0
        
        'if worksheet sMonth does not exist then sMonth the next month
        If n = -1 Then
            sNextMonth = sMonth
            Exit For
        End If
    Next i
    
    MsgBox sNextMonth
End Sub

[kwesmc]

Thanks Paul, but I'm not sure how to use this to select the next month to paste using the code you sent.
Somehow I need to replace this part of the code using a variable to select the sheet I just created, ie....."March"
Sheets("March").Select
Range("A1").Select
ActiveSheet.Paste
Range("B4").Select

[Paul_Hossler]

Because I add a sheet each month and I don't want to have to go in and change the code to "April", "May", and so on, thus, I wanted to know if there was a way to avoid that.

If (very big IF) I'm understanding what you want to do, this copies the ActiveSheet and renames it the next available month

If Jan exist in the workbook, the copy is named Feb. If Feb exists the copy is named March, etc.

Option Explicit

Sub CopyCurrentSheetAndBumpMonth()
    Dim sNextMonth As String, sMonth As String
    Dim i As Long, n As Long
     
     
    For i = 1 To 12
        sMonth = Format(DateSerial(2018, i, 1), "mmmm")
        n = -1
        On Error Resume Next
        n = Worksheets(sMonth).Index
        On Error GoTo 0
         
         'if worksheet sMonth does not exist then sMonth the next month
        If n = -1 Then
            sNextMonth = sMonth
            Exit For
        End If
    Next I
     
    ActiveSheet.Copy after:=ActiveSheet
    ActiveSheet.Name = sNextMonth
End Sub

[yujin]

ActiveSheet.Name = InputBox("Please insert the name of the new sheet.", "Rename Sheet")

In your code, do you enter "March" with this input box?

[Paul_Hossler]

No -- there is no need for an InputBox

If the sheet "February" exists then the copy is automatically named "March"

[Paul_Hossler]

Do you really need to manually enter the next month?

[yujin]

Do you really need to manually enter the next month?

I have the same question.