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

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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:
1633017720729.png



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

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
It looks like there's sheet with apostrophe (') in its name.
Try:
VBA Code:
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?

1633088910860.png


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
 

Excel Facts

Format cells as currency
Select range and press Ctrl+Shift+4 to format cells as currency. (Shift 4 is the $ sign).

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,641
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
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
 
Solution

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
Sorry, my mistake.
Try replacing this line
VBA Code:
            If Not InStr(1, SHEETSTOEXCLUDE, Sht.Name, vbTextCompare) > 0 Then
with this line
VBA Code:
            If Not InStr(1, SHEETSTOEXCLUDE, "*" & Sht.Name & "*" , vbTextCompare) > 0 Then
Hi GWteB,

Replaced it and got this:
1633095439019.png


1633095456150.png



It also added 84 blank tabs to my workbook:
1633095497454.png


KR
Jmorrison67
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

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.
VBA Code:
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

Well-known Member
Joined
Sep 10, 2010
Messages
2,585
Office Version
  1. 2013
Platform
  1. Windows
@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.

VBA Code:
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

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

@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.

VBA Code:
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,641
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
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

Well-known Member
Joined
Feb 1, 2016
Messages
3,641
Office Version
  1. 365
Platform
  1. Windows
Glad it works. :)
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,697
Messages
5,765,977
Members
425,320
Latest member
Galin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top