Excel VBA: set a range as an activate sheet

Question

I'm currently working on a workbook where all the worksheet information is stored in the worksheet("overview"). In the column "A:A", I have the name of each worksheet. What I want to make is macro that runs through every cell that is listed in column "A:A" and print out that worksheet. The printing code is not a problem. The problem is I don't know to refer to a cell as a worksheet. I came up with this solution but I doesn't work.

Worksheets(Range("A1")).Activate

Is there a possibility to refer to an activate worksheet from a cell?

What is the format of the cell? That should work if the name is an exact match. If your value in A1 includes workbook names or something it won't work. You would usually do this like `Worksheets("Sheet1")` so you need to make sure `Range("A1") = Sheet1` for the substitution to work. This should be clear to you, but you can always share sample data of A:A to be sure — urdearboy, Feb 20 '20 at 16:01
Note that `Range("A1")` is implicitly A1 on the *ActiveSheet*, which may not be the sheet you want. — BigBen, Feb 20 '20 at 16:02
As a side note you probably don't need to activate the workbook and can just refer to it. — Warcupine, Feb 20 '20 at 16:02
@urdearboy The information in column "A:A" is 1, 2, 3, 4 etc. Just like the names of the other worksheets — Wouter Groeneweg, Feb 20 '20 at 16:06
My initial comment was wrong. I was able to reproduce issue and resolve with `Range.Value` — urdearboy, Feb 20 '20 at 16:11

score 0 · Answer 1 · answered Feb 20 '20 at 16:05

0

Try

Worksheets(Range("A1").Value).Activate

As stated in comments, your code would benefit from qualifying your objects here (Range("A1") on what sheet?). Lastly, you rarely ever need to activate a sheet to perform operations on it. Just skip the Activate and jump directly to your action statement

answered Feb 20 '20 at 16:05

urdearboy

14,439
5
28
58

Thank you for your answer! I tried it with the action statement I have after this one but it doesn't seem to work. The statement is: `Range("A1:C20").Select` `Selection.PrintOut Copies:= 1, Collate:= True` – Wouter Groeneweg Feb 20 '20 at 16:19
That is really a different problem and likely related to the fact that you are relying on `Selecting` ranges. Automation requires nothing to be selected to work. See @Tim's solution. Why not skip the `Select` and try `ws.Range("A1:C20").PrintOut`. No selection needed.... If you can get the sheet to activate then your question is answered. Anything else should be a new post with your updated code and new problem – urdearboy Feb 20 '20 at 16:26

score 0 · Answer 2 · answered Feb 20 '20 at 16:08

0

Dim ws As Worksheet

With ThisWorkbook 'or ActiveWorkbook, or ?
    Set ws = .Worksheets(.Worksheets("Overview").Range("A1").Value)
End With

'do something with ws

answered Feb 20 '20 at 16:08

Tim Williams

154,628
8
97
125

Vityata · Answer 3 · 2020-02-20T16:28:30.560

The following code refers to a given worksheet through its name, written as a text in column A:A in Worksheets("Overview"):

Sub PrintWorksheetNames()

    Dim myCell As Range
    Dim lastCell As Long
    lastCell = LastRow("Overview")

    For Each myCell In ThisWorkbook.Worksheets("Overview").Range("A1:A" & lastCell).Cells
        Dim wksName As String
        wksName = myCell.Text
        ThisWorkbook.Worksheets(wksName).Range("A1:C20").PrintOut Copies:=1, Collate:=True
    Next

End Sub

Public Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long

    Dim ws As Worksheet
    Set ws = ThisWorkbook.Worksheets(wsName)
    LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

End Function

    Public Function LastRow(wsName As String, Optional columnToCheck As Long = 1) As Long

        Dim ws As Worksheet
        Set ws = ThisWorkbook.Worksheets(wsName)
        LastRow = ws.Cells(ws.Rows.Count, columnToCheck).End(xlUp).Row

    End Function

The range, in which the names of the worksheets are written is defined by ThisWorkbook.Worksheets("Overview").Range("A1:A" & lastCell).Cells.
The abovementioned range could be hardcoded, if it is not going to be changed - ThisWorkbook.Worksheets("Overview").Range("A1:A8").Cells. Thus the additional function LastRow() is not needed.
lastCell is the result of LastRow() for column A. A is the first column, thus the optional parameter takes care of it.
How to avoid using Select in Excel VBA (This is a must-read for every VBA person)

Excel VBA: set a range as an activate sheet

3 Answers3

Try