Have macro run on a single worksheet

[Groovicles]

Hi everyone,

I have the following code:

Please Login or Register  to view this content.

This macro is assigned to a userform button located in different worksheets. All I want is for this macro to be run only in the XXX Data worksheet, even though the button is located in a different worksheet. For some reason, the macro keeps try to read the worksheet that the button is located on first before moving to the XXX data worksheet.

Any ideas?

Thanks!!!

[MarvinP]

Hi Groovicles,

This might be an easy answer but we'd need to see the whole macro to insure it is.

Instead of your line of:
Range("B1:IV1500").Select
try
.Range("B1:IV1500").Select

By putting a period in front of the "Range" it uses the "With" location instead of the active sheet.

I'm not sure this is the answer but give it a try.

[Richard Buttrey]

Hi

Hi,

You can't select a range on a sheet that isn't the active sheet. You can do stuff like Copy but not Select. Even with Copy you would have to put a . in front of the Range("B1.... instruction.

To use the code you have you would need to Activate the XXX worksheet first (and then you wouldn't need the With statement.

But it's it's generally bad practice to use .Select & .Activate. Better to create a range object variable and use that object in any subsequent YYY procedure you are calling.