reference a sheet with variable?

Hi there,

I was wondering if there was any way to reference a sheet with a variable name instead of needing to know the exact name of the sheet?

I am trying to open an individual sheet into my workbook with the code:

Dim sFileName As String
'Show the open dialog and pass the selected _
file name To the String variable "sFileName"
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
     
Workbooks.Open fileName:=sFileName
Sheets("EVENTS").Copy After:=Workbooks("NLeapGIS10.xls").Sheets(ThisWorkbook.Sheets.Count)
Workbooks("EVENTS.xls").Close

That works fine, if the sheet the person is trying to open is called "EVENTS". However I would like to implement something like

Sheets(sFileName)

to open the name of the sheet the same as the file name.

I'm not sure what you are trying to do. Does it help to know that the last three lines do the same thing?

Dim sheetName As String
sheetName = "sheet1"

Dim mySheet As Worksheet
Set mySheet = ThisWorkbook.Sheets("sheet1")

ThisWorkbook.Sheets("sheet1").Activate
ThisWorkbook.Sheets(sheetName).Activate
mySheet.Activate

if your sheet is labeled the same name as your workbook then this will work..

Dim sheetname As String
Dim i As String
Dim j As String
Dim k As String
Dim sFileName As String

j = 1
i = ""

'Show the open dialog and pass the selected _
file name To the String variable "sFileName"
sFileName = Application.GetOpenFilename
'They have cancelled.
If sFileName = "False" Then Exit Sub
Workbooks.Open Filename:=sFileName


Do Until k = "\"
i = Right(sFileName, j)
k = Left(i, 1)
DoEvents
j = j + 1
Loop

j = j - 1
sheetname = Right(i, j)