Excel: Select Worksheet dynamically (Without sheet name)

Question

The sheets inside my Excel file are dynamic. I have a button that creates a sheet with it's new name.

Is it possible to select a worksheet in dynamic way? Or without knowing the names of the sheets.

Here's my vba code for printing a specific range of cells

Private Sub CommandButton1_Click()
    Sheets("-32628").Select
    range("A1:BZ18").Select
    ActiveSheet.PageSetup.PrintArea = "A1:BZ18"
    ActiveWindow.SelectedSheets.PrintOut from:=1, To:=1, Copies:=1, Collate _
      :=True
End Sub

What I want is to select the Sheetname: -32628 without putting the actual name of sheet?

score 3 · Answer 1 · answered Jan 08 '19 at 09:20

I have a button that creates a sheet with it's new name.

If you are creating a sheet in a button click then it is pretty simple. Declare a worksheet object on top of your code so that it can be accessed by other procedures and then work with it. For example

Dim ws As Worksheet

Private Sub CommandButton1_Click()
    If Not ws Is Nothing Then
        ws.PageSetup.PrintArea = "A1:BZ18"

        ActiveWindow.SelectedSheets.PrintOut _
        from:=1, To:=1, Copies:=1, Collate:=True
    End If
End Sub

Private Sub CommandButton2_Click()
    Set ws = ThisWorkbook.Sheets.Add
    ws.Name = "-32628"
End Sub

Pᴇʜ · Answer 2 · 2019-01-08T09:20:40.293

First of all Avoid using Select in Excel VBA. This slows down your code a lot and is a very bad practice.

Instead reference your worksheet directly eg instead of ActiveSheet:

Worksheets("-32628").PageSetup.PrintArea = "A1:BZ18"

There are 3 ways to reference a worksheet:

By its visible tab name
```
Worksheets("YourSheetName")
```
By its tab position
```
Worksheets(1) 'first sheet in tab list
```

By its VBA name

Sheet1 'note that 1 is not the position of the sheet in the tab list. It's just a name.

Note that the VBA name of the sheet can be changed in the Properties window of the VBA-Editor.
Also note that Worksheets("Sheet1") and Worksheets(1) and Sheet1 can be 3 completely different worksheets. I recommend not to use numbers and give them distinct names as the numbers can easily cause confusion.

Excel: Select Worksheet dynamically (Without sheet name)

2 Answers2