VBA Copy and Paste Range - Application Defined or Object Defined Error

[avd88]

Hello,

Hoping I could get some help. I'm working on a project and keep getting an application defined or object defined error.
I'm trying to select a range from A3 to column K with a dynamic row variable depending on the last row with data in it and clear contents.

Then I need to select the same range from 2 different spreadsheets and copy and paste values to the sheet where the data was cleared.

After checking the name of the spreadsheets in the code a couple of times I can't seem to find any issue with the syntax.
I believe that the issue is somewhere on the way I'm selecting a range to copy, this is where I'm getting the error:

Sheets("Materiales Riego VC").Range(Cells(3, 1), Cells(nextrowRiegoMaster, 11)).Copy

This is my full code:

'MASTER

    Dim nextrowMaster As Integer
    Dim nextrowRiegoMaster As Integer
    Dim nextrowJardinesMaster As Integer
    
    nextrowMaster = WorksheetFunction.CountA(Sheets("Materiales Master").Range("D:D"))

    Sheets("Materiales Master").Range(Cells(3, 1), Cells(nextrowMaster, 11)).ClearContents
    
    nextrowRiegoMaster = WorksheetFunction.CountA(Sheets("Materiales Riego VC").Range("D:D"))
    nextrowJardinesMaster = WorksheetFunction.CountA(Sheets("Materiales Jardines VC").Range("D:D"))
    nextrowMaster = WorksheetFunction.CountA(Sheets("Materiales Master").Range("D:D"))

    Sheets("Materiales Riego VC").Range(Cells(3, 1), Cells(nextrowRiegoMaster, 11)).Copy
    Sheets("Materiales Master").Cells(nextrowMaster + 1, 1).PasteSpecial Paste:=xlPasteValues
    
    nextrowMaster = WorksheetFunction.CountA(Sheets("Materiales Master").Range("D:D"))
    
    Sheets("Materiales Jardines VC").Range(Cells(3, 1), Cells(nextrowJardinesMaster, 11)).Copy
    Sheets("Materiales Master").Cells(nextrowMaster + 1, 1).PasteSpecial Paste:=xlPasteValues

 

[MARK858]

Try...

Range(Sheets("Materiales Riego VC").Cells(3, 1), Sheets("Materiales Riego VC").Cells(nextrowRiegoMaster, 11)).Copy

 

[avd88]

Worked beautifully! Thank you!

 

[MARK858]

You're welcome.
Just so you know if you don't qualify the sheet name to all the ranges (Cells(?, ?) is just a single cell range) then it refers to the ActiveSheet so your code was telling Excel to look at 2 different sheets "Materiales Riego VC" and the active sheet.
A range must be on the one sheet which is why it was throwing the error (if "Materiales Riego VC" was the active sheet when your code was run then it wouldn't have erred).