VBA to create an Array from a list

[Monkeyboyz]

I have done this before but just can't remember how to do it.

I have a dynamic range that is a list of some of the tabs I have in a worksheet. I need to turn that range into an array so I can then select it all with the following Sub and create a pdf.
The dynamic range contains the tabs Wall 1, Wall 2 etc. I can create it like I have below but the range continually changes

Sub CreatePDF()
    ThisWorkbook.Sheets(Array("Wall 1", "Wall 2", "Wall 3", "Wall 4")).Select


    Sheets("Wall 1").Activate


    ActiveSheet.ExportAsFixedFormat _
            Type:=xlTypePDF, _
            Filename:="C:\temp\temp.pdf", _
            Quality:=xlQualityStandard, _
            IncludeDocProperties:=True, _
            IgnorePrintAreas:=False, _
            OpenAfterPublish:=True


    Sheets("Wall 1").Select
    Range("A1").Select
End Sub

 

[Mohammad Basem]

Hi Monkeyboyz,

Try the following,

[FONT=Consolas][SIZE=2][COLOR=Navy]Dim bl1stSheet As Boolean
Dim wsWS As Worksheet
 
bl1stSheet = True
For Each wsWS In Worksheets
   If wsWS.Visible = xlSheetVisible And Left(wsWS.Name, 4) = "Wall" Then
      wsWS.Select bl1stSheet
      bl1stSheet = False
   End If
Next
[/COLOR][/SIZE][/FONT]

 

[Rick Rothstein]

I have a dynamic range that is a list of some of the tabs I have in a worksheet.

Where is this dynamic range located at? What sheet is it on? What cell contains the first item in the list? Which direction does the list go in... down a column or across a row?

 

[Monkeyboyz]

The dynamic range is located on a Sheet called "Front Sheet", cells Q12:Q25 and the list goes down

 

[Monkeyboyz]

Thanks Mohammed your code works to an extent, however I do need another couple of sheets included in the selected worksheets, one being "Front Sheet" and the other being "T&Cs"

 

[Rick Rothstein]

The dynamic range is located on a Sheet called "Front Sheet", cells Q12:Q25 and the list goes down

In order to create a one-dimensional array out of that list, try this in your code...

Dim SheetNames As Variant
SheetNames = Application.Transpose(Range("Q12", Cells(Rows.Count, "Q").End(xlUp)))

SheetNames is now a one-dimensional array that you can iterate in a loop as needed.

 

[Mohammad Basem]

Here you go,

[FONT=Consolas][SIZE=2][COLOR=Navy]Dim bl1stSheet As Boolean
Dim wsWS As Worksheet
 
bl1stSheet = True
For Each wsWS In Worksheets
   If wsWS.Visible = xlSheetVisible _
      And (Left(wsWS.Name, 4) = "Wall" _
      Or wsWS.Name = "Front Sheet" _
      Or Left(wsWS.Name, 4) = "T&Cs") _
      Then
      wsWS.Select bl1stSheet
      bl1stSheet = False
   End If
Next[/COLOR][/SIZE][/FONT]

 

[Monkeyboyz]

Thank you both for your help, now all working

 

[Mohammad Basem]

Al-hamdullah, thank you for the feedback!