Home > Blockchain >  Passing Array of strings to Sheets() in VBA
Passing Array of strings to Sheets() in VBA

Time:12-08

i intend to save the sheets as pdf which works fine, however when i wanted to save sheets with names stored in some cell i run into a problem i have written following function in VBA and i cant' figure out what is the problem or how to walk around it

Sub Export_to_pdf()

Dim SheetArr() As String
.
.
.

SheetArr = Split(Worksheets("EXPORT").Range("C12").Value, ",")

Sheets(SheetArr).Activate
.
.
.
End Sub

the problem with code above is that when i pass to Sheets regular array like Sheets(Array("sh1","sh2")).activate - its fine but when i use the split on cell which content looks like "sh1","sh2" it throws error from what i tried to debug i found out that possibly the problem is that Split returns String array as opposed to variant array which i suspect to be the problem, how can i solve this problem ? casting string values with CVar doesn't seem to work

Thanks for any answers

CodePudding user response:

This works fine for me:

Sub Export_to_pdf()

    Dim SheetArr
    SheetArr = Split(Sheet1.[A1], ",")
    Sheets(SheetArr).Select  '<< not Activate

End Sub

In A1: Sheet1,Sheet2

  • Related