Ensuring Macros only Affect the Active Sheet

Tanner_2004

Well-known Member
Joined
Jun 1, 2010
Messages
616
Hi Folks:

I store the majority of my macros in my personal macro workbook/file. Is the a way to ensure that when I run a macro that it only affects the ActiveSheet?

My second question is how to identify the WS that I want the macro to run in when storing macros in my personal macro workbook for times when i may not have the active sheet open?

Thank you for reading and any advice.
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.
Tony

If you remove any worksheet references in the code and the code is in a standard module it will run against the active sheet in the active workbook.

If you want to run the code against a specific worksheet then make sure all the references in the code have the appropriate workshee reference.
 
Upvote 0
I store the majority of my macros in my personal macro workbook/file. Is the a way to ensure that when I run a macro that it only affects the ActiveSheet?
As long as you do not specify any sheet reference in your code, you aren't referring to specific workbooks, and you aren't looping through worksheets, the code will default to run on whatever the active sheet is at the time.

My second question is how to identify the WS that I want the macro to run in when storing macros in my personal macro workbook for times when i may not have the active sheet open?
The wording on your question is a little confusing. By definition, the active sheet is the sheet that you are currently open to. So a statement like "I may not have the active sheet open" makes no sense. If a sheet is not open, it is not the active sheet, by definition.

You can specify your code to run on any sheet you want by including sheet references in your VBA code.
 
Upvote 0
Thank you both! Regarding my second poorly-worded question (my apologies), how do I include the sheet reference in my code please? Thank you.
 
Upvote 0
This would put 7 in A1 of the sheet named Sheetnamehere
Code:
Worksheets("Sheetnamehere").Cells(1, 1) = 7
 
Upvote 0
Put Worksheets("Sheetnamehere") in front of any RANGE() or CELLS(). This includes if a CELLS are in RANGE

even though the range has worksheet referenced the cells do not so they would look in the active sheet
Code:
[I]Worksheets("Sheetnamehere").[/I]Range(Cells(1,1),Cells(2,1)

To make sure it looks at Sheetnamehere you would need to do this
Code:
[I]Worksheets("Sheetnamehere").[/I]Range([I]Worksheets("Sheetnamehere").[/I]Cells(1,1),[I]Worksheets("Sheetnamehere").[/I]Cells(2,1)
 
Upvote 0
That is one way. You can refer to Worksheets by name, index, or use Worksheet objects.
And you can refer to ranges using Range(), Cells(row,column), or Range objects.

I often like to use Worksheet object, because it allows you to be lazier later in your code, i.e.
Code:
Dim ws as Worksheet
Set ws = Sheets("Sheet1")

ws.Range("A1")=10
...
 
Upvote 0
This worked great! Thank you. If I add more objects, properties, methods..after the first one; do I need to repeat the "ws." before each one? Thank you.
 
Upvote 0
If I add more objects, properties, methods..after the first one; do I need to repeat the "ws." before each one? Thank you.
If you want them explicitly tied to that worksheet object, yes.
Any time you use a Range object without a sheet reference, it will default to whatever the active sheet is at that time.
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,025
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top