RT Error 424-Obj Required, when UNIONing 2 ranges in order to sync 2 cols of filtered data

[excelnoobee]

Hello All,


In a nutshell, I'm trying to filter one column ("Emp Cont Type") for two values ("Regular" and "Term"), and then have those values copied over to a second column ("Employment Type Indicator"), for the exact same set of filtered data.

Now, we all know that doing a straight "copy and paste" of filtered data does/will not work.

So, I found out that one way to achieve the required results is as follows:•Apply the required filter
•Select the entire source column
•Then select the target column
•Then perform a "Fill Right" or "Fill Left" depending on which side the target column lies

My code below is doing exactly the above, however I find that when I run the code from a sheet "other than the one that contains the data" it bombs out with a "Runtime error 424 - Object required".

On the other hand, if I physically activate/select the data sheet and run the code it works just fine (no error, and data gets copied across correctly)

What I do want to achieve is to have the end-user click a button from a menu and have the code do its job (transparent to the user/in the background)...without the user having to see that a data sheet is being selected, and all kinds of things happening to the data on that sheet, and then being returned to the "menu" sheet.

Is there any way I can either fix the error, or perhaps even achieve my desired result a different way? Perhaps I'm over-complicating things!

Thanks.

Please Login or Register  to view this content.

[6StringJazzer]

What line of code is highlighted in the debugger when the error occurs?

I just skimmed your code but did see this:

Please Login or Register  to view this content.

You need a . before Range. The way it's written, it will refer to this range on the active sheet.

[6StringJazzer]

What line of code is highlighted in the debugger when the error occurs?

I just skimmed your code but did see this:

Please Login or Register  to view this content.

You need a . before Range. The way it's written, it will refer to this range on the active sheet.

[excelnoobee]

@6StringJazzer,

Thanks for the reply, however, in making the change you suggested, I now get the message "Runtime error 1004 - No cells were found", when I run the code from the "menu" sheet, and that error is displayed again the line:

Please Login or Register  to view this content.

Actually I did change that line slightly, by adding the .SpecialCells.... bit at the end (trying different things).

When I run the code (with your change included) from the data sheet itself, it still runs successfully....so I don't think that that change has really made a difference

Any other things/ways you could think of?

Thanks again.

[excelnoobee]

@6StringJazzer,

Thanks for the reply, however, in making the change you suggested, I now get the message "Runtime error 1004 - No cells were found", when I run the code from the "menu" sheet, and that error is displayed again the line:

Please Login or Register  to view this content.

Actually I did change that line slightly, by adding the .SpecialCells.... bit at the end (trying different things).

When I run the code (with your change included) from the data sheet itself, it still runs successfully....so I don't think that that change has really made a difference

To answer your question though, the line against which I received the earlier error is:

Please Login or Register  to view this content.

Any other things/ways you could think of?

Thanks again.