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]

Glad it works.

Could I just ask something around saving of the worksheets....the code (as I wanted it to be, saves as pdf) however I wondered if it's easy enough to change the 'save' bit so that it saves as another excel workbook with values only?

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

KR
Jmorrison67

 

[Akuini]

I wondered if it's easy enough to change the 'save' bit so that it saves as another excel workbook with values only?

Do you want to save all sheet in the active workbook or just the sheets in the array?

 

[Jmorrison67]

Do you want to save all sheet in the active workbook or just the sheets in the array?

Just a like for like as is currently doing with saving as pdf. So just those selected in the array.
The main reason is because some managers want / like excel worksheets rather than pdf's so they can add to their reporting packs aswell, so i'm going to give them the option to click a button for a pdf version and another button to run in excel (values only) - so will run two modules to handle it (as don't want them having to do anything to get the data out i.e. inputting something into a message box.), so looking for a seperate bit of code to save as excel values only - assume it isn't too dis-similar to the existing code?

KR
Jmorrison67

 

[Akuini]

So just those selected in the array.

Try this:

Sub saveWorkbook()
Dim tx As String, q As String
Dim n As Long, i As Long, ary, z
Dim FolderPath As String
Dim ws As Worksheet
FolderPath = "C:\Users\morrisonj\XXPack"

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).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws

With ActiveWorkbook
    .SaveAs Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy") & ".xlsx"
    .Close
End With

End Sub

 

[Jmorrison67]

Try this:

Sub saveWorkbook()
Dim tx As String, q As String
Dim n As Long, i As Long, ary, z
Dim FolderPath As String
Dim ws As Worksheet
FolderPath = "C:\Users\morrisonj\XXPack"

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).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws

With ActiveWorkbook
    .SaveAs Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy") & ".xlsx"
    .Close
End With

End Sub

I removed this 'End With' and it seems to work alright. Can you confirm?

One final thing I need it to do, is as per pdf print/save bit where it looks at the print area and only prints what is in that area like this bit >

IgnorePrintAreas:=False

Original Pdf code:

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=FolderPath & " - " & Format(Date, "dd-mm-yyyy"), _
OpenAfterPublish:=False, [COLOR=rgb(226, 80, 65)]IgnorePrintAreas:=False

[/COLOR]

I tried added it on the end of the new code but get this error:

With ActiveWorkbook
    .SaveAs Filename:=FolderPath & " - " & Format(Now, "dd-mm-yyyy hhmm") & ".xlsx", [COLOR=rgb(226, 80, 65)]IgnorePrintAreas:=False[/COLOR]
    .Close[CODE=vba]

[/CODE]

KR
Jmorrison67

 

[Akuini]

I removed this 'End With' and it seems to work alright. Can you confirm?

No, my code runs without error.
Can you show your last amended code?

I tried added it on the end of the new code but get this error:

Sorry, I don't know "IgnorePrintAreas:=False" parameter, my guess it's only for saving as pdf file not excel file.

 

[Jmorrison67]

No, my code runs without error.
Can you show your last amended code?


Sorry, I don't know "IgnorePrintAreas:=False" parameter, my guess it's only for saving as pdf file not excel file.

Sub SolutionXL()

Dim FolderPath As String
FolderPath = "C:\Users\morrisonj\XX Pack"

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).Copy
For Each ws In ActiveWorkbook.Worksheets
    With ws.UsedRange
        .Value = .Value
    End With
Next ws

With ActiveWorkbook
    .SaveAs Filename:=FolderPath & " - " & Format(Now, "dd-mm-yyyy hhmm") & ".xlsx"
    .Close
End With

MsgBox "Excel file has been successfully exported."

End Sub

The above works and saves to excel as new workbook, but if i put the 'End With' back in, i get the above error:

 

[Akuini]

The above works and saves to excel as new workbook, but if i put the 'End With' back in, i get the above error:

I thought you're talking about my last code in post #34. Your code is from earlier code in post #21, so yes you need to remove the End With.

 

[Jmorrison67]

I thought you're talking about my last code in post #34. Your code is from earlier code in post #21, so yes you need to remove the End With.

Apologies

 

[Jmorrison67]

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:

call sourcesheet.activate

and it returns to where the button is. When i insert this into my code i get:

Run Time error 424 - Object required.

Any idea's?

Kind regards
Jmorrison67