Close Window   
Free Ebook
Get Your Free Excel
ebook!
Our Top 15 Excel Tutorials
Instant Access!
E-mail:
Subscribe for Free Excel tips & more!
E-mail:
Advertisements



Vba Code For Printing Variable Print Ranges

Forum Register
Search Excel Forum Posts, Tutorials, Macros, Tips, and More

Hello,
I am trying to write VBA code that will print a print range that is presented in cell F3 on a "Reports" worksheet. The content of F3 will change depending on how many reports the user selects to print. For example, he could select one, two, three reports etc - up to twelve. The cell ranges of each report are named (e.g. Report1, Report2 etc) so that if the user selects to print Reports 1 and 2, the contents of cell F3 are "Report1,Report2".

If I replace WhatToPrint with "Report1,Report2" the print macro works. Can anyone help me to understand why it doesn't work when I leave WhatToPrint in?

Really appreciate your help with this!

Jon

VB:

Sub Macro2() 
     '
     ' Macro2 Macro
     ' Macro recorded 05/05/2009 by JW8836
     '
     
     '
     
    Dim WhatToPrint As String 
     
     
     
    WhatToPrint = Sheets("Reports").Cells(3, 6).Value 
     'sets the variable to equal the contents of cell D3 which contains the formula
     'summarising the print ranges I want to print
     
     
     
    Sheets("Reports").Cells(3, 6).Select 
    ActiveCell.FormulaR1C1 = WhatToPrint 
     ' pastes the variable in cell F3 - just to check that it looks like I want it to
     
    Sheets("Reports").PageSetup.PrintArea = WhatToPrint 
     'uses the variable to set print area - this is where it fails!
     'if you replace the variable with the contents of cell F3 the macro will work
     
    ActiveWindow.SelectedSheets.PrintPreview 
     
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



View Answers     

Similar Excel Tutorials

Print All Worksheets at Once in Excel
How to print all worksheets at once from Excel. This saves you the time of having to go to each sheet individually ...
Print Only Specific Parts of a Worksheet in Excel
In Excel you can select parts of a worksheet to print while ignoring all of the other data on the worksheet. This a ...
Print Specific Pages in Excel
This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print ...
Scale Data for Printing in Excel - Fit more onto a Printed Page
This tip shows you how to fit more Excel data onto a page for printing. This does not change the size, shape, or lo ...

Helpful Excel Macros

Print Preview Display for Specific Worksheets in Excel
- This free Excel macro allows you to display the print preview mode or window in Excel for a specified Excel worksheet.
Complete Guide to Printing in Excel Macros - PrintOut Method in Excel
- This free Excel macro illustrates all of the possible parameters and arguments that you can include in the PrintOut Meth
Print Specific Pages in Excel
- This free Excel macro allows you to print a pre-specified selection of pages from Excel. This means you can print 2 pag
Display the Print Window in Excel
- This free Excel macro displays the print window or dialog box in Excel. This is the same window that would appear when
Print Preview Screen Display for The Current or Selected Worksheets in Excel
- This free Excel macro allows you to display the print preview screen or window for the current or selected worksheets in

Similar Topics







Hi there,

I have a macro that does the following bit of code a number of times:


'Print Vic Consolidated
Sheets("New Template").Select
Range("h4").Select
ActiveCell.FormulaR1C1 = "29"
Sheets("new KPI").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=8, Collate:=True

Note: The ActiveCell.Formula is a variable, and changes from 1 to 29 according to the report that is run. The macro just changes the number to print out groups of reports (eg. 4, 5 , 6, 7 and 8 )


The thing I want to know is:

Can I make the number of Copies a variable as well???

As in, is it possible to do some sort of drop down menu when someone pushes the inital Print Macro button...how do I make it ask how many copies to print?

.......how do I build this in???


Thanks

Greg


I am using Windows XP and Excel 2007.
I have a workbook with 12 sheets named Rep1 to Rep12.
Each sheet contains a report. I have a userform ask the user which combination of reports they would like to save as pdf.
On the userform, the user can select any one report or any two reports or any three reports etc up to all 12 reports.
Once all sheets are selected I have a macro save to pdf.
The macro works ok provided I have a separate line of code for each possible combination.
e.g. To select reports(sheets) 1,2,3,4,5,6,11,12 I need the following code (R1, R2 etc represent the reports selected on the userform):
Private Sub OKButton_Click()
If R1 And R2 And R3 And R4 And R5 And R6 And R7 = False And R8 = False And R9 = False And R10 = False And R11 And R12 Then
Sheets(Array("REPORT1", "REPORT2", "REPORT3", "REPORT4", "REPORT5", "REPORT6", "REPORT11", "REPORT12")).Select
End sub
This is not efficient code because I will need hundreds of lines of code to cater for different combinations of reports.
What I really need is code to select a variable number of sheets.
Many thanks


Hi,

Could you please help me with a macro that will set the Print Area of a report based on the current lenght and width.

I have found macros that will set the Print area for reports of variable lenght but nothing for variable width.

I would love to be able to run the macro on the Active Sheet as I have several reports across a number of sheets.

Since the report is customised for the USER columns A:C are hidden as well as a large number of rows.

Your help is appreciated.

d


Hi--

I have 12 print areas set up on Sheet2. I don't always have info on all 12. I want something that will print out only the pages that info on them. Here is what I have right now, but it will only print out the last page. I'm guessing it starts at the bottom and goes up.
VB:

Sub Print1() 
    If Range("DJ78").Value > 0 Then 
        ActiveSheet.PageSetup.PrintArea = "$A$1:$DI$78" 
    End If 
    If Range("DJ167").Value > 0 Then 
        ActiveSheet.PageSetup.PrintArea = "$A$90:$DI$167" 
    End If 
    If Range("DJ256").Value > 0 Then 
        ActiveSheet.PageSetup.PrintArea = "$A$179:$DI$256" 
    End If 
    If Range("DJ345").Value > 0 Then 
        ActiveSheet.PageSetup.PrintArea = "$A$268:$DI$345" 
    End If 
    ActiveWindow.SelectedSheets.PrintPreview 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



Can anyone help me fine tune this? So far the VBA is only set up for 4 pages, even though I will end up needing 12.

I'm looping through a number of reports, and the print area is going to vary, depending on how many columns of data I have present. The starting cell will always be A1, but sometimes the print area will end at D1, E1, F1, etc.

Is there a way to use VBA to set the print area for each report, by refering to a worksheet containing the correct # of columns of data present.

In worksheet "Count," cell A1 = 3. This would mean the print area of my report would be A1 to (A1+3).


I am using a 53x27 worksheet with a print macro designed by someone in my
office. The first column contains information I would like to print on each
of the 3 pages that prints out. This is how it looks now:

ActiveSheet.PageSetup.PrintArea = "$a$6:$n$42"
ActiveWindow.SelectedSheets.PrintPreview
ActiveSheet.PageSetup.PrintArea = "$o$6:$aa$42"
ActiveWindow.SelectedSheets.PrintPreview
ActiveSheet.PageSetup.PrintArea = "$a$46:$m$53"
ActiveWindow.SelectedSheets.PrintPreview

I want the a6:a42 range to print on each page along with the above print
areas.




This is a problem that I have worked around, but I don't understand why it happened (and this bothers me):

A bit of background info - I have a multi-sheet spreadsheet (Excel 2002) to display the locations of samples in a storage container. There are twelve drawer maps (grids) per worksheet, and the user needs to print off the relevant grid to take to the container. So, I recorded the following simple macro for each grid (cell range) on a sheet and assigned each to a button on a toolbar I created. That all works absolutely fine.

Code:

Sub Print_Drawer_1()
    Range("A2:O29").Select
    ActiveSheet.PageSetup.PrintArea = "$A$2:$O$29"
    Range("A3").Select
    ActiveWindow.SelectedSheets.PrintPreview
    ActiveSheet.PageSetup.PrintArea = ""
End Sub



I recorded the macros on my pc, which has the printer preferences of the shared xerox printer set as 'Secure Print' (requiring me to enter a number at the printer to release any prints). The problem occurred when I moved the spreadsheet to a shared drive and others who have never set up the secure print option couldn't print - they got a pop up box saying a secure print passcode is required. This only happened however on the first sheet, printing off any other sheet worked fine. After staring blankly for a while, I tried going into the printing preferences when the macro displayed the print preview window, and saw that the setting was 'secure print' even when this wasn't set up on that pc as I said above.

I got around it by recording each macro again, this time choosing 'print' from the print preview window, choosing 'properties', and specifically selecting 'normal print' (rather than 'secure print') in the printer preferences, then cancelling the print.

This seems to work, but the macro looks identical to the one above - does anyone know where/how this information is stored?! And why it only affected the first worksheet originally (the one I used to record the macros)?

I hope this makes sense, and if anyone can shed any light, I'd be grateful!


I have the following:

Worksheet1: where the button and links are from
Worksheet2: where the button kicks off the macro and also links to certain parts of

Problem:

I'm trying to get the macro to only print a range but it prints the entire page. Even if I edit the macro myself I fail to change this. Here is the macro

Sheet("Worksheet2").Select
Range("A2:F15").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
Sheets("Fortnightly Checklist").Select
End Sub

Range ("A2:F15") is the range I want to print. I actually named that range earlier when I created the link to it, but the macro doesn't like it (name of the range is gravel).

Could someone give me an updated macro that would at least allow me to print only selected ranges?

Thanks
Shortnewb


I am using the following code written by a former employee to set a print area for 3 different ranges in my workbook. For some reason the pages are coming out in a landscape format. How do I get the macro to print in portrait?

Code:

Sub Print_Video_A()
'
' Print_VID_Nonl Macro
' Macro recorded 4/23/2002 by Gary Kapsner
'
' Updated 2/17/04 GAK  (Renamed from Vid_Nonl to Video, modified print areas)
'
' Revised Rev A 9/23/05 GAK  (Modified print areas)

Application.ScreenUpdating = False

    Application.GoTo Reference:="PA_Vid_C"
    ActiveSheet.PageSetup.PrintArea = "$A$5:$M$73"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.GoTo Reference:="PA_Vid_U"
    ActiveSheet.PageSetup.PrintArea = "$O$5:$X$73"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Application.GoTo Reference:="PA_Vid_S"
    ActiveSheet.PageSetup.PrintArea = "$Z$5:$AJ$73"
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Range("A1").Select
    Sheets("Svc_Rpt").Select
    Range("A7").Select
End Sub







I've got a workbook in which one of the tabs pulls a "report" for employee attendance. In order to print this report, I have to select the employee from a dropdown box and then choose to print. I have to continue this process until I have chosen each employee and printed each report seperately. This is quite time consuming when there are 50+ employees. Does anyone know of a macro that would print all of the reports at once? I've included an example.

Thanks!

Hi all,

Been scratching my haed with this one. I have set up a database with a number of reports, i have given access to the database to another team. When they print a report it comes out on my print and not there's. They don't have my printer set up as there default and if they go to 'file' 'print' and select there printer it still comes out on mine!!!! Please help

Thanks




I have a print macro set up so that when the user pushes the "Print" button on the "Input" tab, it prints a selected range ("A1:I272") off of the "Output" tab. It prints 6 sheets as that is the range that is selected, but the way I have it set up (in regards to print area) is that each page contains information that may or may not be relevant and need to be printed.

Is there a way to set up the macro so that when the user presses the "Print" button, a check box comes up and ask the user what all they need printed (6 selections) so that it will only print what is valuable to them at that time?


Thank you!

Hi!

Let's say I have a workbook containing of 10 sheets. I would like to create a macro that would allow me to do the following:

Print sheet 1, 3 and 7. Always print sheet 1, however only print sheet 3 and 7 if there in these sheets are values in the cells from row 8 and below.

(If that is to complicated it would be ok if the condition for printing sheet 3 and 7 is that there's a value in e.g. cell A8.)

I managed to create this script that allows me to print sheets 1, 3 and 7, however I can't seem to find out where to put the if-statement (I suppose that's how you do it?). Here's the script i created so far:

Code:

Sub Test_skriva_ut_flera_ark_samtidigt()
'
' Test_skriva_ut_flera_ark_samtidigt Macro
' 2008-03-13
'

'
    Sheets(Array("Sheet1", "Sheet3", "Sheet7")).Select
    Sheets("Sheet1").Activate
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
    Range("A1").Select
End Sub


I would really appriciate advice from you guys (and girls)

Thank you!

Kind regards,

Nicklas


Hi all,
I have VBA code to create a dxf file which has 1500 line compressed with : into about 100 lines

Code:

Print #1, "  2": Print #1, "HEADER": Print #1, "  9": Print #1, "$ACADVER": Print #1, "  1": Print #1, "AC1009": Print #1, "  9": Print #1, "$INSBASE


I would like to remove all the next line : and replace them with a new line in the VBA editor code so the code is easier to read and work with


Code:

Print #1, "  2"
 Print #1, "HEADER"
 Print #1, "  9"
 Print #1, "$ACADVER"
 Print #1, "  1"
 Print #1, "AC1009"
 Print #1, "  9"
 Print #1, "$INSBASE



Alt + 010 in the number pad doesnt work with the VBA editor find and replace tool

any ideas on how the code could be defraged?


I have set up a Database to store and handle test results on one product. Before I can ship the product, I have to print several reports. Most of the reports are printed as testing is completed, but 6 reports are summaries (its crazy I know, but I have to do what the man says to do).

I set up a macro to print each of the six reports. Then I noticed that everytime a report opened, I had to enter the criteria needed for that report. All 6 reports use the same criteria. So I thought that setting this up in VBA and using a variable would be much easier as I could enter the criteria 1 time instead of 6 times.

Here is the code from converting my macro to a module.

Code:

Function Buy_Off_Reports1()
On Error GoTo Buy_Off_Reports1_Err

    ' Appendix 20
    DoCmd.OpenReport "Appendix 020 - Static Vertical Force/Deflection", acViewPreview, "", "[buyoffnumber]=[enter buy off number]", acNormal
    ' Appendix 60
    DoCmd.OpenReport "Appendix 060 - Static Radial Force/Deflection", acViewPreview, "", "[buyoffnumber]=[enter buy off numnber]", acNormal
    ' Appendix 70
    DoCmd.OpenReport "Appendix 070 - Static Tangential Force Deflection", acViewPreview, "", "[buyoffnumber]=[enter buy off number]", acNormal
    ' As Built Summary
    DoCmd.OpenReport "As Built Summary", acViewPreview, "", "[buyoffnumber]=[enter buy off number]", acNormal
    ' CofC
    DoCmd.OpenReport "CofC", acViewPreview, "", "[buyoffnumber]=[enter buy off number]", acNormal
    ' Report35
    DoCmd.OpenReport "Report35", acViewPreview, "", "[buyoffnumber]=[Enter Buy Off Number]", acNormal


Buy_Off_Reports1_Exit:
    Exit Function

Buy_Off_Reports1_Err:
    MsgBox Error$
    Resume Buy_Off_Reports1_Exit

End Function


The "BuyOffNumber" is the exact same number for each of these six reports. Typing the same thing in 6 times is almost as crazy as having to print 6 reports (and then some) for one product. I am certain it is possible to set this up to use a variable. Where the VBA will ask the user (usually me) to enter the variable information, and then that variable will be used as the criteria for each of the reports. Once I have this working nicely, I am going to change the code so that the reports just printout instead of being previewed (I can do that part easily enough).

I spent a couple days searching through the archives and I have tried several variations on using a variable in the code, but no luck so far. Now I am admitting I could not find the information I wanted and have come here to ask. Thank you in advance for any and all help offerered.


Is there a way to have a macro that will copy/paste a print area from separate worksheets to one single worksheet called "Print Que".

I'm trying to figure out a way to have the print area that is copied sent to a worksheet "Print Que", But it needs to be in the next empty cell or below what was previously copied in a vertical column for printing. I'm trying to put together a button "send to Print Que" that will copy the print Area from one worksheet to another, but below the previous print area that was copy/pasted in the same column "b". the print area's in all of the sheets are in the same location. Is this even Possible? Any help would be appreciated.




Hi All,

Would it be possible to create a Macro that would enable me to, with a single click, send a print copy to the printer with the print area assigned to the last non-blank row?

The maximum print range that I have now is between B1 and N208. Although the number of colums won't change, the rows will. The table I am creating, is for people that will most certainly not know the difference between "update print area" and "print", and eventually, they will print without updating the print area resulting in 3 trees less in the world. Further, I have another sheet that will be the cover page for this printout. Could it be possible to have it printed automatically when printing the former?

So far, I have a code for simple printing that I created with record Macro:



Please Login or Register  to view this content.



Would it be possible to modify the code to include the resetting of the print area and the cover page?

Thanks to all

I currently have a print command button with the following VB code:

VB:

Sub CommandButton2_Click() 
    Dim rngToPrint As Range 
     
    Set rngToPrint = Sheets("Checklist Form").Range("A1:M28") 
    rngToPrint.PrintOut 
    Set rngToPrint = Sheets("Letter").Range("A1:B27") 
    rngToPrint.PrintOut 
    Set rngToPrint = Sheets("Appointment (2nd)").Range("A1:C25") 
    rngToPrint.PrintOut 
     
End If 
 
End Sub 


If you like these VB formatting tags please consider sponsoring me in support of injured Royal Marines



I want to restrict "CommandButton2" from printing if cells A13 & B13 are blank in sheet "Appointment (2nd)". I also want the user to be prompted that information is required in cells A13 & B13 before using this print button.

How do I add this to my current code quoted above? The code is currently in "Sheet1".



Good morning,

I was wondering if it is possible to check to see if a sheet exists in a workbook and if it does, delete it. The reason I am asking is that I have something that adds sheets to run various reports and then deletes the sheet once the report has run. The error handling kicks them back to the main menu if something goes wrong.

The problem is that if an error happens while trying to run one of the reports, the next time it tries to run the report it is going to generate an error when it tries to name the new sheet that was added for the report. Any ideas?

For example, I want to test if any of Sheets("Report1"), Sheets("Report2"), Sheets("Report3") exist and if so, delete whichever one.

Thanks,
dw


Why does my print macro print out two copies of the selected print range?

Code:

Sub PrintPR_Committments()

ActiveSheet.PageSetup.PrintArea = "Print_PRCommittments"
ActiveWindow.SelectedSheets.PrintPreview
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True

End Sub





I have an Excel/VB program compiled of a workbook with many tabs. I compile several workbooks, and when done, it pulls up a 3 tab workbook to allow you to print a table of contents based upon each workbook. Here's the formula shown on the TOC tab.

=IF(Print!$B$2=1,Print!$N$2,IF(Print!$B$3=1,Print!$N$3,IF(Print!$B$4=1,Print!$N$4,IF(Print!$B$5=1,Print!$N$5,IF(Print!$B $6=1,Print!$N$6,IF(Print!$B$7=1,Print!$N$7,IF(Print!$B$8=1,Print!$N$8,IF(Print!$B$9=1,Print!$N$9))))))))

This works if I only have up to 8 workbooks to compile and print in the TOC. If I have 9 or more workbooks, it bombs out. How can I expand this formula to work?


I want to add these numbered steps onto the macro I already have, which I pasted below.

1. Starting in Row 2 in the worksheet named Report 1 look at contents in Column A then look at the contents in Column C, if the contents in Column C do not match exactly the contents in Column A then copy the contents in the range column C:E from that row till the last row in Column C:E.

2. Now we will have a range that is made up of cells from C:E. I would like to move that range down a row at a time, until the first cell in Column C from the range Column C:E matches exactly the contents in Column A. Then I would like to continue this pattern for every row in the worksheet. Do you think you can help?




Code:

Sub Matchingcolumns()
    Sheets.Add
    Sheets("Sheet1").Select
    Sheets("Sheet1").Name = "Report1"
    Range("A1").Select
    Sheets("Report1").Select
    Sheets("Report1").Move Befo =Sheets(1)
    Sheets("Sum1").Select
    Columns("A:B").Select
    Selection.Copy
    Sheets("Report1").Select
    ActiveSheet.Paste
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Delete Shift:=xlUp
    Sheets("Status1").Select
    Columns("A:C").Select
    Selection.Copy
    Sheets("Report1").Select
    Columns("C:C").Select
    Selection.Insert Shift:=xlToRight
    Rows("1:1").Select
    Application.CutCopyMode = False
    Selection.Insert Shift:=xlDown
    Range("A1:E1").Select
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
    Selection.Merge
    ActiveCell.FormulaR1C1 = "Report1"
    Range("A2").Select
End Sub





I have a excel workbook with as many a 2 to 25 pages and can still grow. I use VLOOKUP on the second sheet to grab info form the first. Sheets 3 thru end with all populate based on sheet before. I have a formula on sheet 1 that determines how many sheets need to be print. How can I put this into a macro?

Here is what I have now. This only print 1 page per counter loop.

Sub doprint()
'
' doprint Macro
' Macro recorded 9/25/2003 by AvilaJ

Dim i As Integer
Dim oCell As Range


sname = InputBox("Start in Job Number?", " First Job to Print", 0)
sname2 = InputBox("Finish in Job Number?", " Last Job to Print", 0)

Range("I40").Select
ActiveCell.FormulaR1C1 = sname
Range("I41").Select
ActiveCell.FormulaR1C1 = sname2

For Counter = sname To sname2
Range("L5").Select
ActiveCell.FormulaR1C1 = Counter
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate _
:=True
Next Counter

End Sub


Hi there,

Ive been trying to find a macro that allows me to print certain sheet in a workbook to a single PDF using my "Adobe PDF" printer.

I've previously used a macro where if something exists in a certain cell, the sheet will print. This works but each sheet prints out in a seperate PDF.

The code ive used before is as follows:

Code:

Sub Macro2()
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
   If sht.Range("AJ1").Value <> "" Then
        sht.PrintOut
    End If
Next sht

End Sub


Can anyone help me please?

Matt


I am using the easy record macros function and I am needing a way to have one part of code to do a saveas to equal the cell value of G1. What I have recorded is saving as G1 instead of cell value. I have included the code i am using. any help would be appreciated thank you.

Code:

Sub NEXTREAD()
'
' NEXTREAD Macro
'

'
    Range("B1").Select
    Application.CutCopyMode = False
    ChDir "V:\O&M Maintenance\MINIMAX REPORTS"
    ActiveWorkbook.SaveAs Filename:="V:\O&M Maintenance\MINIMAX REPORTS\=G1[/u].xls" _
        , FileFormat:=xlExcel8, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Selection.ClearContents
    Range("C5:E81").Select
    Selection.ClearContents
    ActiveWindow.SmallScroll Down:=-72
    ActiveWorkbook.SaveAs Filename:= _
        "V:\O&M Maintenance\MINIMAX REPORTS\Next Read.xls", FileFormat:=xlExcel8, _
        Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
        CreateBackup:=False
    ExecuteExcel4Macro "PRINT(1,,,1,,,,,,,,2,,,TRUE,,FALSE)"
    Range("B1").Select
End Sub