CODEWITHSUNDEEP
excelvba
googlekid
googlekid

Reputation: 155

how can you run a macro in one sheet without it having an effect on other spreadsheets you open

Basically I'm running a macro in one workbook which is constantly running. When I open another workbook the macro breaks and its falls out of range. How can I lock the macro on that particular sheet?

Upvotes: 0

Views: 5999

Answers (2)

Ryan G
Ryan G

Reputation: 9560

Rule of thumb when working with excel vba, do not use ActiveWorkbook/ActiveSheet/Selection variables. Make sure these are explicitly stated, eg.

option explicit

sub myRoutine()
    Dim ws as Worksheet
    Set ws = ThisWorkbook.Sheets('SheetName')

End Sub

ActiveSheet and selection will use what ever sheet is currently being used, and the current cell that is selected.

I am guessing that you recorded a macro, in this case hit Alt+F11 and go to your current workbook and find the Modules in the project explorer. You may need to modify the code to not reference Active Instances.

Upvotes: 5

user2858650
user2858650

Reputation:

You need to explicitely state what workbook/worksheet you are using... Your macros are bleeding into other sheets because it is running on whatever has focus at that moment. Use statements like thisWorkbook or Worksheet("myname") to explicitely reference which sheet/book should be edited.

Upvotes: 0

Related Questions