go to specific worksheet on close

[tuktuk]

wht is vb code to select a specific worksheet every time you close a workbook?

that is, i need to active the active worksheet to be "CompiliedMaster" prior to closing the .xls. i still want the user to be prompted for the option of saving the file.

thanks
Terry

 

[VoG]

Try

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.Goto reference:=Sheets("CompiliedMaster").Range("A1"), scroll:=True
End Sub

This goes in the workbook's code module (right click XL logo to the left of File > View Code).

 

[Jeffrey Smith]

Put this in ThisWorkbook in the VBA objects:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Sheets("CompiliedMaster").Activate

End Sub

 

[tuktuk]

what is a private sub?

that is, do i add it to my module with all of my other code? i want to say no becuase i am unable to Step Into it like i do when i test my other coding.

t

 

[VoG]

As I said above:

This goes in the workbook's code module (right click XL logo to the left of File > View Code).

 

[tuktuk]

gotcha. do i have to have a ActiveWorkbook.Save cmd in the code?

i added it to ThisWorkbook but it doesn't open to the CompilieMaster tab after saving the code....closing the .xls.......and then reopening.

t

 

[VoG]

You could include a Save command in the code.

However, if what you want is for the same sheet always to be selected on *opening* the workbook then this would be simpler:

Private Sub Workbook_Open()
Application.Goto reference:=Sheets("CompiliedMaster").Range("A1"), scroll:=True
End Sub

which goes in the same Workbook module (you can then delete the other code as it would be superfluous).

 

[tuktuk]

ahhhhh i see what you are talking about wiht the On_Open concept....but i already have a code that runs on open....i know i can add it to the code but let me explain how it all works......

here is the current on open code (i think you helped me with it a few days ago)

'ReFresh Named Range on Sheet1 to Current Region
'Sheet1="CompiliedMaster" for CPS Doc
    ActiveWorkbook.Names.Add Name:="PRange", RefersTo:=Worksheets("CompiliedMaster").Range("B4").CurrentRegion

'ReFresh Pivot Table on Sheet2
'Sheet2="CompiledMasterPivot" for CPS Doc
    For Each pvt In Worksheets("CompiledMasterPivot").PivotTables
        pvt.PivotCache.Refresh
    Next pvt
End Sub

the order of how this works currently is:

i have a code in another file that opens a 2nd file and uploads data to CompiliedMaster sheet. therefore i need to have the active sheet to always be CompiliedMaster do the data is updated to the correct sheet.

a 2nd sheet in the .xls contain a pivot witch uses "CompiliedMaster" data for it's source.

so i was thinking On Close => makes CompiliedMaster as the active sheet (then the new days data is auto updated in the correct sheet).

then On Open = > refresh pivot with the new data from CompiliedMaster.

any suggestions on streamlining this concept?