PDA

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.

SamT
06-29-2013, 02:14 PM
I'll bet that somewhere in the code applying to sheet 1, you are using XXX.Select
Selection.YYY