Staying on Current Sheet

[snowdyce]

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]

paste here your code with comments

[snowdyce]

Do you want all the code? It is very long and pulls data from a BW BPC database.

[Richardxtc]

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

[snowdyce]

I do have that in the sheet already, thanks though!

[snowdyce]

Hey Guys, I was able to solve it by storing the active sheet as a String.
[VBA]''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[/VBA]

[p45cal]

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.
[VBA]Sheets("Sheet2").select
Range("A1:A33").Select
Selection.interior.colorindex = 3
[/VBA]
can be condensed to:
[VBA]Sheets("Sheet2").Range("A1:A33").interior.colorindex = 3[/VBA]
and the active sheet, whichever that might be, remains the active sheet, so no need to select it at the end of operations.

[SamT]

I'll bet that somewhere in the code applying to sheet 1, you are using [vba]XXX.Select
Selection.YYY[/VBa]