Hello Friends,
Hope you are doing well !! Thought of sharing a small VBA code to help you writing a code to print the Workbook, Worksheet, Cell Range, Chart etc. .PrintOut () Method is used to print any Excel Object.
Syntax of .PrintOut Method
YourObj.PrintOut(From, To, Copies, Preview, ActivePrinter, PrintToFile, Collate, PrToFileName, IgnorePrintAreas)
Where:
|
Examples to Print Excel:
Based on above explanation and Syntax we will see examples of printing the Workbook, sheets, charts etc.
Example 1: VBA Statements to Print Objects with Default Options
In this set of examples, I am using all default options to print. This means I am not providing any other parameter to the method .PrintOut
1. VBA code to print ActiveWorkbook
Function PrintActiveWorkbook()
ActiveWorkbook.PrintOut
End Function
2. VBA code to print Active Sheet
Function PrintActiveSheet()
ActiveSheet.PrintOut
End Function
3. VBA code to print all WorkSheets
Function PrintAllWorkSheets()
WorkSheets.PrintOut
End Function
4. VBA code to print a Single Sheet
Function PrintOneSheet()
Sheets("Sheet1").PrintOut 'Sheet1 is the name of the Sheet which you want to Print
End Function
5. VBA code to print more than one Sheet
Function PrintMultipleSheets()
Sheets(Array("Sheet1" , "Sheet2", "Sheet3").PrintOut
End Function
6. VBA code to print Selected area of a Sheet
Function PrintSelectedArea()
Selection.PrintOut
End Function
7. VBA code to print Range of Worksheet
Function PrintRange()
Range("A1:D5").PrintOut
End Function
8. VBA code to print Excel Chart
Function PrintChart()
Sheets("Sheet1").ChartObjects("Chart1").Chart.PrintOut 'Chart1 is name of the Chart
End Function
9. VBA code to print All Charts in a WorkSheet
Function PrintAllChart()
Dim ExcelCharts As Object
Set ExcelCharts = Sheets("Sheet1").ChartObjects
For Each Chart In ExcelCharts
Chart.Chart.PrintOut
Next
End Function
10. VBA code to print All Charts in a Workbook
Function PrintAllChart()
Dim ExcelCharts As Object
For Each Sheet In Sheets
Set ExcelCharts = Sheet.ChartObjects
For Each Chart In ExcelCharts
Chart.Chart.PrintOut
Next
Set ExcelCharts = Nothing
Next
End Function
Example 2: VBA Statements to Print Objects with different parameters passed
1. VBA code to print From Page Number X to Page Number Y
'Below statement will print from Page No:2 to Page No:3
Worksheets("Sheet1").PrintOut From:=2, To:=3
2. VBA code to print more than 1 Copy
'Below statement will print 3 copy of the Sheet1 from Page 2 to Page no: 3
Worksheets("Sheet1").PrintOut From:=2, To:=3, Copies:=3
3. VBA code to Show Print Preview before actual printing
'Below statement will print 3 copy of the Sheet1 from Page 2 to Page no: 3
Worksheets("Sheet1").PrintOut From:=2, To:=3, Copies:=3, Preview:=True
I have nearly 50 staffs in company with different names create a macros from 1 to 50 automatically (VBA CODE PLEASE)
Dear Vishwamitra
Your WEBSITE has been an eye opner to me. Pl help me to do the following. – print one B & W worlsheet and one Colour print.
When I tried printing with RECORD NEW MACRO this came up, & it works.
“PRINT(1,,,1,,,,,,,,2,””Canon iP2700 series on Ne05:””,,TRUE,,FALSE)” ‘second 1 is for no. of copies. Notice- printer is after 2 and a comma
ExecuteExcel4Macro “PRINT(1,,,2,,,,,,,,2,,,TRUE,,FALSE)” ‘First 2 is for No of Copies made for Color print.
So how to change this code to get one B & W print and thereafter get one Colour print?
I am a self study man and have never done an EXCEL course.
Excell is a wonderful program and I get MACROs to ease my most work.
I have a spreadsheet with 32 worksheets.
Sheet 1 and 2 will not need to be printed.
Sheets 3, 4, 5 will be printed with current print ranges.
I need Sheets 6 through 32 printed only if the formula in cell B5 is greater than 0.
Can you help me with setting up a VBA code for this?
Thank you!
I am looking to just print out only selected items from a menu that includes over 500 items, hence all items will not be selected.
The items on the menu run from columns A1 to F1 down to line 500.
Goal: I am looking to write and then run a print macro that prints out only those menu items selected for a given day which is tied to values (the total number of meals / menu items selected in Column H5 to H500). This way the print out will only include items that were selected and will compress the printout to exclude items not selected.
Can you guide me where to place the macro on my spreadsheet and in writing this macro?
Thanks.
David
I want to select few sheets, but I want to write in a cell the sheets that i want to select. Is it possible?
I´ve written the following:
Public Sub SelecHojas()
Dim HojaSelecionadas As String
Dim HojaActiva As String
Dim CeldaSelecionada As String
*Here the programma fail
HojaSelecionadas = Hoja1.Range(“B5”).value
HojaActiva = Hoja1.Range(“B7”).Value
CeldaSelecionada = Hoja1.Range(“B9”).Value
Sheets(HojaSelecionadas).Select
Sheets(HojaActiva).Activate
Range(CeldaSelecionada).Select
Could you help me?
this is very useful …
i was wondering if you can help me with something …
i have a standard sheet that i need to print it 100 times … but in that sheet , i have 2 cells .. 1st cell represents ” username ” the second cell represents password … so i have to change the username and password in each printed paper … how to do it ?
i have usernames and passwords in another sheet …
This is a possible method to solve your problem. Ill try to explain as I go to help out.
Sheet1 will be the print page. Name of the username in A1 and name of the password in B1
Sheet 2 will have the usernames and passwords
Usernames will be in A:A and passwords in B:B
Sub PrintDoc()
‘Declare your variants
Dim rng as Range
Dim cll as Range
‘rng is the range of the usernames (Given that the passwords are beside the usernames)
‘(eg sheet2 in cells A1:A100)
‘cll is the cell within the range that will contain the username.
‘One cell beside that is will contain the password.
Set rng = Sheets(“Sheet2”).Range(“A1:A100”)
‘Will set the value in sheet 1 to each username and password in turn then print the page. Will only print 1-100 because of the range set.
For each cll in rng
With Sheets(“Sheet1”)
.Range(“A2”).value = cll ‘ Username
.Range(“B2”).value = cll.Offset(0,1) ‘Password.
.PrintOut
End with
next cll
End Sub
If the password is not directly beside the username then change the offset by the number of cells away it is.
Eg. If username is in B:B and password is in E:E change the offset to (0,3) because there is 3 columns between B and E.
Before you run any macro’s make sure you save your document first. But I hope this solves your problem.
Let me know how you went or if you need more clarification let me know. 🙂
Jobo
How do you combine “print multiple sheets” and the “range”of each sheet to be printed in one macro?
with the the range being different in each sheet
can we write a code to let excel print duplex (double-sided)?
Tq
Hi Josean
I get my VBA print a different view from different excel version. Please be advice the tips for yielding a same printout from different excel version.
Thanks
how do pages print at Excel active worksheet?
for example
From 2 to 5 and from 7 to 9
Pages : 2-5,7-9
how to?
Thanks for the tip. I’m trying to create a macro that will only print specific sheets using logic. Example: IF a cell contains X or Y, then print Sheet 1. Can you share a sample of what that code would look like (assuming it’s possible).
Dear Kevin,
Yes, ofcourse it is possible to achieve this. all you need to do it put a condition in your print function: (Please read the comment i have provided in the comment.
[code language=”vb”]
Sub printConditionally()
‘Assumptions:
‘ Cell where X ot Y value are to be compared
‘ in Sheet1 (this is the name of the sheet) in
‘ cell (1,2) means B1 cell
‘ Change your sheet name and cell address according
‘ to your need
‘ Also, I have put condition with small letter or x or y
‘ you can ofcourse change it to capital, if you want
With Sheets("Sheet1").Cells(1, 2)
If .Value = "x" Or .Value = "y" Then
‘ your print statement goes here..
Sheets("Sheet1").PrintOut
End If
End With
End Sub
[/code]
Let me know if this was helpful. You can ask if you have any further question.
I wish to create a excel macro to print only section of sheet1 on each day text is entered.
On 25th November 2017, section of text is created in column A from Row1 to Row7. It is printed on 25th November 2017.
On 26th November, New section of text is added in Column A from Row9 to Row15.
On giving print command on 26th November, printer should print only text added on 26th November leaving top print margin upto Row8 blank.
Idea is to print only the text added on following day without printing text of above sections.
Is there anyway, we can create a print macros to print section/sections with print command button autosetting top print margin.
This is just like Bank passbook printing done by banking software printing records from start date to end date, autosetting top margin.
hello the explanation is very usefull. please tell me code to save pdf file in desired location.
I looking to create VBA Coding in Excel that will allow me to Clear Contents, Print Worksheet 2, and Log contents clear to Worksheet 3. Is this possible??
i want to
print the pay slips for each staff separately. the sheet is prepared by joining different sheet in the file using the volookup.
Thankyou. I have been struggling for hours to record a print workbook macro for a college assignment in Excel for Mac. I can record a macro to do almost anything other than print for some reason. Finally a workaround and my assignment is complete. Many many thanks.
Good day.
Wonder if you can please assist.
I have multiple sheets in a workbook. I need to only print selected sheets based on a value in a specific cell (this differ page to page).
My Cover page must always print, followed by the pages with a value in a specific cell.
I have tried all sort of code but can not get it right.
Can you possible help?
I have recorded mention micro.in this micro he is printing a page but i want to prevent it.i have tried all method but fail.
please help me anyone.
Sub TRP_Print()
‘
‘ TRP_Print Macro
‘
‘
Sheets(“Trucks Wise Report”).Select
Range(“G11:H11”).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Selection.Copy
Sheets(“TRP WORK”).Select
Range(“B3”).Select
ActiveSheet.Paste
Range(“D3”).Select
Range(Selection, Selection.End(xlDown)).Select
Application.CutCopyMode = False
Range(“D3:D117”).AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy
Sheets(“Travel Report Printing”).Select
Range(“H3”).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True, IgnorePrintAreas:=False
Selection.ClearContents
Sheets(“TRP WORK”).Select
ActiveSheet.ShowAllData
Range(“B3:C3”).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.ClearContents
Range(“B3”).Select
Sheets(“Trucks Wise Report”).Select
Range(“C10”).Select
Selection.End(xlDown).Select
End Sub
hello bosses,
i created a workbook for my students class assessment and i have a sheet where i can generate individual terminal report from the database sheet.i need help to create a print macro where i can automatically print select every student ID with its records and print the whole class report after terminal exams for each parent.