CODEWITHSUNDEEP
arraysexcelvbavariables
Andrea Mario Labate
Andrea Mario Labate

Reputation: 31

VBA: Name Sheet in Array

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.

Upvotes: 1

Views: 514

Answers (1)

BigBen
BigBen

Reputation: 50008

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

Upvotes: 3

Related Questions