View Full Version : Staying on Current Sheet
snowdyce
06-28-2013, 10:14 AM
Hello Experts,
I have a Workbook with multiple Sheets. I have a good amount of code in Module 1 which applies to just one sheet. I am working on say Sheet 2 now and when I run a Macro, it runs the code in Module 1 which results in Sheet 1 being selected. I wanted to know if there is a way to capture my Active view, so that when the macro is run, I can ensure it stays on the active Sheet. I am thinking of a some kind of variable to store the view, then at the end of the code I could select the variable. Any advice is appreciated. Thanks
patel
06-28-2013, 10:18 AM
paste here your code with comments
snowdyce
06-28-2013, 10:20 AM
Do you want all the code? It is very long and pulls data from a BW BPC database.
Richardxtc
06-28-2013, 10:47 AM
Hello Experts,
I have a Workbook with multiple Sheets. I have a good amount of code in Module 1 which applies to just one sheet. I am working on say Sheet 2 now and when I run a Macro, it runs the code in Module 1 which results in Sheet 1 being selected. I wanted to know if there is a way to capture my Active view, so that when the macro is run, I can ensure it stays on the active Sheet. I am thinking of a some kind of variable to store the view, then at the end of the code I could select the variable. Any advice is appreciated. Thanks
try using the following at the beginning of sure code application.screenupdating = false and at the end of your code application.screen updating = true:beerchug:
snowdyce
06-28-2013, 10:50 AM
I do have that in the sheet already, thanks though!
snowdyce
06-28-2013, 10:59 AM
Hey Guys, I was able to solve it by storing the active sheet as a String.
''This is at the beginning of the code
Dim strSheetName As String
strSheetName = ActiveSheet.Name
''Then use this at the end to go back to the orginal sheet
Sheets(strSheetName).Select
p45cal
06-28-2013, 01:02 PM
Instead of using a string you can set a variable to the sheet itself (a bit more robust since even if the sheet has its name changed it will still refer to the correct sheet):
Set OrigSheet = Activesheet
and then later:
OrigSheet.activate
but… it's rare that you need to select the sheet to operate on it; can you eliminate the selections
eg.
Sheets("Sheet2").select
Range("A1:A33").Select
Selection.interior.colorindex = 3
can be condensed to:
Sheets("Sheet2").Range("A1:A33").interior.colorindex = 3
and the active sheet, whichever that might be, remains the active sheet, so no need to select it at the end of operations.
I'll bet that somewhere in the code applying to sheet 1, you are using XXX.Select
Selection.YYY
Powered by vBulletin® Version 4.2.5 Copyright © 2024 vBulletin Solutions Inc. All rights reserved.