Macro to allow user to Save as PDF and Print out | VBA & Macros | Excel Forum

Advanced Search

Forum Scope
Current forum
All forums

Include blog posts

Match
Match any word
Match all words
Match phrase

Forum Options
Posts only
Topic titles only
Posts and topic titles

Minimum search word length is 3 characters - maximum search word length is 84 characters

Password

Remember me

Lost password? Home Excel Forum VBA & Macros Macro to allow user to Save as PDF …

Macro to allow user to Save as PDF and Print out

Training Wheels...

New Member

Members

Forum Posts: 2

Member Since:
November 14, 2019

Offline

1
November 14, 2019 - 8:07 am

Hi all,

Not only am I new to the macro world, my macros are still at the recording stage...so bear with me please...

After trying for 2 days to complete my macros with the recorder, with no success, I am going crazy. I need your help to do the following please..!.

My workbook has two worksheets that I need to user to be able to Save as a PDF with a fixed name and Print out (assuming they have a printer connected...).

The first worksheet is called "BANK ACCOUNT" and is text, in a fixed range that needs to be Saved and Printed.

The second worksheet is called "Schedules" and has 3 tables that need to be filtered (remove 0), Saved, Printed, and then refiltered back to their original state (Select all).

Reading thru the threads here, I've tried using some parts from past solutions but I think the combination of "clean code" with my recorded code is just making it worse....

Any help greatly greatly appreciated.

Thank you.

I've posted my latest attempts on the 4 separate macros below...

Sub Xbankprint()
' Xbankprint Macro

Sheets("BANK ACCOUNT").Visible = True
Range("B3:F10").Select
Sheets("Dashboard").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
ActiveWindow.SelectedSheets.Visible = False
Range("B3:F43").Select
Selection.PrintOut Copies:=1, Collate:=True
Range("B3:F10").Select
Sheets("Dashboard").Visible = True
Sheets("Summary").Visible = True
Range("K6:O8").Select
Sheets("BANK ACCOUNT").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
Range("A1:W45").Select
ActiveWindow.Zoom = True
Range("K6:O8").Select
End Sub

Sub Xbanksave()
' Xbanksave Macro

Sheets("BANK ACCOUNT").Visible = True
Range("B3:F10").Select
Sheets("Dashboard").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
ActiveWindow.SelectedSheets.Visible = False
Range("B3:F43").Select
ActiveSheet.ExportAsFixedFormat Type:=xlTypePDF, _
Range("B3:F10").Select
Sheets("Dashboard").Visible = True
Sheets("Summary").Visible = True
Range("K6:O8").Select
Sheets("BANK ACCOUNT").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
Range("A1:W45").Select
ActiveWindow.Zoom = True
Range("K6:O8").Select

End Sub

Sub Xscheduleprint()
' Xscheduleprint Macro

Sheets("Schedules").Visible = True
Sheets("Dashboard").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Schedules").Select
Range("BILLS[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("BILLS").Range.AutoFilter Field:=3, Criteria1:= _
Array("$115", "$120", "$140", "$240", "$30", "$40", "$400", "$50", "$58", "$59", "$60", _
"$70"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=40
Range("DEBIT[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("DEBIT").Range.AutoFilter Field:=3, Criteria1:= _
Array("$150", "$180", "$2", "$30"), Operator:=xlFilterValues
Range("CREDIT[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("CREDIT").Range.AutoFilter Field:=3, Criteria1:= _
Array("$100", "$200", "$400", "$800"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-40
ActiveWindow.SmallScroll ToRight:=-2
Range("BT85:CB230").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$B$2:$F$57"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.236220472440945)
.RightMargin = Application.InchesToPoints(0.236220472440945)
.TopMargin = Application.InchesToPoints(0.748031496062992)
.BottomMargin = Application.InchesToPoints(0.748031496062992)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
Selection.PrintOut Copies:=1, Collate:=True
Range("CREDIT[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("CREDIT").Range.AutoFilter Field:=3
ActiveWindow.SmallScroll Down:=-24
Range("DEBIT[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("DEBIT").Range.AutoFilter Field:=3
ActiveWindow.SmallScroll Down:=-48
Range("BILLS[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("BILLS").Range.AutoFilter Field:=3
ActiveWindow.SmallScroll Down:=-16
ActiveWindow.SmallScroll ToRight:=-2
Range("BT85:BW85").Select
Sheets("Dashboard").Visible = True
Sheets("Summary").Visible = True
Sheets("Schedules").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
Range("A1:V45").Select
ActiveWindow.Zoom = True
Range("K6:O8").Select
End Sub

Sub Xschedulesave()
' Xschedulesave Macro

Sheets("Schedules").Visible = True
Sheets("Dashboard").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
ActiveWindow.SelectedSheets.Visible = False
Range("BILLS[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("BILLS").Range.AutoFilter Field:=3, Criteria1:= _
Array("$115", "$120", "$140", "$240", "$30", "$40", "$400", "$50", "$58", "$59", "$60", _
"$70"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=48
Range("DEBIT[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("DEBIT").Range.AutoFilter Field:=3, Criteria1:= _
Array("$150", "$180", "$2", "$30"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=8
Range("CREDIT[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("CREDIT").Range.AutoFilter Field:=3, Criteria1:= _
Array("$100", "$200", "$400", "$800"), Operator:=xlFilterValues
ActiveWindow.SmallScroll Down:=-48
Range("BT85:CB230").Select
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = "$B$2:$F$57"
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.236220472440945)
.RightMargin = Application.InchesToPoints(0.236220472440945)
.TopMargin = Application.InchesToPoints(0.748031496062992)
.BottomMargin = Application.InchesToPoints(0.748031496062992)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlLandscape
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = 2
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With

ThisWorkbook.ExportAsFixedFormat Type:=xlTypePDF, Filename:=PDFFile, Quality:=xlQualityStandard, IncludeDocProperties:=True, IgnorePrintAreas _
:=False, OpenAfterPublish:=OpenPDFAfterCreating
Range("CREDIT[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("CREDIT").Range.AutoFilter Field:=3
ActiveWindow.SmallScroll Down:=-24
Range("DEBIT[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("DEBIT").Range.AutoFilter Field:=3
ActiveWindow.SmallScroll Down:=-48
Range("BILLS[[#Headers],[Amount]]").Select
ActiveSheet.ListObjects("BILLS").Range.AutoFilter Field:=3
ActiveWindow.SmallScroll Down:=-32
ActiveWindow.SmallScroll ToRight:=-3
Range("BT85:BW85").Select
ActiveWindow.SmallScroll Down:=27
Sheets("Dashboard").Visible = True
Sheets("Summary").Visible = True
Sheets("Schedules").Select
ActiveWindow.SelectedSheets.Visible = False
Sheets("Summary").Select
Range("A1:V45").Select
ActiveWindow.Zoom = True
Range("K6:O8").Select
End Sub
See Answer

Catalin Bombea

Iasi, Romania

Admin

Forum Posts: 1842

Member Since:
November 8, 2013

Offline

2
November 16, 2019 - 1:59 pm

Try this code:

Sub Macro1()
Dim Tbl As ListObject

Set Tbl = ThisWorkbook.Worksheets("Schedules").ListObjects("BILLS")
Tbl.Range.AutoFilter Field:=Tbl.ListColumns("Amount").Index, Criteria1:="<>0", Operator:=xlAnd
Tbl.Range.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Catalin\Desktop\Bills.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
Tbl.Range.AutoFilter Field:=Tbl.ListColumns("Amount").Index

Set Tbl = ThisWorkbook.Worksheets("Schedules").ListObjects("CREDIT")
Tbl.Range.AutoFilter Field:=Tbl.ListColumns("Amount").Index, Criteria1:="<>0", Operator:=xlAnd
Tbl.Range.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Catalin\Desktop\CREDIT.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
Tbl.Range.AutoFilter Field:=Tbl.ListColumns("Amount").Index

Set Tbl = ThisWorkbook.Worksheets("Schedules").ListObjects("DEBIT")
Tbl.Range.AutoFilter Field:=Tbl.ListColumns("Amount").Index, Criteria1:="<>0", Operator:=xlAnd
Tbl.Range.ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Catalin\Desktop\DEBIT.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
Tbl.Range.AutoFilter Field:=Tbl.ListColumns("Amount").Index

ThisWorkbook.Worksheets("Bank Account").ExportAsFixedFormat Type:=xlTypePDF, Filename:= _
"C:\Users\Catalin\Desktop\Book1.pdf", Quality:=xlQualityStandard, _
IncludeDocProperties:=True, IgnorePrintAreas:=False, OpenAfterPublish:= _
Set Tbl = Nothing
End Sub

Answers Post

Training Wheels...

New Member

Members

Forum Posts: 2

Member Since:
November 14, 2019

Offline

3
November 16, 2019 - 8:30 pm

Thx mate!,

I know it was probably a simple thing for you but it means alot to me.

I'll study it and learn from it also.

Again, Thank you!

Forum Timezone: Australia/Brisbane

Most Users Ever Online: 245

Currently Online: Mark Milan , RACHEL LAWRENCE , Scott Kelley
Guest(s) 12

Currently Browsing this Page:
1 Guest(s)

Top Posters:

SunnyKow: 1432

Anders Sehlstedt: 873

Purfleet: 414

Frans Visser: 346

David_Ng: 306

lea cohen: 237

Jessica Stewart: 219

A.Maurizio: 213

Aye Mu: 201

jaryszek: 183

Newest Members:

Kirk Leavy

Arnab Ghosal

Muralidaran Ramamurthy

Alison Sissins

Ade Saputra

Boyan Boyadzhiev

Rajashanea Everett

Steve Frauenheim

Massimiliano Rabbi

Ivan Lopez

Forum Stats:

Groups: 3

Forums: 24

Topics: 6488

Posts: 28377

Member Stats:

Guest Posters: 49

Members: 32714

Moderators: 3

Admins: 4

Administrators: Mynda Treacy, Philip Treacy, Catalin Bombea, FT

Moderators: MOTH Support, Velouria, Riny van Eekelen

© Simple:Press —

Macro to allow user to Save as PDF and Print out |VBA & Macros|Excel Forum|My Online Training Hub