Excel With VBA: activate a worksheet from a file by calling it in a macro from another file

Hi,

I created a file where my sheets have names and I used to store data in those sheets, calling them by the following code:

Sheets(Sheet1.Range("A1").Value).Activate

Where the name of the sheet I want to activate is written in cell A1 of Sheet1.
This works when the macro is in the same file as the relevant sheets but it doesn't if the code is in another opened file. The error is "subscript out of range".
How could I do?

Thanks
Camille.

Hi Camille

Try this

'create a variable
Dim sSheetName As String

'Store the sheet name in a variable
sSheetName = Sheet1.Range("A1").Value

'activate the other workbook
Application.Workbooks("book2").Activate

'then activate the sheet
Application.Sheets(sSheetName).Activate

I hope this helps

Jerid

Hi Jerid,

I tried what you wrote but it is still giving me the error "subscript out of range". I tried to declare "Sheetname" as a public variable in all the modules and userforms of my program.
If the macro is in the file I am working on, it works but I want to use a general macro for different files and that seem to be the problem.
Do you see something else I could try?

Thanks
Camille

Hi Cammille

Try this code, it will activate each open Workbook until it finds one that has a Sheet called the same as the String Variable. If one is not found it will come back to the Workbook the code was run from

Sub ActivateASheet()
Dim SShtName As String
Dim Wbk As Workbook
Dim Ssheet As Worksheet
' Written by OzGrid Business Applications
'www.ozgrid.com

'''''''''''''''''''''''''''''''''''
' Activates a sheet in another workbook
''''''''''''''''''''''''''''''''''

SShtName = Sheet1.Range("A1")

For Each Wbk In Application.Workbooks
On Error GoTo Done
Wbk.Activate
On Error Resume Next
Set Ssheet = Sheets(SShtName)
If Not Ssheet Is Nothing Then
Ssheet.Activate
Exit Sub
End If
Next Wbk

Done:
ThisWorkbook.Activate
End Sub

Dave

OzGrid Business Applications

Hi Dave,

Thanks for your help there is one remaining problem: the string variable (Sheet1.Range("A1"))is defined in the same file as the sheet I want to activate. Your code works if the string variable is defined in the file where the code is written not in the other file. What should I change?

Thanks!
Camille

for your help there is one remaining problem: the string variable (Sheet1.Range("A1"))is defined in the same file as the sheet I want to activate. Your code works if the string variable is defined in the file where the code is written not in the other file. What should I change? !

Hi Camille

Try this

Sub ActivateASheet()
Dim SShtName As String
Dim Wbk As Workbook
Dim Ssheet As Worksheet
' Written by OzGrid Business Applications
'www.ozgrid.com

'''''''''''''''''''''''''''''''''''
' Activates a sheet in another workbook
''''''''''''''''''''''''''''''''''

For Each Wbk In Application.Workbooks
On Error GoTo Done
Wbk.Activate
On Error Resume Next
SShtName = Sheet1.Range("A1")
Set Ssheet = Sheets(SShtName)
If Not Ssheet Is Nothing Then
Ssheet.Activate
Exit Sub
End If
Next Wbk

Done:
ThisWorkbook.Activate
End Sub

Dave

OzGrid Business Applications

This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.

Archive of Mr Excel Message Board

With VBA: activate a worksheet from a file by calling it in a macro from another file

Re: With VBA: activate a worksheet from a file by calling it in a macro from another file

Re: With VBA: activate a worksheet from a file by calling it in a macro from another file

Re: With VBA: activate a worksheet from a file by calling it in a macro from another file

Re: With VBA: activate a worksheet from a file by calling it in a macro from another file

Re: With VBA: activate a worksheet from a file by calling it in a macro from another file