Method 'Range' of object '_Worksheet' failed

**Burli** · 10-23-2012, 10:11 AM

I have set up a worksheet that has activex clickbuttons to insert some values into the criteria cells before I do a filter and then run a couple of macros. This works well with everything on a sheet named "dataextract", but I want to move the buttons or place new buttons on another sheet called "graph". The code that I use and works fine from the dataextract sheet is

Please Login or Register  to view this content.

I then have other buttons with different values to copy to the two named cells crit1 and crit2 and run the 2 macros using basically the same scripts.

However if I create a new button on the graph sheet with the same code it fails at the first step and gives a run time error 1004. I think that I have a basic problem addressing the cells on the dataextract sheet but can't seem to work out the correct format. any advice would be welcome.

**JosephP** · 10-23-2012, 10:25 AM

I reckon you probably just need

Please Login or Register  to view this content.

**Burli** · 10-23-2012, 12:15 PM

Thank you Joseph, you are absolutely correct and now the first part of the process works fine to copy values into the crit1 and crit2 ranges. I thought I might be able to fix the macros that are called in the same manner so what I have come up with for the filtering process is

Please Login or Register  to view this content.

but I have messed up somewhere along the line or specifying the work on the dataextract sheet, any ideas please ?

This is the macro before I messed with it, and it works fine when run from the dataextract sheet.

Please Login or Register  to view this content.

as you can see, this is all quite new to me !

**JosephP** · 10-23-2012, 12:43 PM

please edit your post to use code tags before we proceed in accordance with the forum rules, as Jeffrey requested in his edit to your original post

**Burli** · 10-23-2012, 01:10 PM

Thanks Joseph for the tag advice, I had missed the note from Jeffery, and as this is my first time on this forum I didn't know how you made your reply look so neat

**JosephP** · 10-23-2012, 04:59 PM

Please Login or Register  to view this content.

**Burli** · 10-24-2012, 04:41 AM

Thank you Joseph for your help with this and it has been a great help. I think the VBA bug has bitten and I have ordered a book on the subject to try to learn a bit more about it. I used the last code that you sent me, but have a slight problem with it as it again fails with a 1004 error and highlights the following text

Please Login or Register  to view this content.

I believe that this may come from the fact that the data in "datarange" is in fact in Sheet1, something that I failed to mention. Is it possible to include a statement to add Sheet1?

**JosephP** · 10-24-2012, 04:44 AM

yes-it's just

Please Login or Register  to view this content.

**Burli** · 10-24-2012, 05:07 AM

Yes, just perfect. Many many thanks for your help Joseph, it has solved what I have spent hours trying to resolve.

**JosephP** · 10-24-2012, 05:46 AM

If you are satisfied with the solution(s) provided, please mark your thread as Solved.

New quick method:
Select Thread Tools-> Mark thread as Solved. To undo, select Thread Tools-> Mark thread as Unsolved.

Or you can use this way:

How to mark a thread Solved
Go to the first post
Click edit
Click Go Advanced
Just below the word Title you will see a dropdown with the word No prefix.
Change to Solved
Click Save