• 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.

VBA to print out an Excel table

glennpc

Member
I have an Excel table (not a pivot table) and I have VBA code that sorts the table, filters rows, etc. I want the macro to then print the table after the sorting/filtering are done.


I can get it to show the printpreview screen, but there I need to MANUALLY select the "Print the Selected Table" option (it defaults to "Print the Worksheet"). How can I do this inside my VBA code so that I can create a button that runs the macro and the macro does everything except execute the print (i.e., it does the sorting, the filtering, selects the table, brings up the print preview, and selects the "Print Selected Table" option) so my user only has to then click the Print button or cancel out? Right now, I can get it to do everything except telling the print job that I want to print the selected table.
 
Hi, glennpc!

How do you get brought up the print preview? Would you please post that part of the code? Thank you.

Regards!
 
Here's my VBA code (I call up the PrintPreview at the end):

[pre]
Code:
Sub PrintMasterAll()
'
' PrintMasterAll Macro
' Selects entire Master table and prints it, sorted on Item Number
'
Sheets("Lessons Learned").Select
Range("Table2").Select

' Turn off Filtering if its On

Call StopFiltering

' Sort on the Item Number Lowest to Highest

Range("A6").Select
ActiveWorkbook.Worksheets("Lessons Learned").ListObjects("Table2").Sort. _
SortFields.Clear
ActiveWorkbook.Worksheets("Lessons Learned").ListObjects("Table2").Sort. _
SortFields.Add Key:=Range("A6"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal
With ActiveWorkbook.Worksheets("Lessons Learned").ListObjects("Table2").Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

' Fix header to indicate report title correctly

Worksheets("Lessons Learned").PageSetup.CenterHeader = "&B Master List:  Sorted by Item # &B"

'Print preview

Range("Table2[#All]").Select

Range("Table2[#All]").PrintPreview

End Sub
[/pre]
 
Hi, glennpc!


It's a normal print preview, I was thinking in something different which I didn't know what it might be. A solution is to use the method PrintOut, instead of the PrintPreview.


Does this code helps?

-----

[pre]
Code:
Sub x()
Range("Table2[#All]").PrintPreview
If MsgBox("Sure you want it in paper?", _
vbApplicationModal + vbQuestion + vbDefaultButton2 + vbOKCancel, _
"Confirmation") = vbOK Then
Range("Table2[#All]").PrintOut
End If
End Sub
[/pre]
-----


Just advise if any issue.


Regards1
 
Sorry, I haven't had chance to get back to this (I'm studying for the PMP). I will try your method and get back to you. Thanks very much for the help.
 
Back
Top