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
Good morning Akuini,

I am now using the code and working as expected. Just had a quick question I was hoping to run past you or the community.
Whilst running the code (after pressing my macro button i have inserted), once it's finished running it doesnt return to the source sheet (i know thats not in the code so it wouldnt know to do that). I've previously used this:
VBA Code:
call sourcesheet.activate
and it returns to where the button is. When i insert this into my code i get:

View attachment 49389

Run Time error 424 - Object required.

Any idea's?

Kind regards
Jmorrison67
I think i might have done it.....

Changed the code to:

VBA Code:
Call Sheets("Output").Activate

With tab named 'Output' being the source sheet and will always be.
 

Excel Facts

Get help while writing formula
Click the italics "fx" icon to the left of the formula bar to open the Functions Arguments dialog. Help is displayed for each argument.

Jmorrison67

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

I was wondering if you were about at all, as I have been using this code for the last couple of weeks and has been working, but i went in to run off the report today and i got this error:

1635511536597.png


I havn't just changed anything. When i run debug it highlight the

VBA Code:
Sheets(ary).Select

1635511611344.png


This is the whole code:

1635511657139.png



I'm just confused as to what could have happened, any common idea's you could suggest?

KR
Jmorrison67
 

Jmorrison67

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

I was wondering if you were about at all, as I have been using this code for the last couple of weeks and has been working, but i went in to run off the report today and i got this error:

View attachment 50111

I havn't just changed anything. When i run debug it highlight the

VBA Code:
Sheets(ary).Select

View attachment 50112

This is the whole code:

View attachment 50114


I'm just confused as to what could have happened, any common idea's you could suggest?

KR
Jmorrison67
Got it - one of the sheets was hidden in the workbook :)
 
Learn Excel from Bill Jelen

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

Forum statistics

Threads
1,151,700
Messages
5,766,007
Members
425,322
Latest member
galaxy6623top

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