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]

Try this:
Put the 84 sheet's name say in Sheets("Sheet1").Range("A1:A84").
This code will select all of them.

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:

Sub getName()
Dim ws As Worksheet
For Each ws In Worksheets
Debug.Print ws.Name
Next
End Sub

Got this error when running the code:

Input all 84 sheet names into a new tab called 'Sheet1' in cells A1 to A84.
When run debug:

KR
Jmorrison67

 

[Jmorrison67]

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

End With
ThisWorkbook.Activate
Sheets(arr).Select

Hi,

Error message:

Debug:

Hmm not sure

KR
Jmorrison67

 

[GWteB]

@Jmorrison67, are there also Charts involved?

 

[Akuini]

It could be typo in sheets name, I added a line to check if sheet's name exist, try:

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

Sheets(ary).Select

 

[GWteB]

Not sure what could be the cause that both our codes fails on Sheets( ).Select in your situation.
It should work, as it does for me ...

 

[Jmorrison67]

@Jmorrison67, are there also Charts involved?

Good morning GWteB,

Each of the 84 tabs has a mix of tables with data and graphs.

KR
Jmorrison67

 

[Jmorrison67]

It could be typo in sheets name, I added a line to check if sheet's name exist, try:

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

Sheets(ary).Select

Good morning,

I must be doing something wrong, as this also doesn't work. Get 'Type Mismatch' error now....

KR
Jmorrison67

 

[Jmorrison67]

Not sure what could be the cause that both our codes fails on Sheets( ).Select in your situation.
It should work, as it does for me ...



View attachment 48119

Good morning GWteb,

I must be doing something wrong - added this and all it done was add 84 tabs extra into my workbook and then got an error message saying....

Then looks like it tried to save it in the directory:

KR
Jmorrison67

 

[Akuini]

I must be doing something wrong, as this also doesn't work. Get 'Type Mismatch' error now....

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

 

[GWteB]

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