Sheets(Array) help needed. 84 worksheets trying to run with code but limit seems to be 75, how to overcome 'too many line continuations' error?

[Jmorrison67]

Good evening the ever amazing MrExcel community ,

I have another problem I am trying to overcome....I have a workbook with 95 worksheets of which I need to save 84 on a monthly basis as a pdf and distribute to the business. Current WoW is just to select the tabs and save manually but looking to get macro to run rather than having to do that by just pressing a button. So...given the tab names are very long, I recorded a macro to pull in the names rather than me having to type them into the code manually. Macro recorded all sheets when I selected them when I went into developer mode to get copy the names into my code.

When I add all 84 to the code I get this error:

It appears to work up to 75 worksheets being added but unable to include the other 9 worksheets in the save to pdf job......I can't put the whole code into here due to confidentiality (but replaced it with numbers as the tab names) but here is what currently works with 75 worksheets:

Sub ExportAsPDFXXPack()
Dim FolderPath As String
FolderPath = "C:\Users\morrisonj\XXPack"

Sheets(Array("1", "2", "3", "4", "5", "6", "7", "8", "9", "10", "11", "12", "13", "14", "15", "16", "17", "18", "19", "20", "21", "22", "23", "24", "25", "26", "27", "28", "29", "30", "31", "31", "32", "33", "34", "35", "36", "37", "38", "39", "40", "41", "42", "43", "44", "45", "46", "47, "48", "49", "50", "51", "52", "53", "54", "55", "56", "57", "58", "59", "60", "61", "62", "63", "64", "65", "66", "67", "68", "69", "70", "71", "72", "73", "74", "75")).Select

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy"), _
OpenAfterPublish:=False, IgnorePrintAreas:=False

MsgBox "All PDF's have been successfully exported."
End Sub


I tried to just add another sheets(Array("76", "77", "78", "79", "80", "81", "82", "83", "84").select line below (but above activesheet.export...)> but then the pdf job only saves down this 9 and excludes the 75 above > i'm certain there is a simple solution but need some help with it. Can anyone help editing the code above to allow me to run off all 84 worksheets? (This is 84 out of 95 in the workbook FYI)
Also for my understanding, is there a limit as to how many worksheets you can add to the array?


As always really appreciate your help

KR
Jmorrison67

 

[Jmorrison67]

It looks like there's sheet with apostrophe (') in its name.
Try:

Dim i As Long, ary, tx As String, q As String
ReDim ary(1 To 84)
For i = 1 To 84
    tx = Sheets("Sheet1").Range("A" & i).Value
    q = Replace(tx, "'", "''")
    If Not Evaluate("ISREF('" & q & "'!A1)") Then MsgBox "Sheets " & q & " doesn't exist": Exit Sub
    ary(i) = tx
Next

Sheets(ary).Select

Hi,

Yes - The tab names are quite long and many have for example "KPI's" in the names.

That check is very good - as showed up 4 tabs which had typo errors.....
1st find = a tab which was named .....Obs..... but the text i had copied into the range on Sheet1 said Obso
2nd find = a tab which had 2 x blank spaces after the tab name
3rd find = "'" on the word KPI's on one tab but should actually have been KPIs
4th find = 1 x blank space after tab name

But this works and produces the output as expected

Couple follow up questions

1. In the saving part of the code, is there a way to change it so that each time it saves down the file, it doesnt overwrite the previously saved copy - so like v1 or v2 etc etc >
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy"), _

2. With putting the tab names into 'Sheet1' from A1 through A84, if I wanted to have multiple different reports with variations of the different tabs, could i have a list say in Col B, Col C etc etc and then just adjust the code to look at those columns?

Where i have highlighted A, just change that to B or C etc?
Would i also need to change the ary 1 to 84 to match the number of tabs i had in say col B if that was 10?


KR
Jmorrison67

 

[GWteB]

@Jmorrison67, have you noticed my post #20?

 

[Akuini]

1. In the saving part of the code, is there a way to change it so that each time it saves down the file, it doesnt overwrite the previously saved copy - so like v1 or v2 etc etc >
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy"), _

Not sure about this, perhaps other members could help you with this.

2. With putting the tab names into 'Sheet1' from A1 through A84, if I wanted to have multiple different reports with variations of the different tabs, could i have a list say in Col B, Col C etc etc and then just adjust the code to look at those columns?

1. List must start at row 1
2. The number of rows is dynamic.
3. Type the column letter in the inputbox.

Dim tx As String, q As String
Dim n As Long, i As Long, ary, z
z = Application.InputBox("Type the column letter:", , Type:=2)
If z = False Or z = "" Then Exit Sub

With Sheets("Sheet1")
    n = .Range(z & .Rows.Count).End(xlUp).Row
    If .Range(z & n) = "" Then MsgBox "Column " & UCase(z) & " is empty": Exit Sub
ReDim ary(1 To n)
For i = 1 To n
    tx = .Range(z & i).Value
    q = Replace(tx, "'", "''")
    If Not Evaluate("ISREF('" & q & "'!A1)") Then MsgBox "Sheets " & q & " doesn't exist": Exit Sub
    ary(i) = tx
Next
End With

Sheets(ary).Select

 

[Jmorrison67]

Sorry, my mistake.
Try replacing this line

            If Not InStr(1, SHEETSTOEXCLUDE, Sht.Name, vbTextCompare) > 0 Then

with this line

            If Not InStr(1, SHEETSTOEXCLUDE, "*" & Sht.Name & "*" , vbTextCompare) > 0 Then

Hi GWteB,

Replaced it and got this:

It also added 84 blank tabs to my workbook:

KR
Jmorrison67

 

[Jmorrison67]

Not sure about this, perhaps other members could help you with this.

1. List must start at row 1
2. The number of rows is dynamic.
3. Type the column letter in the inputbox.

Dim tx As String, q As String
Dim n As Long, i As Long, ary, z
z = Application.InputBox("Type the column letter:", , Type:=2)
If z = False Or z = "" Then Exit Sub

With Sheets("Sheet1")
    n = .Range(z & .Rows.Count).End(xlUp).Row
    If .Range(z & n) = "" Then MsgBox "Column " & UCase(z) & " is empty": Exit Sub
ReDim ary(1 To n)
For i = 1 To n
    tx = .Range(z & i).Value
    q = Replace(tx, "'", "''")
    If Not Evaluate("ISREF('" & q & "'!A1)") Then MsgBox "Sheets " & q & " doesn't exist": Exit Sub
    ary(i) = tx
Next
End With

Sheets(ary).Select

OK
Think what I will do is create a tab Sheet1 and drop in the 84 tab names (which works), create a tab sheet2 and drop in 10 tab names and then just have module 2 etc with the sheet2 reference - think that would keep it fairly straight forward. this will work won't it? The more complicated the code, the harder it will be to fix if something goes astray.
Your previous post was the solution though.
I have changed the format of the output bit to be : Format(Now, "dd-mm-yyyy hhmm") > this should resolve the naming of the file and it being overwritten i think, previously it was Format(Date, "dd-mm-yyyy")

KR
Jmorrison67

 

[GWteB]

@Jmorrison67, have checked everything again. The error that occurred every time on the Select line could only have been caused by a typo in the list of names of worksheets to be excluded, or a duplication in this list. After all, the names of the worksheets to be printed were retrieved in a loop from the workbook itself, so they were always correct.
I've ammended my code in a way that the foregoing is taken into account. Note the separate procedure. Although you have a solution yet, I would appriciate if you would like to test the code below.

Public Sub Jmorrison67_v3()
   
    Const SHEETSTOEXCLUDE As String = "*Sheet33*Sheet10*Sheet11*Sheet12*Sheet 21*Sheet's 22*Sheet23*Sheet33*Sheet33*Sheet35*"     ' << change to suit, sheet names surrounded with * character
   
    Dim Sht As Worksheet, arr As Variant, i As Long, n As Long

    With ThisWorkbook
        If Len(SHEETSTOEXCLUDE) > 2 And Left(SHEETSTOEXCLUDE, 1) = "*" And Right(SHEETSTOEXCLUDE, 1) = "*" Then
            arr = RemoveDuplicates(Split(SHEETSTOEXCLUDE, "*"))
            For i = LBound(arr) To UBound(arr)
                Set Sht = Nothing
                On Error Resume Next
                Set Sht = .Worksheets(arr(i))
                On Error GoTo 0
                If Not Sht Is Nothing Then
                    n = n + 1
                End If
            Next i
        End If
        ReDim arr(1 To (.Worksheets.Count - n))
        i = 1
        For Each Sht In .Worksheets
            If Not InStr(1, SHEETSTOEXCLUDE, "*" & Sht.Name & "*", vbTextCompare) > 0 Then
                arr(i) = Sht.Name
                i = i + 1
            End If
        Next Sht
        .Activate
    End With
    Sheets(arr).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy hhmmss"), OpenAfterPublish:=False, IgnorePrintAreas:=False
End Sub

Public Function RemoveDuplicates(ByVal argArray As Variant) As Variant
    Dim Dict As Object, Itm As Variant, Dummy As Variant
    Set Dict = CreateObject("Scripting.Dictionary")
    With Dict
        For Each Itm In argArray
            If Len(Itm) > 0 Then
                Dummy = .Item(Itm)
            End If
        Next
        RemoveDuplicates = .Keys
    End With
End Function

 

[Jmorrison67]

@Jmorrison67, have checked everything again. The error that occurred every time on the Select line could only have been caused by a typo in the list of names of worksheets to be excluded, or a duplication in this list. After all, the names of the worksheets to be printed were retrieved in a loop from the workbook itself, so they were always correct.
I've ammended my code in a way that the foregoing is taken into account. Note the separate procedure. Although you have a solution yet, I would appriciate if you would like to test the code below.

Public Sub Jmorrison67_v3()
  
    Const SHEETSTOEXCLUDE As String = "*Sheet33*Sheet10*Sheet11*Sheet12*Sheet 21*Sheet's 22*Sheet23*Sheet33*Sheet33*Sheet35*"     ' << change to suit, sheet names surrounded with * character
  
    Dim Sht As Worksheet, arr As Variant, i As Long, n As Long

    With ThisWorkbook
        If Len(SHEETSTOEXCLUDE) > 2 And Left(SHEETSTOEXCLUDE, 1) = "*" And Right(SHEETSTOEXCLUDE, 1) = "*" Then
            arr = RemoveDuplicates(Split(SHEETSTOEXCLUDE, "*"))
            For i = LBound(arr) To UBound(arr)
                Set Sht = Nothing
                On Error Resume Next
                Set Sht = .Worksheets(arr(i))
                On Error GoTo 0
                If Not Sht Is Nothing Then
                    n = n + 1
                End If
            Next i
        End If
        ReDim arr(1 To (.Worksheets.Count - n))
        i = 1
        For Each Sht In .Worksheets
            If Not InStr(1, SHEETSTOEXCLUDE, "*" & Sht.Name & "*", vbTextCompare) > 0 Then
                arr(i) = Sht.Name
                i = i + 1
            End If
        Next Sht
        .Activate
    End With
    Sheets(arr).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy hhmmss"), OpenAfterPublish:=False, IgnorePrintAreas:=False
End Sub

Public Function RemoveDuplicates(ByVal argArray As Variant) As Variant
    Dim Dict As Object, Itm As Variant, Dummy As Variant
    Set Dict = CreateObject("Scripting.Dictionary")
    With Dict
        For Each Itm In argArray
            If Len(Itm) > 0 Then
                Dummy = .Item(Itm)
            End If
        Next
        RemoveDuplicates = .Keys
    End With
End Function

I have checked the 84 tab names and they are all unique, but let me rerun it with the above and get back to you shortly

KR
Jmorrison67

 

[Akuini]

Think what I will do is create a tab Sheet1 and drop in the 84 tab names (which works), create a tab sheet2 and drop in 10 tab names and then just have module 2 etc with the sheet2 reference - think that would keep it fairly straight forward. this will work won't it? The more complicated the code, the harder it will be to fix if something goes astray.

Actually the last code I gave you make it simpler. Just put the various list in various columns in sheet1, then run the code, then type the column letter that you want in the inputbox then OK.
You don't have to change anything in the code.

 

[Jmorrison67]

Actually the last code I gave you make it simpler. Just put the various list in various columns in sheet1, then run the code, then type the column letter that you want in the inputbox then OK.
You don't have to change anything in the code.

Good afternoon Akuini,

Apologies for the delay in responding - tested this afternoon and last code works as expected. Great result!

KR
Jmorrison67

 

[Akuini]

Glad it works.