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

Posted by Camille on April 24, 2001 12:42 PM

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.

Posted by Jerid on April 24, 2001 1:01 PM

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

Posted by camille on April 24, 2001 3:02 PM

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

Posted by David Hawley on April 24, 2001 4:21 PM

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

Posted by camille on April 25, 2001 10:16 AM

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

Posted by Dave Hawley on April 25, 2001 2:22 PM

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? !