Range "Object Required"

Question

I can't figure this out.

Sub Paste1()
    Dim NextRow As Range
    Set NextRow = Range("A" & Sheets("AMCurrent").UsedRange.Rows.Count + 1)
    AMPaste.Range("A3:F3").Copy
    AMCurrent.Activate
    NextRow.PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False
    Set NextRow = Nothing
End Sub

I get an "object error" on row 4, AMPaste.Range("A3:F3").Copy.

what is `AMPaste` is it the name of the sheet, the code name of the sheet, or a public worksheet variable? — Scott Craner, Feb 14 '18 at 14:09
You didn't declare the AMPaste as a worksheet, same with AMCurrent.. — Xabier, Feb 14 '18 at 14:09
I am working off of someone else's code and new to it. They had "Sheet1" in place of "AMCurrent" and "Sheet2" in place of "AMPaste". They are the names of the worksheets. — Heather, Feb 14 '18 at 14:12
When they were using `Sheet1` and `Sheet2` they were using the code name of the sheets and not the actual name of the sheets. I will bet the code name is still something like `Sheet1` and `Sheet2` — Scott Craner, Feb 14 '18 at 14:15
Google `Worksheet Code name VBA` and you will find many articles on the difference, and how to use in code. — Scott Craner, Feb 14 '18 at 14:16
That makes sense. Thanks a ton! I thought that it changed it completely. Lots to learn. — Heather, Feb 14 '18 at 14:16
See [cant access a different sheet getting #VALUE! error](https://stackoverflow.com/questions/48727089/cant-access-a-different-sheet-getting-value-error/48727117#48727117). — , Feb 14 '18 at 14:18
Using Range without preceding it with a worksheet reference, as in `AMCurrent.Range("A" & ...` may be why it's not finding the object originally. Especially if your Activated worksheet changes — Jimmy Smith, Feb 14 '18 at 14:19

score 1 · Accepted Answer · answered Feb 14 '18 at 14:14

1

I'm assuming you didn't declare your Worksheets, and that you used their name instead of Sheets("SheetName"), but I believe this could be your solution:

 Sub Paste1()
    Dim NextRow As Long
    Dim wsPaste As Worksheet: Set wsPaste = Sheets("AMPaste")
    Dim wsCurrent As Worksheet: Set wsCurrent = Sheets("AMCurrent")

    NextRow = wsCurrent.Cells(wsCurrent.Rows.Count, "A").End(xlUp).Row + 1
    wsPaste.Range("A3:F3").Copy
    wsCurrent.Range("A" & NextRow).PasteSpecial Paste:=xlValues, Transpose:=False
    Application.CutCopyMode = False

End Sub

answered Feb 14 '18 at 14:14

Xabier

7,587
1
8
20

Amazing! Thank you so much! This worked perfectly. Excited to pick it apart and learn it. – Heather Feb 14 '18 at 14:18
@Heather, please could you mark my response as an answer, if it has helped? Thanks :) – Xabier Feb 14 '18 at 14:19
I've got to wait a few minutes but definitely will do. Thanks! – Heather Feb 14 '18 at 14:20

score 0 · Answer 2 · answered Oct 15 '20 at 18:06

I had the same problem. My worksheet was named "tagsListSheet" and in my script I had a Worksheet variable also named "tagsListSheet". My line of code that produced your error looked like:

Set wwTags = Range(tagsListSheet.Cells(2, 2), tagsListSheet.Cells(lastTagRow, 2))

and when I changed the name of the worksheet variable, it worked.

Set wwTags = Range(tagSheet.Cells(2, 2), tagSheet.Cells(lastTagRow, 2))

Range "Object Required"

2 Answers2