Activate a Worksheet

[Sean]

For some reason, when I open up a workbook with several worksheets, I
am not able to activate a worksheet to select a range. Here's the
code:

Workbooks(excelFilename).Activate
Sheets(wsName).Select

Both excelFilename and wsname are Dim as String. As I trace the code,
it opens the workbook, but it will not select the sheet wsName.

For example, it will open the workbook and Sheet1 is active. When it
hits the Sheets(wsName).Select part of the code (where wsName is
"Sheet3), it does not switch to Sheet3.

I've also tried Sheets(wsName).Activate, and this does not work either.

 

[Sandy]

Replace Sheets(wsName).Select
with WorkSheets(wsName).Select

Sandy

 

[Sean]

Replace Sheets(wsName).Select
with WorkSheets(wsName).Select

Sandy

That didn't work. It still does not Select the Sheet

 

[Guest]

Hey Sean,

Did you try :

Workbooks(excelFilename).Sheets(wsName).Range( ) ... ??

You could also try the following:

Public Function FindWorksheet(wsName as String) as Worksheet
Dim aWS as Worksheet

For Each aWS in ActiveWorkbook
If aWS.Name = wsName then
Set FindWorksheet = aWS
Exit Function
End If
Next aWS

Set FindWorksheet = Nothing
End Function


You'd call this function like this :

Dim myWorksheet as Worksheet

.... ' (some code)

Set myWorksheet = FindWorksheet(wsName)

myWorksheet.Range().Select ... ' whatever you want to do ...

Hope this helps,
Chad

 

[Sandy]

what is the name of the sheet exactly the way that it appears in the
tab...also let me see the code that this is in, from dimming the two
strings to calling the workbook and worksheet, and I'll see what may be
going on

 

[Sean]

Here is the basic code:

Dim wbName As String
Dim wsName As String
Dim fileName As String

FileName = Cells(w, y)
Workbooks.Open FileName:= _
FileName, UpdateLinks:=xlUpdateLinksAlways
Workbooks(wbName).Activate
Worksheets(wsName).Activate
Rows("10:1000").Select

The code above is in XL1.xls. It will open an excel file (fileName)
and should activate the worksheet (wsname) and then select rows
10:1000....but it doesn't work.

If I walk through the code in the fileName it works, so I'm not sure
why it doesn't work with my code shown above.

 

[Guest]

Hey Sean,

Looking at your code...

Dim wbName As String
Dim wsName As String
Dim fileName As String

FileName = Cells(w, y)
Workbooks.Open FileName:= _
FileName, UpdateLinks:=xlUpdateLinksAlways

FileName has a definition... what doesnt have an assignment
is both wbName and wsName ...

Workbooks(wbName).Activate
Worksheets(wsName).Activate

So these 2 statements wont work the way it's written

... try Workbooks(FileName).Activate
and set the wsName before referencing it in
Worksheet(wsName).Activate ...

Hope this helps,
Chad