Quote:
Originally Posted by
You might try changing the sheet name in the sub to "ActiveSheet" (i.e., find and replace "Sheet4" with "ActiveSheet"). Then, you could assign a keyboard shortcut to the macro. By changing the destination sheet to the active sheet, then the sub will run on whatever sheet was active when the keyboard shortcut was used.
If you use this method, you may want to add some form of validation at the beginning of the sub so that it doesn't inadvertently run on the wrong tab. This could be handled with one line:
If ActiveSheet.CodeName < "Sheet4" And ActiveSheet.CodeName < "Sheet5" Then Exit Sub
Another option might be to add a button to each sheet and tie the macro to that button. If you really need an option on the ribbon, I can't be of much help. But there is an article on the subject at http://msdn.microsoft.com/en-us/libr.../ee767705.aspx.
Ben |
Thanks again Ben.
The issue I have is that each sheet has a sub ParseData() and the criteria for the data and the cells copied can be different for each sheet.
I did think about the button route as I have used that before to sort result league tables after they have been updated.
i will look into that link you provided to see if I can get the equivalent of the button but not embedded on the sheet. I do not think I will have much success though after thinking about it. Still worth a try.