Reference excel worksheet by name?

Question

I have the name of a worksheet stored as a string in a variable. How do I perform some operation on this worksheet?

I though I would do something like this:

nameOfWorkSheet = "test" ActiveWorkbook.Worksheets(nameOfWorkSheet).someOperation() 

How do I get this done?

Answer 1

There are several options, including using the method you demonstrate, With, and using a variable.

My preference is option 4 below: Dim a variable of type Worksheet and store the worksheet and call the methods on the variable or pass it to functions, however any of the options work.

Sub Test()   Dim SheetName As String   Dim SearchText As String   Dim FoundRange As Range    SheetName = "test"         SearchText = "abc"    ' 0. If you know the sheet is the ActiveSheet, you can use if directly.   Set FoundRange = ActiveSheet.UsedRange.Find(What:=SearchText)   ' Since I usually have a lot of Subs/Functions, I don't use this method often.   ' If I do, I store it in a variable to make it easy to change in the future or   ' to pass to functions, e.g.: Set MySheet = ActiveSheet   ' If your methods need to work with multiple worksheets at the same time, using   ' ActiveSheet probably isn't a good idea and you should just specify the sheets.    ' 1. Using Sheets or Worksheets (Least efficient if repeating or calling multiple times)   Set FoundRange = Sheets(SheetName).UsedRange.Find(What:=SearchText)   Set FoundRange = Worksheets(SheetName).UsedRange.Find(What:=SearchText)    ' 2. Using Named Sheet, i.e. Sheet1 (if Worksheet is named "Sheet1"). The   ' sheet names use the title/name of the worksheet, however the name must   ' be a valid VBA identifier (no spaces or special characters. Use the Object   ' Browser to find the sheet names if it isn't obvious. (More efficient than #1)   Set FoundRange = Sheet1.UsedRange.Find(What:=SearchText)    ' 3. Using "With" (more efficient than #1)   With Sheets(SheetName)     Set FoundRange = .UsedRange.Find(What:=SearchText)   End With   ' or possibly...   With Sheets(SheetName).UsedRange     Set FoundRange = .Find(What:=SearchText)   End With    ' 4. Using Worksheet variable (more efficient than 1)   Dim MySheet As Worksheet   Set MySheet = Worksheets(SheetName)   Set FoundRange = MySheet.UsedRange.Find(What:=SearchText)    ' Calling a Function/Sub   Test2 Sheets(SheetName) ' Option 1   Test2 Sheet1 ' Option 2   Test2 MySheet ' Option 4  End Sub  Sub Test2(TestSheet As Worksheet)     Dim RowIndex As Long     For RowIndex = 1 To TestSheet.UsedRange.Rows.Count         If TestSheet.Cells(RowIndex, 1).Value = "SomeValue" Then             ' Do something         End If     Next RowIndex End Sub