1

I get a type mismatch error when selecting multiple excel sheets (in an array) for export to a single pdf.

I have multiple sheets in a single excel workbook that I want to export to a single pdf for review and printing. I can export single sheets, each to its own pdf. To handle multiple, varying sheets I have tried to set up a dynamic array with the intent to export the sheets in the array. After the array is defined the sheets(array) are selected and at this point, a type mismatch error occurs. The code below is adapted from an example I found online. Additionally, I have tried to view the contents of the array in lieu of select/export to pdf. Type mismatch occurs with this code as well. This code is shown below the '@@@@ line below.

I have reviewed 3 or 4 posts on VBA export multiple sheets to pdf and on type mismatch but could not identify any applicable solutions (but could not understand much of what I read either).

And also, "ThisWorkbook.Sheets" code is shown in all the examples I've seen as having a capital 'S' in Sheets. Every time I try to capitalize the 'S', it is automatically reverted back to a lowercase 's'. ????

Any help is greatly appreciated; thank you.

Option Explicit

LastRowRoutes = VV  'LastRowRoutes & VV are Variant
Dim M As Variant
Dim arr() As Variant
Dim cnt As Variant
Dim strFilename As String, strFilepath As String
Dim wksSheet1 As Worksheet
Dim printSheets As Variant
printSheets = arr
Set wksSheet1 = ThisWorkbook.sheets("Sheet1")
Set wksSheet1 = Worksheets("Sheet1")
strFilepath = "E:\"
With wksSheet1
   strFilename = strFilepath & "TripSheets_Print.pdf"
End With
cnt = 0
For M = 22 To LastRowRoutes
    If Worksheets(M).Range("B4").Value <> "" Then
        ReDim Preserve arr(0 To cnt)
        arr(cnt) = Worksheets(M).Range("A1:K43")
        cnt = cnt + 1
    Else
    End If
Next
ThisWorkbook.sheets(printSheets).Select  '*Type Mismatch here
wksSheet1.ExportAsFixedFormat Type:=xlTypePDF, Filename:=strFilename, Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
'@@@@@@@@ code below to view array contents in lieu of select & export
For M = LBound(arr) To UBound(arr) 'type mismatch here as well
    msg = msg & arr(M) & vbNewLine
Next M
MsgBox "the values of my array are: " & vbNewLine & msg
Miles Fett
  • 711
  • 4
  • 17
j22
  • 31
  • 3
  • 1
    You're trying to use an array as a single worksheet? Perhaps you mean to do `ThisWorkbook.Worksheeets(printSheets(0))...`? Also, as far as I can tell, both `arr` and therefore `printSheets` are empty...Add `printSheets = arr` after the `arr` is updated in that `For M` loop. – BruceWayne Aug 20 '19 at 21:23
  • `printsheets(?)` – urdearboy Aug 20 '19 at 21:23
  • 1
    To fix the lowercase `s` in `sheets` - add a new variable - `Dim Sheets`, and then delete it. – BigBen Aug 20 '19 at 21:24
  • 1
    @BruceWayne each element of `arr` is an array: `arr(cnt) = Worksheets(M).Range("A1:K43")`. – BigBen Aug 20 '19 at 21:27
  • @BigBen - Yeah, but he declares `printSheets = arr` before `arr` is assigned anything as far as I can tell. – BruceWayne Aug 20 '19 at 21:29
  • 1
    @BruceWayne I agree with that problem, it's just that `arr` is problematic too... – BigBen Aug 20 '19 at 21:30
  • Welcome to SO. You need to put the 'printSheets = arr' statement after your 'For, Next' loop or it will always be empty. If you step through the code, does arr(cnt) actually get any values inserted? – GoodJuJu Aug 20 '19 at 21:38
  • Thank you for your comments. I tried to edit the posting but the editor keeps throwing up error that it's code & to indent 4 spaces - can't figure out how to get around it. Lower case 's' fixed, thanks. Moving printSheets=arr after loop did not help. See https://stackoverflow.com/questions/14404650. This works but I have to code in the specific sheets in the array which I can't do in practice. Question is How do I dynamically list the sheets in the array? If I can do that I can solve the problem. Thank you - j22 – j22 Aug 21 '19 at 16:50

0 Answers0