CODEWITHSUNDEEP
excelvba
Bikebrain
Bikebrain

Reputation: 11

Copy and name a worksheet with version number in Excel VBA

I am trying to copy from a master template and rename the copy in Excel VBA.

I am trying to rename the first copy of the master to DATA 1 and then the next time the new sheet would be DATA 2.

Private Sub Workbook_Open()

Dim iThisMonth As Integer, iLastMonth As Integer
Dim datLastRun As Date
Dim rCheckCell As Range
Set rCheckCell = Sheets("master").Range("A5")
Set Target = Range("V16")

On Error Resume Next
datLastRun = CDate(rCheckCell.Value)
iLastMonth = Month(datLastRun)
On Error GoTo 0

If Target.Value = "yes" Then
    MsgBox "Call Peter"
    Call Peter
    rCheckCell.Value = Format(Now(), "dd/mmm/yy")
End If
End Sub

Macro Code

Sub Peter()

Sheets("Master").Visible = True
Sheets("Master").Copy After:=Worksheets(Worksheets.Count)
NewPageName = Data1
ActiveWindow.ActiveSheet.Name = NewPageName

End Sub

I want to copy and rename to DATA 1 when I have entered YES in cell V16 but then I want to rename MASTER to DATA 2 the next time YES is entered into cell V16.

Upvotes: 1

Views: 15396

Answers (2)

kris
kris

Reputation: 11

Public Function fxCopySheetName(ByVal pBook As String, ByVal pSheet As String, ByVal pName As String) As String
    With Workbooks(pBook)
        .Sheets(pSheet).Copy After:=.Sheets(.Sheets.Count)
        .Sheets(.Sheets.Count).Name = pName
        fxCopySheetName = .Sheets(.Sheets.Count).Name
    End With
End Function

Upvotes: 0

Siddharth Rout
Siddharth Rout

Reputation: 149295

Create a basic function like below which will give you the next available name. For example, if there is a worksheet with the name Data1 and Data2, then the below function will return Data3

Private Function NewDataName() As String
    Dim ws As Worksheet
    Dim i As Long: i = 1
    Dim shtname As String
    
    Do
        '~~> Create a worksheet name
        shtname = "DATA" & i
        
        '~~> Check if we already have a worksheet with that name
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(shtname)
        On Error GoTo 0
        
        '~~> If no worksheet with that name then return name
        If ws Is Nothing Then
            NewDataName = shtname
            Exit Do
        Else
            i = i + 1
            Set ws = Nothing
        End If
    Loop
End Function

And the usage will be like

Sub Peter()
    Sheets("Master").Visible = True
    Sheets("Master").Copy After:=Worksheets(Worksheets.Count)
    ActiveWindow.ActiveSheet.Name = NewDataName
End Sub

Upvotes: 1

Related Questions