VBA: Name Sheet in Array

Question

I have one cell with all sheet name write in this way: "Sheet1", "Sheet2", "Sheet3". So I insert this value in single variable SheetNames as String.

Now, I need to get single sheet name and insert. My code is:

varSheets = Array(FogliArray)
    'varSheets = Array("Other", "Research", "IT")

    lngShtCnt = 0
    On Error Resume Next
    For Each varSheet In varSheets
        With wkbSource.Worksheets(varSheet)

If I use varSheets = Array("Other", "Research", "IT") all work correctly. But if I use varSheets = Array(FogliArray) where FogliArray is variable that contains all sheet name ("Other", "Research", "IT") not works.

Please, can you help me? Many thanks, Andrea.

Use Split and you may have to replace the quotes too.

BigBen
– BigBen

2020-03-30 14:35:31 +00:00
Commented Mar 30, 2020 at 14:35 — BigBen
– BigBen, Commented Mar 30, 2020 at 14:35
so FogliArray refers to one cell?

Scott Craner
– Scott Craner

2020-03-30 14:35:35 +00:00
Commented Mar 30, 2020 at 14:35 — Scott Craner
– Scott Craner, Commented Mar 30, 2020 at 14:35
@BigBen you may want to post it as an aswer?

Siddharth Rout
– Siddharth Rout

2020-03-30 14:47:01 +00:00
Commented Mar 30, 2020 at 14:47 — Siddharth Rout
– Siddharth Rout, Commented Mar 30, 2020 at 14:47

BigBen · Accepted Answer · 2020-03-30 14:57:10Z

3

Use Split to return an array of the worksheet names, Replace the quotes, and Trim$ the spaces, something like the following:

Sub Test()
    Dim FogliList As String
    FogliList = Sheet1.Range("A1").Value
    FogliList = Replace(FogliList, """", "") ' no quotes

    Dim FogliArray
    FogliArray = Split(FogliList, ",")

    Dim i As Long
    For i = LBound(FogliArray) To UBound(FogliArray)
        Dim FoglioName As String
        FoglioName = Trim$(FogliArray(i))

        With wkbSource.Worksheets(FoglioName)
            ' Do whatever you wanted to do
        End With
    Next
End Sub

answered Mar 30, 2020 at 14:57

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

VBA: Name Sheet in Array

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related