Ensuring Macros only Affect the Active Sheet

[Tanner_2004]

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.

 

[Tanner_2004]

Thank you for teaching me to fish!

 

[Joe4]

You are welcome!

What you may also see sometimes is that a person will put code at the top that activates a specific sheet, i.e.

Sheets("Sheet1").Activate

That makes that specific sheet the active sheet.

Then you can have code underneath it does not use a sheet reference of any kind (so it defaults to the active sheet).

So instead of using specific sheet references, they will just first make whatever sheet the want to work on the active sheet by selecting/activating it.
That works fine if everything is being done to one specific sheet, but if bouncing back-and-forth between sheets a bunch in your code (like copying/pasting), that can be confusing and your code might not work right if you don't have it written correctly.

 

[Tanner_2004]

Yes..even better. I have actually used that myself. I will change. Thank you!

 

[Tanner_2004]

Where do I place the Activate method please? Thank you.

Dim ws As Worksheet
Set ws = Sheets("PositionStatus")

 

[Joe4]

Just under that, you could do:

ws.Activate

However, in your are only going to be working on this sheet (no other sheets are involved), if would be easier to replace all three of those lines with just the single line:

Sheets("PositionStatus").Activate

 

[Scott T]

You would need to place it before any code that does not have a sheet reference on the range objects (range(),CELLS()).
So if you are formatting cells on sheet1 activate it and to work with a different sheet you would activate it.

Sheets("Sheet1").Activate
Range("A2:A7") = Format(Date, "mm/dd/yy")

Sheets("Sheet2").Activate ' to use range objects with out sheet references for cells on sheet2
Range("A2:B7")="HI"

 

[Tanner_2004]

Thank you both very much!

 

[Norie]

Tony

FWIW you don't need to activate sheets to work with them, and as someone mentioned earlier, activating sheets can cause problems.

Also, you don't need to repeat the worksheet reference everywhere, you can use a With statement.

For example, let's say you had code like this,

Range("A10").Value = "Total"

Range("B10").Value = "=SUM(B2:B9)"

and you want to apply it to a specific sheet, say 'Sheet3'.

Then you can use something like this.

With Sheets("Sheet3")
    .Range("A10").Value = "Total"

    .Range("B10").Value = "=SUM(B2:B9)"
End With

Anything in between the With...End With that's preceded by a . refers back to the sheet in the With statement, in this case Sheets("Sheet3").

 

[Tanner_2004]

Thank you very much.