0

Hi I am trying to copy a worksheet every month from a master using a macro in VB i have managed to copy and rename the new worksheet but get an error when i close and reopen the file because the name exists i am new to VB :) Code i have is

Private Sub Workbook_Open()
Sheets("Master").Visible = True
Sheets("Master").Copy After:=Worksheets(Worksheets.Count)
NewPageName = Format(Date, "MMYY")
ActiveWindow.ActiveSheet.Name = NewPageName
End Sub
Bikebrain
  • 11
  • 1
  • 1
  • 2
  • 1
    `NewPageName = Format(Date, "MMYY")` IF you execute this more than once per day, then the worksheet will already exist after 1st run. Think a different way of naming the sheet, or delete it before copying it. A Workbook cannot contain 2 worksheets with same name – Foxfire And Burns And Burns Feb 08 '21 at 11:34
  • I want this to copy Every month and rename to the new month – Bikebrain Feb 08 '21 at 11:36
  • 1
    Check first if the monthly worksheet exists and if not create it as you have done. For checking existence of a sheet see https://stackoverflow.com/questions/6688131/test-or-check-if-sheet-exists – Senior Momentum Feb 08 '21 at 11:56
  • Private Sub Workbook_Open() Sheets("Master").Visible = True Sheets("Master").Copy After:=Worksheets(Worksheets.Count) NewPageName = Format(Date, "mmmm") ActiveWindow.ActiveSheet.Name = NewPageName End Sub – Bikebrain Feb 08 '21 at 12:46
  • Changed a bit to make the name the month but i still cant stop the error if i am in that month – Bikebrain Feb 08 '21 at 12:46
  • 1
    Welcome to Stack Overflow! Could you edit your question to include the error text? It would help others to find the question, answer it or solve their own problem. – Nikolay Shebanov Feb 08 '21 at 18:53

0 Answers0