title: copy and rename a worksheet in excel VBA
body: <p>Hi i am trying to copy from a master template and rename a the copy worksheet in excel VBA i was using Date which worked great but now I am trying to rename the copy of the master to DATA 1 and then the next time the new sheet would be DATA 2</p>
  <pre><code>Private Sub Workbook_Open()
  
  Dim iThisMonth As Integer, iLastMonth As Integer
  Dim datLastRun As Date
  Dim rCheckCell As Range
  Set rCheckCell = Sheets(&quot;master&quot;).Range(&quot;A5&quot;)
  Set Target = Range(&quot;V16&quot;)
  
  On Error Resume Next
  datLastRun = CDate(rCheckCell.Value)
  iLastMonth = Month(datLastRun)
  On Error GoTo 0
  
  If Target.Value = &quot;yes&quot; Then
  MsgBox &quot;Call Peter&quot;
  Call Peter
  rCheckCell.Value = Format(Now(), &quot;dd/mmm/yy&quot;)
  End If
  End Sub
  </code></pre>
  <p>Macro Code</p>
  <pre><code>Sub Peter()
  
  Sheets(&quot;Master&quot;).Visible = True
  Sheets(&quot;Master&quot;).Copy After:=Worksheets(Worksheets.Count)
  NewPageName = Data1
  ActiveWindow.ActiveSheet.Name = NewPageName
  
  End Sub
  </code></pre>