• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Print a sheet selection

Portucale

Portucale

Member
Hi,

I need to get a procedure whereby the user choose the worksheets to print, these can differ from job to job, I can perform this if the selection would be the same all the time but struggling to do when the selection is volatile.

Any ideas is very much appreciated,

Thanks in advance,
 
Thanks Hui,

But I am still in the muddle, I just need to select the sheets that the Status is "ON" and then print them as a PDF, this is the current code that I have when user prints the completed report.

Code: 
Private Sub PrintPDF()

Dim strReportName As String
Dim strDataPath As String
Dim wb As Workbook

strDataPath = "\\qiqstasan01a\File_Store\Sky Ops Reporting Team\Reporting Team SKY\Automated WorkStack\3 PDF\" '~~~ Adjust location to suit your work
strReportName = "SkyIQ_Priorities_for_Customer_Group.pdf"

Set wb = ActiveWorkbook

Call Activatesheets

    Sheets("Main").Activate '~~~ Activate sheet Main to update the Week Commencing Date
    With ActiveSheet
        .Shapes.Range(Array("Week")).Select
        Selection.ShapeRange(1).TextFrame2.TextRange.Characters.Text = "Week commencing:  " & Range("V1").Value
        .Range("A1").Select
     End With
   
   Sheets(Array("Main", "Index", "RR-Triaged", "RR-NotTriaged", "Planned Work", "WorkInProgress", _
        "InSignOff", "Closed", "Publishing", "Hold", "Overview", "FollowUp", "Prioritisation Matrix", "LastPage")).Select
   ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strDataPath & strReportName, _
   Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
   
   Sheets("Main").Select
End Sub

I have created "checked box (Form Control)" where teh user can select the sheet(s) that they need to print, have the reference "On/Off" within range J17:J30. Alll seem Ok but stock when printout to PDF not working...

Appreciate the help

Thanks in advance,
 
Hi Portucale ,

My hunch is that you are selecting a lot of sheets , but the print command is executing on the Activesheet.

You need to have a loop so that after selecting all the sheets that you wish to print , you activate them one by one , and then execute the print command on the Activesheet.

Code: 
  Sheets(Array("Main", "Index", "RR-Triaged", "RR-NotTriaged", "Planned Work", "WorkInProgress", _
        "InSignOff", "Closed", "Publishing", "Hold", "Overview", "FollowUp", "Prioritisation Matrix", "LastPage")).Select
For each sht in Selection
    sht.Activate
  ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strDataPath & strReportName, _
  Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next
Replace the section where you have the selection and the print command by the above 5 lines.

Narayan
 
Hi Narayan,

Selecting the sheets on an Array, it works, the issue is when the users do their own selection, for some reason I cannot set the Print to a PDF, as I believe I cannot use "ExportAsFixedFormat" when using
"With ActiveWorkbook.Worksheets(Arr)".


In essence I am trying to give the users the facility to select Sheet1, Sheet2, etc of 15 Sheets and "print" the selection as a PDF with a specific name in a specific folder.

Pre selecting the sheets as the code above, no worries it works like a dream...

Thanks,
 
If you want the user to select the sheets by multiple selection of sheet tabs, then you could use something similar to:

Code: 
For Each s In ActiveWorkbook.Windows(1).SelectedSheets
s.ExportAsFixedFormat Type:=xlTypePDF, FileName:=strDataPath & strReportName, _
  Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:=True
Next s

Hope that helps.

Regards,
Ken
 
You must log in or register to reply here.
Back
Top