Deselect sheet from a group of selected sheets

[L33]

Hi,
I have a macro that selects a number of worksheets (actually chart sheets mostly) and then generates a PDF of those selected sheets (using the standard "Save As PDF or XPS" functionality in Excel 2007).

I had the macro as a Private Sub against a sheet (called "Slide Selection") instead of a standard module, activated from a button press:

Private Sub CommandButton2_Click()

Dim blnReplace As Boolean
Dim lngRow As Long
Dim lastRow As Single
Dim PDFSaveLocation As String
Dim PDFSaveNameCorp As String
Dim PDFSaveNamePeriod As String

PDFSaveLocation = ActiveWorkbook.Path
PDFSaveNameCorp = Range("I1")
PDFSaveNamePeriod = Range("I2")

    blnReplace = True
    
   lastRow = ActiveSheet.Cells(Rows.Count, "a").End(xlUp).Row
    For lngRow = 2 To lastRow
        If Cells(lngRow, 2) = "Y" Then
            Sheets(Cells(lngRow, 1).Value).Select blnReplace
            blnReplace = False
        End If
    Next

ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFSaveLocation & "\" & PDFSaveNameCorp & " - " & PDFSaveNamePeriod & " - Custom PDF output"

End Sub

(My sincere apologies are due here - a lot of this is not my own code and is taken from a forum somewhere (possibly this one) but I didn't make a note of exactly where, and, more to the point, who, I took it from. I now understand that this is very poor form and it won't happen again!)

The user is presented with a list of all the slides available to them with tick boxes that get translated to simple "Y" or "N", which this code then reads and selects each one in turn before finally saving as PDF.

However, I now have a need to call on this code from within another larger macro. The larger macro basically makes copies of the master Excel report file but cuts out various elements of the core data to make sub-reports. But I want to incorporate the creation of this custom PDF for each of the sub-reports - so the user selects the slides he wants, then hits the button to create the sub-reports, including the PDF element.

Firstly, I can't call the "Private Sub CommandButton2_Click", and I didn't expect to. Changing it to a normal "Sub" seems to have no adverse affect on the normal function of the command button, but I still have trouble calling it.

I created a new version of the macro that can reside in a normal module making certain edits:

For lngRow = 2 To lastRow
        If Cells(lngRow, 2) = "Y" Then
            Sheets(Cells(lngRow, 1).Value).Select blnReplace
            blnReplace = False
        End If
        Sheets("Slide Selection").Select (False)
        Sheets("Slide Selection").Activate
    Next

So all I've done is make it return to the "Slide Selection" worksheet on each loop to get the next "Y" or "N" value as it goes down the list.

My problem now is probably more basic than the length of this post will suggest: how do I deselect the worksheet "Slide Selection" before invoking the code to save as PDF??

Unless there is some valuable lesson I can be taught here, and I'm going about it all in completely the wrong way?

Many thanks in advance for any assistance!

 

[Jerry Sullivan]

Hi,

Perhaps a better approach would be to build the list of sheets that you want to print
and then select them in one statement using an Array.
This is more efficient and you won't need to deselect sheet "Slide Selection"

Try...

Sub Build_Sheet_Array()
    Dim lngRowCurr As Long, lngRowLast As Long
    Dim strArrayList As String, PDFSaveLocation As String
    Dim PDFSaveNameCorp As String, PDFSaveNamePeriod As String
    Application.ScreenUpdating = False
 
    With Sheets("Slide Selection")
        lngRowLast = .Cells(Rows.Count, "A").End(xlUp).row
 
        For lngRowCurr = 2 To lngRowLast
            If .Cells(lngRowCurr, 2) = "Y" Then
                strArrayList = strArrayList & _
                    .Cells(lngRowCurr, 1) & ","
            End If
        Next
        If Len(strArrayList) > 1 Then
            strArrayList = Left(strArrayList, Len(strArrayList) - 1)
        Else: Exit Sub
        End If
 
        PDFSaveLocation = ActiveWorkbook.path
        PDFSaveNameCorp = .Range("I1")
        PDFSaveNamePeriod = .Range("I2")
    End With
 
    Sheets(Split(strArrayList, ",")).Select
 
    ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
        FileName:=PDFSaveLocation & "\" & PDFSaveNameCorp _
        & " - " & PDFSaveNamePeriod & " - Custom PDF output"
End Sub

 

[L33]

Many thanks Jerry for getting through my overly lengthy post and giving me this fantastic answer!

It works perfectly. I had been advised some time back on a previous post to look in to the "Split" function for arrays, but try as I might, I never could get the hang of it. I'll revisit that now that you've demonstrated it in action.

many thanks
Lee

 

[Dash12]

Hi,
I am trying to do something similar. I have about 10 checkboxes on the sheet. When a checkbox is checked, it reads the text from another cell, and opens a pdf with the same name. My goal is to combine all the pdfs as one or sort of have a dynamic way of filling the array you mentioned. So, for example: if checkbox 1 is checked, there is only one pdf. but if checkbox 2 is checked , then the end result is a pdf of pdf 1 and pdf 2 combined. Any input will be appreciated!

Many thanks Jerry for getting through my overly lengthy post and giving me this fantastic answer!

It works perfectly. I had been advised some time back on a previous post to look in to the "Split" function for arrays, but try as I might, I never could get the hang of it. I'll revisit that now that you've demonstrated it in action.

many thanks
Lee

 

[Jerry Sullivan]

Hi,
I am trying to do something similar. I have about 10 checkboxes on the sheet. When a checkbox is checked, it reads the text from another cell, and opens a pdf with the same name. My goal is to combine all the pdfs as one or sort of have a dynamic way of filling the array you mentioned. So, for example: if checkbox 1 is checked, there is only one pdf. but if checkbox 2 is checked , then the end result is a pdf of pdf 1 and pdf 2 combined. Any input will be appreciated!

Hi Dash12,
Combining existing PDF's into a single file is very different problem than exporting multiple worksheets in the same workbook to a PDF format. I'm not aware of any way to do that with Excel alone.

I was going to suggest you start another thread, then I noticed you did start a thread several days ago that didn't get any replies.
http://www.mrexcel.com/forum/excel-...ng-visual-basic-applications.html#post4224637

I'll add a reply to that thread with a suggestion about how you might pursue this with a 3rd party utility.