Object required error 424 on copying worksheet

Question

I'm having problem copying a sheet and creating new ones from it in excel macro.

I have buttons on sheet1 named "ahmer". There exists a commandbutton that reads required data from cells of sheet2 and sheet3. Then it needs to copy sheet named "sheet100" and create new sheets for every row of data it read from sheet2 and. Then populate data on them that it gathered from sheet2 and sheeet3.

But it is giving "object required error 424". here's my code of commandbutton2.

Private Sub CommandButton2_Click()


Dim d As Integer

For d = 2 To 3
    Dim z As Integer
    Dim e As Integer
    Dim last_row As Long
    Dim uu As Worksheet
    last_row = Worksheets(d).Cells(Rows.Count, 1).End(xlUp).Row

    For e = 2 To last_row
        Dim Name As String, IndustryName As String

        Name = Worksheets(d).Cells(e, "C")
        IndustryName = Worksheets(d).Cells(e, "D")

        Dim new_sheet As Worksheet


        Set new_sheet = Sheets("Sheet100").Copy(after:=Worksheets(Sheets.Count)) #error here
            new_sheet.Name = "Sheet" & ActiveWorkbook.Worksheets.Count

            Worksheets(new_sheet.Name).Cells(3, "E").Value = IndustryName

            Worksheets(new_sheet.Name).Cells(3, "C").Value = Name

    Next e
Next d


End Sub

But it is giving "object required error 424" - it is also giving you the line where it happens. — GSerg
Set new_sheet = Sheets("Sheet100") On the next line put new_sheet.Copy(after:=Worksheets(Sheets.Count)) — Miles Fett
@MilesFett That is wrong. It will then proceed changing the old sheet. — GSerg
.Copy does not return a worksheet. But it makes the copied sheet active, so you can save ActiveSheet into new_sheet right after copying. — GSerg

Tim Williams Tim Williams · Accepted Answer · 2019-10-14T19:19:26

Since you know where to find the new copy:

With ActiveWorkbook
    .Sheets("Sheet100").Copy after:=.Sheets(.Sheets.Count)
    Set new_sheet = .Sheets(.Sheets.Count)
End with

Object required error 424 on copying worksheet

1 Answers