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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
807
Office Version
  1. 365
Platform
  1. Windows
What if you create the pdf's while using a loop?
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
What if you create the pdf's while using a loop?
Hi JEC,

Erm...i'm not familiar with VBA coding so not sure where to start with that other than googling options around it. Any tips on where to start with adapting the code? I need all 84 worksheets to be in 1 pdf document.

KR
Jmorrison67
 

Jmorrison67

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

Erm...i'm not familiar with VBA coding so not sure where to start with that other than googling options around it. Any tips on where to start with adapting the code? I need all 84 worksheets to be in 1 pdf document.

KR
Jmorrison67
Also sheets that need to be run to pdf start from tab 4 and run consecutively for 84 sheets.
 

JEC

Well-known Member
Joined
Aug 21, 2021
Messages
807
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

All sheets will be saved with the same name now. That is not possible.
When using a loop you have to make sure that every pdf has a different name.

Something like:

VBA Code:
Sub jec()
Application.ScreenUpdating = False
c00 = "C:\Users\xxx\Downloads\Export"

For i = 4 To 84
  Sheets(i).ExportAsFixedFormat xlTypePDF, c00 & i & ".pdf"
Next
End Sub
 

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
All sheets will be saved with the same name now. That is not possible.
When using a loop you have to make sure that every pdf has a different name.

Something like:

VBA Code:
Sub jec()
Application.ScreenUpdating = False
c00 = "C:\Users\xxx\Downloads\Export"

For i = 4 To 84
  Sheets(i).ExportAsFixedFormat xlTypePDF, c00 & i & ".pdf"
Next
End Sub
Hmmm...this produces a pdf for each tab, but I just need a 84 page pdf i.e. one document.
Is there a way the loop can just go through and select the worksheets, then finish the loop and move on to "
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy"), _
OpenAfterPublish:=False, IgnorePrintAreas:=False" bit and save all 84 tabs as one file?

Or as the original part where 75 out of the 84 tabs works in the 'sheets(array......) part of the code, could that be one loop and then a secondary loop containing the 9 additional tabs which exceed the array limit runs straight after the 1st loop and then moves on to save to pdf?

Something like:

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

' << Add additional code here >>

Sheets(Array("76", "77", "78", "79", "80", "81", "82", "83", "84").select

' then moves on to the save bit:
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy"), _
OpenAfterPublish:=False, IgnorePrintAreas:=False"

KR
Jmorrison67
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows

ADVERTISEMENT

The code below is most likely to do what you want. Amend the SHEETSTOEXCLUDE constant to suit your situation.

VBA Code:
Public Sub Jmorrison67()
   
    Const SHEETSTOEXCLUDE As String = "*Sheet1*Sheet2*Sheet3*Sheet4*Sheet5*Sheet6*"     ' << change to suit, sheet names surrounded with * character
   
    Dim Sht As Worksheet, i As Long, arr() As String
   
    With ThisWorkbook
        i = UBound(Split(SHEETSTOEXCLUDE, "*")) - 1
        ReDim arr(1 To (.Worksheets.Count - i))
        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
    End With
    Sheets(arr).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy"), OpenAfterPublish:=False, IgnorePrintAreas:=False
End Sub
 

Akuini

Well-known Member
Joined
Feb 1, 2016
Messages
3,653
Office Version
  1. 365
Platform
  1. Windows
Try this:
Put the 84 sheet's name say in Sheets("Sheet1").Range("A1:A84").
This code will select all of them.
VBA Code:
Dim ary
ReDim ary(1 To 84)
For i = 1 To 84
    ary(i) = Sheets("Sheet1").Range("A" & i).Value
Next

Sheets(ary).Select

To get the name of all sheets in the workbook, you can use this:
VBA Code:
Sub getName()
Dim ws As Worksheet
For Each ws In Worksheets
Debug.Print ws.Name
Next
End Sub
 
Last edited:

Jmorrison67

Board Regular
Joined
Aug 20, 2021
Messages
51
Office Version
  1. 2016
Platform
  1. Windows
The code below is most likely to do what you want. Amend the SHEETSTOEXCLUDE constant to suit your situation.

VBA Code:
Public Sub Jmorrison67()
  
    Const SHEETSTOEXCLUDE As String = "*Sheet1*Sheet2*Sheet3*Sheet4*Sheet5*Sheet6*"     ' << change to suit, sheet names surrounded with * character
  
    Dim Sht As Worksheet, i As Long, arr() As String
  
    With ThisWorkbook
        i = UBound(Split(SHEETSTOEXCLUDE, "*")) - 1
        ReDim arr(1 To (.Worksheets.Count - i))
        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
    End With
    Sheets(arr).Select
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy"), OpenAfterPublish:=False, IgnorePrintAreas:=False
End Sub
Good evening GWteB,

I tried to run the above and got this:

1633043613699.png


Debug highlighted:
1633043638165.png


Any idea's?

KR
Jmorrison67
 

GWteB

Well-known Member
Joined
Sep 10, 2010
Messages
2,599
Office Version
  1. 2013
Platform
  1. Windows
The workbook you are running the code from, has to be the current / active workbook.
I should have added a line of code to ensure that condition, like ...

Rich (BB code):
End With
ThisWorkbook.Activate
Sheets(arr).Select
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,393
Messages
5,769,824
Members
425,574
Latest member
grimeslisa

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