using inputted values in a worksheet to reference different worksheets vba

Question

I'm trying to create a macro in excel that takes cell values from one tab and creates a reference to these values as worksheets in vba.

An example of a worksheet list would be the following (could be longer or shorter in length):

sheet1
sheet2
sheet3
...

I have been able to store this in an array using the code below. With this array I would like to take these stored values and use them to reference worksheets as could be done manually as in the following.

ws1= wb.Sheets("sheet1")
ws(i)= wb.Sheets("ws(i)")

Any help to solve this or recommending a different approach would be greatly appreciated!

Thanks, M

Dim wb As Workbook: Set wb = ThisWorkbook
Dim wsInputsList As Worksheet
Set wsInputsList = wb.Sheets("InputsTab")

Dim lastrowInputs As Long
lastrowInputs = wsInputsList.Cells(Rows.Count, 1).End(xlUp).Row

Dim i As Integer
i = 1
Do While i < lastrowInputs
Dim ws(1 To 50) As Variant
ws(i) = wsInputsList.Cells(i + 1, 1).Value
i = i + 1
Loop

user_13148075 · Answer

Tim Williams gave you the answer

Here I give you some more suggestions

code 1

Option Explicit

Sub SetSheetList()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim wsInputsList As Worksheet: Set wsInputsList = wb.Sheets("InputsTab")

    Dim lastrowInputs As Long, i As Long
    lastrowInputs = wsInputsList.Cells(Rows.Count, 1).End(xlUp).Row
    ReDim ws(1 To lastrowInputs) As Worksheet 'dim your array only once
    For i = 1 To lastrowInputs
        Set ws(i) = wb.Sheets(wsInputsList.Cells(i, 1).Value)
    Next

End Sub

where

moved array dimming outside the loop so as not to dim it lastrowInputs times…
used a For … Next loop which fist more your case (does what you need, does it clearly and in less stataments)

but this kind of sheets listing is prone to some drawbacks, like having a name in the list that is no more actual. For which case you could consider

Code 2

Option Explicit

Sub SetSheetList2()

    Dim wb As Workbook: Set wb = ThisWorkbook
    Dim wsInputsList As Worksheet: Set wsInputsList = wb.Sheets("InputsTab")

    Dim shtName As String
    Dim lastrowInputs As Long, i As Long, nshts As Long

    lastrowInputs = wsInputsList.Cells(Rows.Count, 1).End(xlUp).Row
    ReDim ws(1 To lastrowInputs) As Worksheet 'dim your array only once

    For i = 1 To lastrowInputs
        shtName = wsInputsList.Cells(i, 1).Value ' store current "possible" sheet name
        If IsSheetThere(wb, shtName) Then  ' if current "possible" sheet name is an "actual" one
            nshts = nshts + 1 ' update sheet names found
            Set ws(nshts) = wb.Sheets(shtName) ' update sheets array
        End If
    Next
    If nshts < lastrowInputs Then ReDim Preserve ws(1 To nshts) As Worksheet 'redim your array to the actual number of items stored, if needed

End Sub

And yet ther's a chance your sheet names list in "InputsTab" isn't exhaustive…

As you see, keeping and using such a list can be much more difficult than it might seem at the beginning: it's a matter of focusing on what you really need and why

using inputted values in a worksheet to reference different worksheets vba

1 Answers