VBA code that see's if a file is already open, takes you to the requested sheet?

[Carley]

Good Morning


I have created a stand alone dashboard for my team leaders which automattically opens for them in the mornings and has links to all the files they need to use on a daily basis.

(in case you are interested to code I used for the auto opening is:

Sub Autpen()

'

' Autpen Macro


Workbooks.Open Filename:= _

"G:BrentwoodOperations Development & ControlOD&C Team1. General Team DocumentsOD&C Dashboard.xlsm"

End Sub)


Some of the links that the team leaders have requested are to open different worksheets within the same workbook (all the workbooks are shared)


At present I only have the individual codes to open the workbook then the work sheet:

For example:

One option

Workbooks.Open Filename:="group.netglobalBrentwoodUnderwritingPLOperationsMI Master2013 DataPersonal Tables.xlsm"

Worksheets("Accounts Tables ").Select


Another option

Workbooks.Open Filename:="group.netglobalBrentwoodUnderwritingPLOperationsMI Master2013 DataPersonal Tables.xlsm"

Worksheets("Compliance Tables").Select


Is there a code that I can use that will check if the workbook is already open, and if it is take the user to the requested worksheet rather than trying to re-open the whole file?


Thanks

 

[DaveTurton]

[pre]

Dim wBook As Workbook

On Error Resume Next

Set wBook = Workbooks("Personal Tables.xlsm")

If wBook Is Nothing Then 'Not open

Workbooks.Open Filename:="group.netglobalBrentwoodUnderwritingPLOperationsMI Master2013 DataPersonal Tables.xlsm"
Worksheets("Accounts Tables ").Select

Set wBook = Nothing

On Error GoTo 0

Else 'It is open

Workbooks("Personal Tables.xlsm").Activate
Worksheets("Accounts Tables ").Select

Set wBook = Nothing

On Error GoTo 0

End If

[/pre]

 

[Carley]

@DaveTurton

Thank you so much, I have been stressing over this for the past 4 days!!


It works perfectly.

 

[DaveTurton]

You're welcome