Copy a worksheet to the end of the workbook.

[Jerald_Rufus]

Hi,

I am new to VBA, to be frank I am new to Excel.

I needed to copy a sheet to the end of the workbook and rename with a value of cell in another sheet of the same workbook and copy some values for another sheet.

I manage type some code based on the results i get in web but i am getting

"Run-time error '424':
Object Required."

The sheet which I am trying to copy has Buttons and the code for it.

Below is the code.

Private Sub Seperate_Click()


Dim no_assy As Variant
Dim i As Integer
Dim j As Integer
Dim k As Integer
Dim assy As String
Dim ws As Worksheet
Dim lRow As Long
Dim l As Integer
Dim m As Integer


    lRow = Worksheets("PART LIST").Cells.Find(what:="*", _
                    After:=Range("A1"), _
                    LookAt:=xlPart, _
                    LookIn:=xlFormulas, _
                    searchorder:=xlByRows, _
                    searchdirection:=xlPrevious, _
                    MatchCase:=False).Row
    
no_assy = InputBox("Enter number of Item")
no_assy = no_assy + 2
    For i = 3 To no_assy
        l = 3
        assy = Worksheets("PART LIST").Cells(1, i)
        m = ThisWorkbook.Sheets.Count
        Set ws = ThisWorkbook.Sheets("CompTemplate").Copy(After:=Worksheets(m))
        ws.Name = assy
           For j = 2 To lRow
                If IsEmpty(Worksheets("PART LIST").Cells(j, i).Value) = False Then
                    For k = 1 To 2
                        Worksheets(assy).Cells(l, k) = Worksheets("PART LIST").Cells(j, k)
                    Next k
                        Worksheets(assy).Cells(l, 3) = Worksheets("PART LIST").Cells(j, i)
                        l = l + 1
                 End If
            Next j
                        
        Next i
End Sub

Thanks for your help in advance

 

[JoeMo]

When you get an error message, click Debug and you will see that the line causing the error highlighted. Tell us what line that is to help us diagnose the problem.

 

[Jerald_Rufus]

When you get an error message, click Debug and you will see that the line causing the error highlighted. Tell us what line that is to help us diagnose the problem.

\

Thank You very much Joe.

I have found a solution.
I don't know the cause of the problem but it runs now.

assy = Worksheets("PART LIST").Cells(1, i)
                Sheets(3).Copy After:=Sheets(Sheets.Count)
                Set ws = ActiveSheet
        ws.Name = assy