Compile Error : Object required in VBA code

[Noobulon]

So i have two ranges, on my "Home Page" sheet, that each contains a drop down list.

Each Drop Down List contains the same list of sheets ( "Location 1","Location 2",...)

EXCEL DROP DOWN.PNG

All the "Location #" sheets have the same layout.

I have a CommandButton1 on my "Home Page" Sheet aswell.

I would like to be able to Copy/Paste from a sheet to another while staying on the home page.

But I'm getting a Compile Error : Object Required

EXCEL OBJ REQU.PNG

Here is my code, any help is appreciated.

Please Login or Register  to view this content.

[WasWodge]

SheetCopy as you have it is a Range not a String

Please Login or Register  to view this content.

I haven't looked at the rest of your code as you are using merged cells.

[Bernie Deitrick]

Don't use Set - that is only for when you are using an object-based variable. You are using a string (and merged cells!), so just use

Please Login or Register  to view this content.

[Noobulon]

Alright thanks guys!! I've got passed that Error, now I'm getting Subscript out of range error.

Its when i'm trying to Dim WScopy as a worksheet variable.

Please Login or Register  to view this content.

Thanks for your times guys

[cytop]

You don't have a sheet named 'SheetCopy' ...

[Bernie Deitrick]

You need to use Set in that case (now you are using an object), and lose the quotes since you have a variable:

Please Login or Register  to view this content.

[WasWodge]

I suspect you want it as below (which is what Bernie Deitrick posted originally)

Leave it as a String and make the change Bernie Deitrick posted in post #3 and remove the quotes.

Please Login or Register  to view this content.

Btw, I don't know why you aren't just doing

Please Login or Register  to view this content.

rather than use the sheetcopy variable

[Noobulon]

Yes WasWodge and Bernie, you guys figured it out.

Thanks alot!!

here is my functional code and layout for future references if any other are trying to do the same thing.

DONECompost Pile Manager.PNG

Please Login or Register  to view this content.

GL HF