0

First post so please excuse any poor etiquette.

I am writing a VBA which produces an information pack for our customers to use. This pack is built to be dynamic based on certain user selections - for example FAQ pages will be specific to selections made by the user. I have built each module to add the relevant sheet names to a contents sheet as it runs. At the end, I pick up this list of sheets, select them and produce a PDF.

What I am trying to do is hide all of the other sheets other than the ones that are selected - I can do this by calling them specifically by name, or by adding "zzz" to the end of sheet names which are pure reference sheets, but I am hoping there is a better way.

The code I want would do this:

  1. Select all of the sheets which have been added to the contents page and create an array (this already works)
  2. Produce the PDF for the selected sheets (this also works)
  3. Hide any sheet which is not in the array (this is what I am struggling with)

Code for producing the PDF is below - note that due to some ill-planned naming, "Contents Array" is a named range on the contents sheet, and "ContentsList" is the VBA array:

For Each cell In wsContents.Range("ContentsArray")
    ContentsList(j) = cell.Value
    wb1.Sheets(cell.Value).Select
    j = j + 1
Next 'cell

wb1.Sheets(ContentsList()).Select

FileName = wsControl.Range("CustomerName") & " Pack " & Format(wsControl.Range("ReportDate"), "dd-mm-yyyy")

PDFFilePath = wb1.Path & "/AutoGenerated Packs/" & FileName & ".pdf"

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=PDFFilePath, _
        Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Pᴇʜ
  • 56,719
  • 10
  • 49
  • 73
David Blakeborough
  • 23
  • 4
  • You can use a `FOR` loop. Something along the line of: `For Each oSH In ThisWorkbook.Sheets`. This will loop through all sheets in your workbook (where `oSH` is a `Worksheet` object). You can then check if `oSH` exists in your array and decided what you want to do based on the outcome. It might be worth reading [why we should avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) – Zac Oct 07 '19 at 13:21

1 Answers1

0

First, you can set your array to the range in one go:

ContentsList = wsContents.Range("ContentsArray").Value

You will then need to loop through your array for each sheet in your workbook to check if it exists... Something like the below (if you use the same method for the array as you already have, else you will need another way to determine j (the number of elements in your array)):

Dim ws As Worksheet
Dim i As Long

    For Each ws In Thisworkbook.Worksheets
        For i = 1 To j
             If ws.Name = ContentsList(i) Then
                 'if found exit for as we do not want to hide
                 Exit For
             End If
             'if not found then hide
             If i = j Then
                 ws.Visible = xlSheetHidden
             End If
        Next j
    Next ws

Hope this helps!

Dean
  • 2,326
  • 3
  • 13
  • 32
  • I would use a `collection` instead of an array for this. Also, you don't need `j`. You can use `UBound` for an array – Zac Oct 07 '19 at 13:47
  • This is perfect - worked perfectly - I think i was over-complicating things by trying to create a kind of inverse array, but looping through each makes sense. The reason I had the array looping through the range was because I wanted to add the contents sheet to the list as well - the line of code before this one is: {ContentsList(0) = "Contents" j = 1} – David Blakeborough Oct 07 '19 at 13:53
  • @Zac if you map the array in one go, it will return a multi dimension array, so you will not be able to use `UBound`. – Dean Oct 07 '19 at 13:55
  • Have a look at [this post](https://stackoverflow.com/questions/26644231/vba-using-ubound-on-a-multidimensional-array) – Zac Oct 07 '19 at 14:39