VBA to search & ignore missing WS' then convert to pdf

[GD03]

Hi Guys thanks for letting me join up


I am trying to get a VBA to create a PDF of my worksheets (combined sheets, not separate pdf's).

The following works perfectly except when BBB (for example) is not in the excel sheet, how do I get the VBA to ignore missing worksheets and still create a PDF of the ones that are included?


I've been searching and searching for this and have gained a basic idea about 'resume next' and 'goto' but as the sheet search is looking through an array I have convinced myself that it's not possible... there must be another way though ?!

Sub CWNformatSelective()

Sheets(Array("Cover", "AAA", "BBB", "CCC", "DDD")).Select
    Sheets("AAA").Activate

    ChDir "C:\Downloads"
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
        "C:\Downloads\Creation.pdf", Quality:= _
        xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
        
        End Sub

 

[Domenic]

Hi,

Try the following...

Sub CWNformatSelective()

    Dim targetSheetNames As Variant
    Dim sheetName As Variant
    Dim currentSheet As Worksheet
    Dim sheetCount As Long

    targetSheetNames = Array("Cover", "AAA", "BBB", "CCC", "DDD")
   
    sheetCount = 0
   
    On Error Resume Next
    For Each sheetName In targetSheetNames
        Set currentSheet = Sheets(sheetName)
        If Not currentSheet Is Nothing Then
            If sheetCount > 0 Then
                currentSheet.Select Replace:=False
            Else
                currentSheet.Select Replace:=True
            End If
            Set currentSheet = Nothing
            sheetCount = sheetCount + 1
        End If
    Next sheetName
    On Error GoTo 0

    If sheetCount = 0 Then
        MsgBox "No sheets found!", vbInformation
        Exit Sub
    End If
   
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\Downloads\Creation.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
   
End Sub

Hope this helps!

 

[JEC]

Or like this

Sub jec()
 Dim sh, s
 For Each sh In Array("Cover", "AAA", "BBB", "CCC", "DDD")
   If Evaluate("isref('" & sh & "'! A1)") Then s = s & IIf(s = "", "", ";") & sh
 Next
 If Len(s) = 0 Then Exit Sub
 Sheets(Split(s, ";")).Select
 ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\Downloads\Creation.pdf"
End Sub

 

[GD03]

Or like this

Sub jec()
 Dim sh, s
 For Each sh In Array("Cover", "AAA", "BBB", "CCC", "DDD")
   If Evaluate("isref('" & sh & "'! A1)") Then s = s & IIf(s = "", "", ";") & sh
 Next
 If Len(s) = 0 Then Exit Sub
 Sheets(Split(s, ";")).Select
 ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\Downloads\Creation.pdf"
End Sub

Thanks both for your help with this, they work perfectly


I now need to understand for myself what each does so that I can fit them fully into my VBA (there's more to it than the snippet I originally posted)
I'm going to start with yours @JEC purely because it has fewer bits to look at, i've a few questions if OK?

> Dim sh, s
Guessing 'sh' is VBA shorthand for sheet (then shortened even further using the Dim to become 's') ?
This has prompted me to note the difference between worksheet and sheet - worksheet collects just excel 'sheets', sheet includes 'chart' sheets

Would this command do the same as 'Dim sh = s' (why the comma and not the '=' ?)


> If Evaluate("isref('" & sh & "'! A1)") Then s = s & IIf(s = "", "", ";") & sh
What's this doing - Where does 'If Evaluate' come from? not seen this before and what the heck is 'llf' ?


> If Len(s) = 0 Then Exit Sub
That's clever, used this for counting digits in a cell before. So this looks for a zero value when searching for the sheet name - IE no sheet by that name
This I take it closes the macro if there are no sheets what so ever, is it essential to have this in the script?


This one is all incorporated into the VBA and appears to be working as intended (even if I don't understand the half of it)


I don't suppose you know how to 'save as' the excel file name instead of 'creation' ?



Thanks again, now to have a look at @Domenic s version

 

[GD03]

Hi,

Try the following...

Sub CWNformatSelective()

    Dim targetSheetNames As Variant
    Dim sheetName As Variant
    Dim currentSheet As Worksheet
    Dim sheetCount As Long

    targetSheetNames = Array("Cover", "AAA", "BBB", "CCC", "DDD")
  
    sheetCount = 0
  
    On Error Resume Next
    For Each sheetName In targetSheetNames
        Set currentSheet = Sheets(sheetName)
        If Not currentSheet Is Nothing Then
            If sheetCount > 0 Then
                currentSheet.Select Replace:=False
            Else
                currentSheet.Select Replace:=True
            End If
            Set currentSheet = Nothing
            sheetCount = sheetCount + 1
        End If
    Next sheetName
    On Error GoTo 0

    If sheetCount = 0 Then
        MsgBox "No sheets found!", vbInformation
        Exit Sub
    End If
  
    ActiveSheet.ExportAsFixedFormat _
        Type:=xlTypePDF, _
        Filename:="C:\Downloads\Creation.pdf", _
        Quality:=xlQualityStandard, _
        IncludeDocProperties:=True, _
        IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
  
End Sub

Hope this helps!

Thanks @Domenic I wish I had gone through this one first, this is more me

Another variation to the way the Dim settings are laid out! is there a proper rule for how these should be written LOL!

 

[JEC]

> Dim sh, s

This is to declare your variables. I often use the default variant/string declaration, without naming them. You can also write this as;

Dim sh as Worksheet, s as String

"sh" and "s" are the only two variables. Sh is indeed short for sheet(you can give it your own name in the for-each-loop if you want)
sh is not becoming "s". "s" is just another string variable which I use to concatenate the worksheetnames if they exist.



> If Evaluate("isref('" & sh & "'! A1)") Then s = s & IIf(s = "", "", ";") & sh
Evaluate does an evaluation of an Excel formula. For each sheet, it checks if cell "A1" of that sheet exists. If so, it will be added to "s".
"Iif" is a short way of using If Then Else in VBA. For simple logical checks you could use this variant.



> If Len(s) = 0 Then Exit Sub
This line just checks if at least one sheet exists. When the length of variable "s" is 0, no sheets in your array do exist (because it is a concatenated string, delimited with ";" of all existing sheetnames)
When you don't have sheets to print, you can just exit the sub to avoid errors.


I don't suppose you know how to 'save as' the excel file name instead of 'creation' ?

ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\Downloads\" & replace(Thisworkbook.name,".xlsm", ".pdf")

 

[Domenic]

Thanks @Domenic

You're very welcome!

Another variation to the way the Dim settings are laid out! is there a proper rule for how these should be written LOL!

Another way to dedclare variables, other than my example, would be to declare similar variables on the same line...

Sub CWNformatSelective()

    Dim targetSheetNames As Variant, sheetName As Variant
    Dim currentSheet As Worksheet
    Dim sheetCount As Long
 
    'etc
    '
    '
    '
End Sub

Yet another way would be to declare them close to where they are being used...

Sub CWNformatSelective()

    Dim targetSheetNames As Variant
    targetSheetNames = Array("Cover", "AAA", "BBB", "CCC", "DDD")
 
    Dim sheetCount As Long
    sheetCount = 0
     
    Dim sheetName As Variant
    Dim currentSheet As Worksheet
    On Error Resume Next
    For Each sheetName In targetSheetNames
        Set currentSheet = Sheets(sheetName)
        If Not currentSheet Is Nothing Then
            If sheetCount > 0 Then
                currentSheet.Select Replace:=False
            Else
                currentSheet.Select Replace:=True
            End If
            Set currentSheet = Nothing
            sheetCount = sheetCount + 1
        End If
    Next sheetName
    On Error GoTo 0

    'etc
    '
    '
    '
End Sub

Which method you choose I think is largely a matter of preference, although some advocate for one method of the other.

Cheers!

 

[GD03]

I don't suppose you know how to 'save as' the excel file name instead of 'creation' ?

ActiveSheet.ExportAsFixedFormat xlTypePDF, "C:\Downloads\" & replace(Thisworkbook.name,".xlsm", ".pdf")

My excel file is in xlsx format so I have swapped xlsx for xlsm and the rename works perfectly without the .xlsx

 

[GD03]

Hello all

So I have got the above to work for formatting all sheets the same and for printing/pdf'ing but how do I use it if I don't have an array?



I want to post a left header but only to a certain sheet

Currently using the below which works fine until a missing sheet comes up then it just jumbles up the message - posting it to the wrong sheet

        Resume Next
On Error GoTo CCC1
Sheets("BBB").Select

'Note
    With ActiveSheet.PageSetup
        .LeftHeader = "&16 &K92D050 Notes for BBB" & Chr(13) & Chr(10) & "MESSAGE TEXT for sheet BBB"
    End With
 

CCC1:
          Resume Next
On Error GoTo EEE1
    Sheets("CCC").Select

'Note
    With ActiveSheet.PageSetup
        .LeftHeader = "&16 &K92D050 Notes for CCC" & Chr(13) & Chr(10) & "MESSAGE TEXT for sheet CCC"
    End With


DDD1:
and as above repeats....