PDA

View Full Version : [SOLVED] Macro to Print Worksheets Containing Specific Data



Kristy Jurey
11-29-2017, 10:26 AM
Good Morning -

I have been watching youtube videos and reading blogs for 2 days now. I am trying to figure out the code that will allow my users to select a print button that will only print to PDF the worksheets in the file that have the word print in any cell in columns T & U. Is this possible?

jolivanes
11-29-2017, 06:53 PM
Do you know how to print to PDF?

Sub Select_With_Print()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws
If WorksheetFunction.CountIf(.Range("T:U"), "print") <> 0 Then ws.Select False
End With
Next ws
End Sub

This should save the sheets as individual pdf files in the folder that houses your excel workbook.

Sub Or_Maybe()
Dim i As Long, ws As Worksheet
For i = 1 To ActiveWorkbook.Worksheets.Count
With Sheets(i)
If WorksheetFunction.CountIf(.Range("T:U"), "print") <> 0 Then Sheets(i).ExportAsFixedFormat Type:=xlTypePDF, _
Filename:=ThisWorkbook.Path & "\" & Sheets(i).Name, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas:=False
End With
Next i
End Sub

Kristy Jurey
11-30-2017, 07:17 AM
Thank you! I was able to get this to 1/2 work. I can get it to select the sheets with "print" in T or U however, nothing actually prints. Also, can I set it up to print to 1 PDF file rather then 1 file for each worksheet?

jolivanes
11-30-2017, 08:18 AM
The top code does not print, hence the question if you know how to print to PDF.
I'll look into it a little later.

jolivanes
11-30-2017, 11:06 AM
Kristy
Does this do what you have in mind?

Sub AAAAA()
Dim cntr As Long, shArr(), ws As Worksheet
cntr = 0


For Each ws In ActiveWorkbook.Worksheets
If WorksheetFunction.CountIf(ws.Range("C:D"), "print") <> 0 Then
ReDim Preserve shArr(cntr)
shArr(cntr) = ws.Name
cntr = cntr + 1
End If
Next ws

Sheets(shArr).Select


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & "All_Together" & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=True


End Sub

Kristy Jurey
12-04-2017, 02:21 PM
IT WORKED!!!!!!!! Thank you so much!:clap:





Kristy
Does this do what you have in mind?

Sub AAAAA()
Dim cntr As Long, shArr(), ws As Worksheet
cntr = 0


For Each ws In ActiveWorkbook.Worksheets
If WorksheetFunction.CountIf(ws.Range("C:D"), "print") <> 0 Then
ReDim Preserve shArr(cntr)
shArr(cntr) = ws.Name
cntr = cntr + 1
End If
Next ws

Sheets(shArr).Select


ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, Filename:=ThisWorkbook.Path & "\" & "All_Together" & ".pdf", _
Quality:=xlQualityStandard, IncludeDocProperties:=False, IgnorePrintAreas:=True


End Sub

jolivanes
12-04-2017, 02:47 PM
Thank you for letting us know.
I like your enthusiasm.
Good luck