Created copied Excel Worksheet throws 424 error

Question

The goal is to create a copied sheet that is renamed in the current month and year. The copied sheet is being created in the workbook, however a default name is given to the sheet. What am I missing?

Private Sub Button3_Click()

Dim wb As Workbook
Dim ws As Worksheet
Dim nowMonth As Integer, nowYear As Integer

Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

nowMonth = Month(Now)
nowYear = Year(Now)

Set wb = ActiveWorkbook

On Error Resume Next

Set ws = wb.Sheet(nowMonth & ", " & nowYear)

On Error GoTo 0

If Not ws Is Nothing Then
    MsgBox "The Sheet called " & nowMonth & ", " & nowYear & " already exists in the workbook.", vbExclamation, "Sheet Already Exists!"
    Exit Sub

Else
    Set ws = ActiveSheet.Copy(after:=wb.Sheets(wb.Sheets.Count))
    ws.Name = nowMonth & ", " & nowYear

End If

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic

End Sub

Vityata · Answer 1 · 2019-11-13T19:31:47.973

1

The problem is in Set ws = ActiveSheet.Copy(after:=wb.Sheets(wb.Sheets.Count)), because it is trying to Copy and Set in the same time and this is a bit too much. Change the code in the condition to this:

If Not ws Is Nothing Then
    MsgBox "something"
    Exit Sub
Else:
    Set ws = ActiveSheet
    ws.Copy after:=wb.Sheets(wb.Sheets.Count)
    wb.Worksheets(wb.Sheets.Count).Name = nowMonth & ", " & nowYear
End If

In general, avoid using Active and Select in VBA - How to avoid using Select in Excel VBA.

edited Nov 13 '19 at 19:31

answered Nov 13 '19 at 19:05

Vityata

42,633
8
55
100

`Copy` is creating (and activating) a new worksheet. OP's code means to set the `Name` of the new sheet - this code doesn't do that, it renames the copied sheet instead. – Mathieu Guindon Nov 13 '19 at 19:26
@MathieuGuindon - and this is why `Activate` and `Select` should be avoided :) I have changed it. – Vityata Nov 13 '19 at 19:32
Hm, why not just `Set newSheet = ActiveSheet` after the copy? The sheet ordering is dependent on the preceding line of code, so if that changes, the wrong sheet gets the new name.. ActiveSheet side-effect isn't ideal, but still better than repeating the ordering logic IMO. – Mathieu Guindon Nov 13 '19 at 19:34
@MathieuGuindon - no idea. I am a bit "allergic" to `ActiveSheet`, but I left it only to make it easy for the the OP to understand what is going on. – Vityata Nov 13 '19 at 19:37

score 0 · Answer 2 · answered Nov 13 '19 at 20:27

Worksheet.Copy disappointingly doesn't return a reference to the created sheet. Instead, it has the side-effect of adding a new sheet to the workbook, and activating it.

So after running Worksheet.Copy, the ActiveSheet is the newly created sheet.

ActiveSheet.Copy after:=wb.Sheets(wb.Sheets.Count)
ActiveSheet.Name = nowMonth & ", " & nowYear

Now, this code is confusing/misleading, because it looks like the two statements are qualified with the same object, but they aren't.

What's not clear, is why and how the ActiveSheet is guaranteed to be the correct sheet to copy; we're working off the ActiveWorkbook and we don't really care which sheet is active.

I'd suggest to make the copy work off an explicit sheet:

Dim sourceSheet As Worksheet
Set sourceSheet = wb.Sheets(wb.Sheets.Count)

sourceSheet.Copy after:=sourceSheet '<~ new sheet becomes ActiveSheet
ActiveSheet.Name = nowMonth & ", " & nowYear

And now everything is as clear as it gets.

Created copied Excel Worksheet throws 424 error

2 Answers2