Macro Error 1004: Application-defined or object-defined error

[boldcode]

Hello,

I have a macro that is suppose to copy cells with data from one worksheet and paste them to another except when I run the Macro I get the Macro Error 1004. Here is my code below:

Sub UpdateReport()
    Dim lngFirstRowToAdd As Long, lngLastRowToAdd As Long, lngLastReportRow As Long, lngLastColumnToAdd As Long
    lngFirstRowToAdd = Sheets("rptNoTune").Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).Row
    lngLastRowToAdd = Sheets("rptNoTune").Range("A65536").End(xlUp).Row
    lngLastColumnToAdd = Sheets("rptNoTune").Range("A10").End(xlToRight).Column
    lngLastReportRow = Sheets("rptTune").Range("A65536").End(xlUp).Offset(1, 0).Row
    Sheets("rptNoTune").Range(Cells(lngFirstRowToAdd, 1), Cells(lngLastRowToAdd, lngLastColumnToAdd)).Copy
    Sheets("rptTune").Range("A" & lngLastReportRow).PasteSpecial
    Application.CutCopyMode = False
End Sub

The Macro stops on this particular line of code:

Sheets("rptNoTune").Range(Cells(lngFirstRowToAdd, 1), Cells(lngLastRowToAdd, lngLastColumnToAdd)).Copy

 

[Jonmo1]

When using the syntax
Range(Cells(a,b), Cells(y,z))

And you specify the Sheet on the range, you must also specify the sheet on the Cells as well..

Sheets("...").Range(Sheets("...").Cells(a,b), Sheets("...").Cells(y,z))

Use a With structure to make it simpler without naming the sheet 3 times..

With Sheets("...")
    .Range(.Cells(a,b), .Cells(y,z)).Copy
End With

Hope that helps.

 

[boldcode]

jonmo1,

I changed the code to what you suggested and it works. I have a question for you though how can I streamline my these parts of my code a little better.

In this part how can I change Range("A65536") to last row in worksheet

  lngFirstRowToAdd = Sheets("rptNoTune").Range("A65536").End(xlUp).End(xlUp).Offset(1, 0).Row
    lngLastRowToAdd = Sheets("rptNoTune").Range("A65536").End(xlUp).Row

In this part how can I change Range("A10") which is the start row of my pivot table to start at pivot field titles and again change Range("A65536") to last row in worksheet:

lngLastColumnToAdd = Sheets("rptNoTune").Range("A10").End(xlToRight).Column
    lngLastReportRow = Sheets("rptTune").Range("A65536").End(xlUp).Offset(1, 0).Row

 

[Jonmo1]

Cells(Rows.Count,"A") is preferred over Range("A65536")

Rows.Count = available rows
And will reflect the correct # regardless of XL Version.

 

[boldcode]

jonmo1,

You completely solved my problem. Thank you, I truly appreciate it.

ray:ray:ray:

 

[Jonmo1]

Glad to help, thanks for the feedback.

 

[sugrue]

Jonmo1,

I just want to throw another "Thank You" your way. In the past, I have always been successful creating Pivot Tables using:

    Dim PTCache As PivotCache
    Dim PT As PivotTable
    Dim PRange As Range

...

    Set PRange = WSR.Cells(1, 1).Resize(FinalRow, 9)
    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
            xlDatabase, SourceData:=PRange.Address)

    Set PT = PTCache.CreatePivotTable(TableDestination:=WSR. _
        Cells(2, 2), TableName:="PivotByEmp")

However, for the first time, I was trying to create the PivotTable on a sheet other than the one that contained the source data and I kept getting the 1004 error.

What finally worked (after reading your response in this thread) was to change the SourceData to include the sheet as:

    Set PTCache = ActiveWorkbook.PivotCaches.Add(SourceType:= _
            xlDatabase, SourceData:=WSR.Cells(1, 1).Resize(FinalRow, 9))
   
    Set PT = PTCache.CreatePivotTable(TableDestination:=WSPE. _
        Cells(2, 2), TableName:="PivotByEmp")

Thanks for your help!

 

[Jonmo1]

MrExcel's Search Function hard at work

Glad it helped you out.

 

[pexcel1990]

Hi Jonm01,

need help , i have below code facing same error . can you please check and let me know solution

Sub Filter_Excel_Dates(Date1 As String, Date2 As String)
Dim LastRow As Long




LastRow = ThisWorkbook.Worksheets("Potential CCRA SME Customer").Range("V" & Rows.Count, "V").End(xlUp).Row


ThisWorkbook.Worksheets("Potential CCRA SME Customer").Range("V1:V10000" & LastRow).AutoFilter Field:=1, Operator:= _
xlFilterValues, Criteria2:=Array(1, Date1, 1, Date2)

 

[RoryA]

Welcome to the forum.

This line:

LastRow = ThisWorkbook.Worksheets("Potential CCRA SME Customer").Range("V" & Rows.Count, "V").End(xlUp).Row

should read:

LastRow = ThisWorkbook.Worksheets("Potential CCRA SME Customer").Range("V" & Rows.Count).End(xlUp).Row