Run an Excel VBA script on only the active worksheet and not the entire book?

Question

I am using a VBA script to essentially find/replace. Right now when I run the VBA script it applies to all open sheets in the workbook. I wish for the VBA script to only apply in the active sheet and not touch the rest.

Here is my current macro code:

Sub ReplaceCC()
'
' ReplaceCC Macro
' Add CC to Distributor, Reseller, Government and Retail.
'

'
Range("A1").Select
Cells.Replace What:="Distributor", Replacement:="DistributorCC", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Cells.Replace What:="Reseller", Replacement:="ResellerCC", LookAt:=xlPart _
    , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Cells.Replace What:="Government", Replacement:="GovernmentCC", LookAt:= _
    xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
Cells.Replace What:="Retail", Replacement:="RetailCC", LookAt:=xlPart, _
    SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
    ReplaceFormat:=False
End Sub

Here is the entire VBA script with all subs.

There's nothing in this sub that changes sheets. So it should only affect the active sheet by default. Unless it is called from another sub where the active sheet is changed. — tbur, Feb 26 '14 at 21:09
As of last week this macro was working fine. Today it will not run on a single sheet, but all open sheets. Nothing has changed. Could there be another setting somewhere? — InfiniteGeoff, Feb 26 '14 at 21:32
Could be... just created a workbook with some entries and it will only replace values on my active worksheet... are you sure this is all the code? — pegicity, Feb 26 '14 at 21:39
[Here](http://pastebin.com/13H5ZZr2) is the entire macro with all subs that are essentially the same. — InfiniteGeoff, Feb 26 '14 at 21:52
@InfiniteGeoff Are you sure this is changing every sheet of the workbook? Also, what module does the code reside in? Did you put it in a module you inserted, or is attached to one of the sheet modules or the "ThisWorkbook" module? It seems to run fine from either an inserted module or the "ThisWorkbook" module, but only seems to modify the sheet it is attached to in any of the sheet modules, unless you add the `ActiveSheet` reference. In any event, no matter where I put it I cannot get it to affect all sheets of the workbook. How are you executing it? — MattB, Feb 26 '14 at 22:06
Just declare and set a fully qualified Range Object before you do something to it. That will solve your problem. See [THIS](http://stackoverflow.com/questions/10714251/excel-macro-avoiding-using-select) for some guidance. — L42, Feb 27 '14 at 00:46
@user2761919 I guess some more info may help. I have my workbook. It contains 6 identical sheets, copied from one main sheet. I open an excel file that contains the macros. From my workbook I [view macros and run one of 5 in each sheet](http://puu.sh/7clAB.png). This has always worked for me until this week. When I run a single macro from the 5, it applies that single macro to all sheets in the workbook. — InfiniteGeoff, Feb 27 '14 at 13:44
@InfiniteGeoff Well, I think I'm out of ideas. As far as I can tell, it looks like your macros are placed in inserted modules, not one of the sheet or this workbook modules, which should mean that you are good to go. I still think you should insert `ActiveSheet.` immediately before each of your `Cells` and `Range` references, and the code should only modify the active sheet, but it should only be modifying the active sheet now and I am unable to reproduce the behavior that you are experiencing - I can't figure out how it is modifying all sheets in your new workbook. Hope you figure it out. — MattB, Feb 27 '14 at 16:06
@InfiniteGeoff ... the values you are changing... they aren't linked to the other sheets by formulas or something like that are they? That's my real last idea... — MattB, Feb 27 '14 at 16:20

score 3 · Answer 1 · answered Aug 30 '14 at 23:28

I got the same problem. It depends of the Find/Replace history: if in the previous manual use of Find/Replace, you specified "Options > Search in Workbook", your macro will apply to all sheets. Otherwise, with the default "In Sheet", the macro will stay in the original Worksheet. I still don't know how to fix that from within the macro, though.

MattB · Answer 2 · 2014-02-26T21:58:07.607

0

Interesting question. I believe that you could do what you're trying to do by simply placing "ActiveSheet" before each object reference in your code.

For example: Instead of Range("A1").Select use ActiveSheet.Range("A1").select

If you set up your code like this, whenever you execute, it should carry out its actions on the worksheet you currently have active.

EDIT: You should check to see which module you have this code placed in. If it is in an inserted module, I think you should be fine. On the other hand, if you've inserted it in one of the sheet modules it will only modify the sheet it is attached to. If you insert ActiveSheet before your cell references and the rest it will still modify the active sheet instead of the sheet the code is attached to.

edited Feb 26 '14 at 21:58

answered Feb 26 '14 at 20:49

MattB

2,203
5
26
48

Range(...) or Cells(...) are shortcuts for ActiveSheet.Range(...) or ActiveSheet.Cells(...). So this is not an answer. – Wolfgang Kuehn Feb 26 '14 at 21:42
@amadeus In a module yes, in the worksheet modules not so much. I usually find it is better to be more specific generally. Might have been better posted as a comment than answer though. – MattB Feb 26 '14 at 21:50
Put _Debug.Print Cells(1, 1).Parent.Name_ anywhere, be it vba module, sheet module or workbook module: It will always print the name of the active sheet. – Wolfgang Kuehn Feb 26 '14 at 22:10
@amadeus I think you need to place that code in the Sheet1 module and activate sheet 3 and see what happens yourself. Not trying to be a smart-ass, I just double checked myself, it returns the sheet it is attached to, not the active sheet just as I thought it would. Likewise, you can put code for `Range("A1").value = "Hello"` in any of the sheet modules and activate any sheet you want but it will still only write "Hello" in the sheet it is attached to. I still don't understand how he has code that is changing every sheet though, that doesn't seem to happen no matter where the code is. – MattB Feb 26 '14 at 22:18

score 0 · Accepted Answer · answered Mar 03 '14 at 14:20

It seems the macro was not recognizing the active sheet properly since the sheets were copies of each other. If you copy the content on Sheet 1 and create a new sheet then paste the content on it this will work. Each sheet needed to be unique and not just a copy of the original.

score 0 · Answer 4 · answered Dec 11 '14 at 17:07

This is due to a VBA bug. When you use "Find" in VBA, the parameter "LookAt:=xlPart" or "LookAt:=xlWhole" works as expected. But when you use "Replace", the coded parameter is ignored and the replace uses the last MANUAL setting for its scope. The workaround is to do a "Find" operation right before the "Replace" operation, since "Find" uses the coded "LookAt" scope (thereby making the "Find"'s scope the last setting used, so the "Replace" will then use the scope coded into the "Find").

score 0 · Answer 5 · edited Apr 05 '17 at 00:03

0

Solution from http://www.vbaexpress.com/forum/showthread.php?11444-Solved-Replace-in-a-sheet-and-not-the-entire-workbook

Dim dummy As Range

Set dummy = Worksheets(1).Range("A1:A1").Find("Dummy", LookIn:=xlValues)

Then you can post REPLACE functions underneath these lines of codes

edited Apr 05 '17 at 00:03

Baum mit Augen

49,044
25
144
182

answered Apr 05 '17 at 00:02

william hartanto

1

Run an Excel VBA script on only the active worksheet and not the entire book?

5 Answers5