Need Help with a Macro to activate a specific sheet: Excel 2012

[Johnny Thunder]

Hello all,

I have a macro I am working (see below). I have defined "varCellvalue" as a name of a Excel file in a cell which is a drop down list and "VarCell" as a Sheet name in every file of the drop down list so what I am trying to accomplish is that whatever a user selects as a file and a tab name the macro will open a specific file and specific sheet.

Currently the file will open but the specific sheet isn't working? any help or recode to help this would be greatly appreciated. Thanks all!!


Sub OpenWorkbook()

varCellvalue = Sheets("Main").Range("B23").Value

varCell = Sheets("Main").Range("B19").Value

If Not IsEmpty(Range("B23").Value) Then

Workbooks.Open "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xlsm"
Sheets(" & VarCell & ").Activate

Else

Workbooks.Open "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xls" Sheets(" & VarCell & ").Activate

End If

End Sub

 

[Stildawn]

Someone probably has a better idea. But couldnt you simply have the macro open the workbooks (like you have). Then have another line of code activate the sheet once open?

Activeworkbook.Sheets(VarCell).Activate

Has this after your open command. I might go quickly check.

EDIT:

Yep worked for me:

Workbooks.Open "\\Lax-Netapp01\Dept_private\Business Systems\LFC MACRO TEST\" & varCellvalue & ".xls"

ActiveWorkbook.Sheets(VarCell).Activate

This modification should work.

 

[Johnny Thunder]

Just adding this "Activeworkbook.Sheets(VarCell).Activate" worked perfect for me! Thanks again!

 

[Stildawn]

No worries.

It might be more accurate however to have this:

Workbooks(varCellvalue & ".xls").Sheets(VarCell).Activate

Instead, as it removes the "activeworkbook" thing and is more specific, might stop some potential issues.

 

[Johnny Thunder]

I like your style