Copy Range & Paste to different Sheet 1004 Run-Time Error

Question

I have tried finding a fix for my problem but what I've tried hasn't worked. Copying raw exported data to another sheet for sorting and formatting. Active workbook copy of data to the second sheet, pasting of data error.

Run-time error '1004':

Application-defined or object-defined error

Public Sub BOM_FORMAT()

If ActiveWorkbook.Worksheets.Count = 1 Then
Sheets.Add after:=Worksheets(Worksheets.Count), Count:=3
End If


' Set numrows = number of rows of data.
  NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

Sheets("Sheet1").Select
Sheets("Sheet1").Name = "RAW"
Columns("C:K").Copy _
Destination:=Sheets(2).Columns(1, 8)

Sheets(2).Select
End Sub

Rest of Code is just formatting and works fine. Error thrown on "Destination" line.

Please any help would be greatly appreciated.

the '_Destination' is to keep the '.Copy' reading for parameters — A McRae

Tim Williams Tim Williams · Accepted Answer · 2018-07-27T22:09:02

Columns("C:K").Copy Destination:=Sheets(2).Cells(1, 8)

So:

Public Sub BOM_FORMAT()

    If ActiveWorkbook.Worksheets.Count = 1 Then
        Sheets.Add after:=Worksheets(1), Count:=3
    End If

    'number of rows of data
    NumRows = Range("A2", Range("A2").End(xlDown)).Rows.Count

    With Sheets("Sheet1")
        .Name = "RAW"
        .Columns("C:K").Copy Destination:=Sheets(2).Cells(1, 8)
    End With

    Sheets(2).Select

End Sub

Copy Range & Paste to different Sheet 1004 Run-Time Error

1 Answers