Excel VBA Object Required Error

Question

I have been looking at this code and can't seem to figure out why I keep getting an object required error. I am trying to add a new sheet, place and array and range in the sheet (this works). Next I want to name all of the cells on the sheet a variable name to be used later. Can anyone see why it's not working?

    Set WS_Temp = Sheets.Add
With WS_Temp
    .Range(Cells(1, 1), Cells(1, LastColRA)) = Sheet1.Range("Dynamic_Range").Value
    .Range(Cells(2, 1), Cells(counter + 1, LastColRA)) = Application.Transpose(vList)
    '.Range(Cells(1, 1), Cells(counter + 1, LastColRA)) = Selected_Range
    '.Range(Selection, Selection.SpecialCells(xlLastCell)).Select = Selected_Range
End With

Set Selected_Range = WS_Temp.Range(Selection, Selection.SpecialCells(xlLastCell)).Value ***ERRORS HERE

I have Dimed WS_Temp as Worksheet and Selected_Range As Range earlier.. FYI — Liz
Don't use .Select, that's likely part of it. Also, don't use Value at the end. You just set the Range(), then if you need the value of the range, do Selected_Range.Value. — BruceWayne
When I do that I get "Run-Time error '381' Could not set the Column property. Invalid property array index." — Liz
I have also tried the lined commented out I just posted. None of which worked — Liz

Mathieu Guindon Mathieu Guindon · Accepted Answer · 2016-06-22T14:03:16

Can you try taking off the .Value? – Matt Cremeens

The Set keyword is used to assign object references. Using it to assign values throws the error you're getting, "Object required".

And that's exactly what you're doing here:

Set Selected_Range = WS_Temp.Range(Selection, Selection.SpecialCells(xlLastCell)).Value

You're trying to assign the Selected_Range object the value of WS_Temp.Range(...), which you cannot do legally. Remove .Value and you will assign Selected_Range a reference pointing to a Range object returned by that Range call on your WS_Temp sheet.

Excel VBA Object Required Error

1 Answers