Copy sheet, add +1 to cell F1 -> name the copied sheet

Hi there,

See if the following code does what you need:

Sub CopySheet()

    Const sWORKSHEET_PREFIX As String = "Weekly data "
    Const sWEEK_NO_CELL     As String = "F1"

    Dim iCurentWeekNo       As Integer
    Dim iNewWeekNo          As Integer
    Dim wksLatest           As Worksheet

    With ThisWorkbook
        Set wksLatest = .Worksheets(.Worksheets.Count)
    End With

    iCurentWeekNo = wksLatest.Range(sWEEK_NO_CELL).Value
    iNewWeekNo = iCurentWeekNo + 1

    Application.DisplayAlerts = False
        wksLatest.Copy After:=wksLatest
    Application.DisplayAlerts = True

    With ActiveSheet
        .Name = sWORKSHEET_PREFIX & CStr(iNewWeekNo)
        .Range(sWEEK_NO_CELL).Value = iNewWeekNo
    End With

End Sub

The highlighted values can be changed to suit your requirements.


Hope this helps - please let me know how you get on.

Regards,

Greg M

Thanks, it works like a charm.

Hi again,

Many thanks for your very prompt feedback and also for the Reputation increase - much appreciated!

You're welcome - glad I was able to help.

Best regards,

Greg M