HELP - run macro from any worksheet

Hi

I was hoping someone could help as I have a code that works fine on one sheet but not on other worksheets. Can someone give me some advice on how I can change it so it runs in whichever worksheet I like please?

I have the following code and it works when I run it from a worksheet entitled "Active List". However I want to run it from another worksheet call "Full List" (completely unrelated worksheet but this is where I want to run all my macros from), but it comes back with a 'Run Time Error 1004 'Application Defined or Object Defined Error'.

Please Login or Register  to view this content.

The error is associated with the following line:

Please Login or Register  to view this content.

I have attached an example of my issue. You will notice 4 visible tabs.

If you press the "Run All Macros" button on the first tab entitled "Full List" you will note the error coming up. However if you turn to tab entitled "Active List" where I have that specific code only (and it relates to that tab), it works fine.

The question I have is how do I modify the code so it runs from the first tab entitled "Full List" ??(or indeed any tab).

The overall objective of the code is to extract data based on specific headings (in this case numbers) into different tabs using one macro. It works, but is having issues when I change the tab it is run from.

Hope you can help

Hi Kaper

Thanks for your reply and help.

Unfortunately still doesn't look like it is working. Any other suggestions?

I cant figure it out.

Leith

Many thanks for your help - it worked really well!

However I was wondering do you know how I could adapt it so it would also work if I wanted to hide the headings row in my destination sheet? My aim is to have data extracted to the relevant columns (as it does now), but also to hide row 1, where the numbers are so my template looks more professional and avoids anyone deleting the numbers. The numbers in the destination sheet will never change, hence why I want to hide them.

I have tried to hide row one and it doesnt work. As soon as I unhide row 1 of the destination sheet it works perfectly.

Thanks for your help

Quick update - I have worked around the issue by adding in a simple unhide and hide macro either side of calling the above macros. This does the job.



However out of interest is there a way the code could be modified simply so it searches on hidden rows?

Also just wanted to say thank you for highlighting what your code was doing with your comments. They were very helpful.

Many thanks