Copying range of cells generates object defined error

Question

I'm trying to copy paste from an assigned variable worksheet to another.

I've been able to make do by recording but I would like to know how to do it manually.

This is the code I made:

Sub Parse_Reportable()

Dim ws As Worksheet

Sheets.Add.Name = "Copy to Reportable or TAK"

Set ws = Sheets("Copy to Reportable or TAK")

Sheets("sheet1").Select

Range("H3", Cells.Columns(8).End(x1Down)).Copy

ws.Cells(2, 2).Paste

End Sub

Running it creates the worksheet but gives me an

object defined error

in the copy code.

Answer 1

Cells.Columns(8).End(x1Down) will give you the error. It also has a typo. x1Down

Instead of using xlDown, use xlUp to find the last row using THIS and then construct your range to copy.

Is this what you are trying? (Untested)

Option Explicit

Sub Parse_Reportable()
    Dim rngToCopy
    Dim ws As Worksheet
    Dim lRow As Long

    Sheets.Add.Name = "Copy to Reportable or TAK"

    Set ws = Sheets("Copy to Reportable or TAK")

    With Sheets("sheet1")
        lRow = .Range("H" & .Rows.Count).End(xlUp).Row

        Set rngToCopy = .Range("H3:H" & lRow)
    End With

    rngToCopy.Copy ws.Cells(2, 2)
End Sub

Note: You may also want to delete the sheet "Copy to Reportable or TAK" if it already exists before naming the new sheet with that name else you will again get an error.

On Error Resume Next
Application.DisplayAlerts = False
Sheets("Copy to Reportable or TAK").Delete
Application.DisplayAlerts = True
On Error GoTo 0

Sheets.Add.Name = "Copy to Reportable or TAK"