Macro: Print Ranges From Multiple Sheets
Dec 6, 2006
I am trying to write what I thought would be a simple macro to print out specific areas of my worksheets. I have shown the code below; the line causing the problem I have highlighted in RED. I am getting the following error message: "Select method of range class failed".
Reading other posts here. I think this may have something to do with the macro being assigned to a command button in one worksheet (AY114) and I am trying to get the macro to run on both the worksheet that the command button is in (AY114) as well as another worksheet (AY062).
Sub CommandButton1_Click()
Range("J2").Select
Sheets("AY114").Select
Range("A4:J53").Select
ActiveSheet.PageSetup.PrintArea = "$A$4:$J$53"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
ActiveWindow.SmallScroll Down:=45
Range("A56:M151").Select
ActiveSheet.PageSetup.PrintArea = "$A$56:$M$151"......................
View 4 Replies
ADVERTISEMENT
Sep 24, 2013
I've found some code which works to print certain pages with value in cell A1 but I need to print dynamic ranges on some of the sheets as they will have filters on so the rows ranges will be different each time.
So far this is what I have but the dynamic range part is not working:
VB:
Sub Print_All_Worksheets_With_Value_In_A1()
Dim Sh As Worksheet
Dim Arr() As String
Dim N As Integer
[Code] ....
View 3 Replies
View Related
Jun 16, 2007
I inherited a spreadsheet that had an userform where the user checked off which 'pages' he wanted to print. The Ok button routine used if statements to run a routine for each 'page.' Here's an example of the original code for one page:
Sub Button2_Click()
Sheet7.Activate
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.RightFooter = " Construction Assumptions"
.PrintArea = "CONSTRUCTION" 'the named range to print
.Zoom = False
.FitToPagesTall = 1
.FitToPagesWide = 1 'this changes depending upon the page selected
End With
ActiveSheet.PrintOut
End Sub
The problem was it printed each page as a separate print job; and if you print to adobe, you get serveral files, not one file. That and it took a long time to run.
So I tried a different tack. If the checkboxes has true, then the printarea is set to that named range. If there were more than one named range on a sheet to be printed, I consolidated them. I did this with a bunch of if statements - very cumbersome.
Sheet3.Activate
'Sheet3.ResetAllPageBreaks 'disabled due to errors
Run "HorizontalPrintStuff" 'generic landscape pagesetup
With ActiveSheet.PageSetup 'specific pageset settings
.PrintArea = "DEVBGTALL" 'the named range to print
.FitToPagesWide = 4 'this changes depending upon the
.FitToPagesTall = 1
End With
I haven't shown all the code cause it goes on for 12 sheets containing 16 different printareas.
My current muck ups are .....
1) it prints every printarea/named range on a given sheet (I took out all the if statements trying to debug everything.) Is there another conditional argument that allows for multiple 'trues'?
2) the pagebreaks in printarea/named ranges that are multiple pages (like a 48 month schedule) won't stay set. I've tried both VPageBreaks(3).Location:= and .VPageBreaks.Add Before:=
3) the Sheet1.select false argument is always adding a random sheet to the end of the print job. Don't know why.
I can do all this in a recorded macro, just not the selection userform. I've thought about copying to another sheet or hiding columns and rows then printing, but that seems just as cumbersome.
To recap, i want to print out, as one print job, multiple printareas from mulitple sheets, based upon checkbox selection on an userform.
View 6 Replies
View Related
Apr 29, 2013
What I have is a large number of sheets in a workbook (26 to be exact). Each of these sheets has one specific named range. The file itself is quite large so I would like to print these ranges to a single PDF file. I did my best to search for this topic in the forums and while I did find some macros that were close, there was some sort of piece of code missing. Also, will how I list the named ranges determine the order of how they will show up in the PDF File?
View 3 Replies
View Related
Mar 26, 2014
There were 2 macros. One printed all of the defined ranges (40) on separate pages, and the other printed all the graphs ("charts" - 39) on separate pages. I tried to combine the VBA code to print each range and then the corresponding graph. Everything is still on separate pages, but it saved time because I didn't have to collate after printing. It seemed to work. Then I tried figure out if I could print them all to a specific tray of the printer as set in the workbook or as the printer default. Now both the combined macro and the original macro are giving errors.
Is the code I have correct to do what I am trying to do (print each range and then the graph all on separate pages)? Is there any way to put the output tray choice into the macro?
Sub Load_Data_Report()
'
' Load_Data_Report Macro (print all tables & graphs)
' Macro recorded 12/21/00 by xxx
'
' Keyboard Shortcut: Ctrl+j
'
[Code] ........
View 1 Replies
View Related
Aug 25, 2010
I am trying to build a macro to work with a template file.
The template file has set sheet numbers and names (with one exception, see 4) below).
There are close to 40 sheets in all.
Some sheets are never printed.
Some sheets are always printed.
Many sheets are printed only if they are used.
Most of the sheets are 'break out' sheets and a variable number of them will be used.
So... Here is what I'm trying to accomplish.
1) Always print Sheet4(Overview1)
2) Always print Sheet6(Overview2)
3) Print Sheets 11 thru 40 IF value in cell G50 on these sheets is > 0. Note that this cell has a name (Total) and I would like to reference the name if possible. (It could happen that further evolution of the Workbook moves the cell up or down a row.)
4) Print Sheet38(Data Sort) IF it exists. Otherwise, print Sheet1(Data).
If the information on the Data sheet is limited (eg only 1 or 2 pages), we print it off directly. If the information is more extensive, we copy it to a new sheet and sort it (Data Sort).
I can achieve 1) and 2). I'm not quite sure how to go about 3) as I'm still not very good with macro loops. 4) I haven't tackled yet.
View 3 Replies
View Related
Nov 18, 2008
So I've tried this a few different ways and every time I get the 1004 error: "Unable to set PrintArea property of PageSetup class"
Basically I have a macro that goes through a workbook to hide certain rows and columns based on some user inputs. After doing this I would like to set up appropriate print areas on certain sheets so that when you go to print anything it comes out clean. I thought this would be easy but I'm stuck.
The Plans variable in the code below is an integer from the user input. Think of this code as grabbing two separate boxes and setting them as print areas, which I've know I can do manually because I've tried it.
What's wrong with the following code?? (I've also tried using the union function here, to no avail)
View 6 Replies
View Related
Jan 24, 2012
I have a worksheet that has a few ranges and I need a printarea statement that looks like this:
Code:
ActiveSheet.PageSetup.PrintArea = "$A$1:$F$26,$G$1:$L$9,$M$1:$P$16,$Q$1:$S$7"
The above works, but each time I generate this worksheet, the ranges for the last row of each area can be dynamic.
So, I tried something like this:
Code:
Sub setPrtArea()
'set the print area
lr1 = Range("F65536").End(xlUp).Row
rngA = Range("$A$1:$F$" & lr1)
lr2 = Range("L65536").End(xlUp).Row
rngB = Range("$G$1:$L$" & lr2)
lr3 = Range("P65536").End(xlUp).Row
rngC = Range("$M$1:$P$" & lr3)
lr4 = Range("S65536").End(xlUp).Row
rngD = Range("$Q$1:$S$" & lr4)
ActiveSheet.PageSetup.PrintArea = rngA & "," & rngB & "," & rngC & "," & rngD
But, it fails. I have looked through many topics on this subject, but nothing seems to fit my scenario. This will pretty much complete my current project if I get this figured out and can export these print areas to pdf without a bunch of blank pages as I get now with no print area set.
View 9 Replies
View Related
Apr 21, 2013
I've got several worksheets that all have the exact same layout that a user will enter unique information in to each worksheet. Then I've got a final worksheet that I want to have a button that the user can click and when they do, it will look to each worksheet and do the exact same process for each worksheet as follows:
It first looks to see if the worksheet is visible. If it is, I want it to copy the range A5 to K5 down until it gets to the last non-blank cell in column C. The first non blank cell that will be referenced will be C7. Then I want it to paste this information into the range A5:K5 on the final sheet named Sheet8 with the same values and keep cell formatting such as width and height, font. If the worksheet is not visible, it skips the sheet.
I want it to do this for each visible worksheet, placing the next visible worksheet info under the previous visible worksheet info. My current code as shown doesn't do that. It requires that something be inSheet8 A6 before it will even paste, then it pastes the info from A5:K5 but it doesn't do just the values nor does it keep the formatting. What I mean about not doing just the values is some of the info that needs to be copied comes from a drop down they can choose from and it copies the actual drop down menu. Also, it seems to copy all of the ranges from each sheet and paste it into just A5:K5 on Sheet8 and overwrites each other instead of pasting Sheet2 just below the information from Sheet1. So the only information shown after the entire process is completed is the information from the last visible sheet.
If Worksheets("Sheet1").Visible = True Then
Sheets("Sheet1").Range(Sheets("Sheet1").Range("A5:K5"),
Sheets("Sheet1").Range("C7").End(xlDown)).Copy
Sheets("Sheet8").Range("A5").End(xlDown)
End If
[Code]...
View 4 Replies
View Related
Jul 6, 2007
I need to be able to track changes on selected ranges on multiple sheets, but Excel does not appear to be able to do this. It only appears to allow me to select multiple ranges on the same sheet.
is there a way to track changes on multiple selected ranges on multiple sheets
View 9 Replies
View Related
Jul 17, 2013
I have 23 sheets out of a much larger number of sheets, all in one workbook. How can I print only the first 23 sheets all at once?
View 6 Replies
View Related
Apr 5, 2009
I've been feverishly searching for a pre-existing macro to print specific sheets assigned manually by the macro to the windows default printer (or better still bring up the printer dialog box and you can select which printer!)
The macro is attached to an image on Sheet 3 called "Details"
The respective sheets I want to print in succession are Sheet 2 "Letter Of Estimation", Sheet 6 "Labour & Equipment SOR" and Sheet 7 "Labour Only SOR".
Can anyone help me with this please? I've been searching for a while and come up with various bits of code that perform intricate loops based on user input, and other unrelated tasks I can't seem to ween out of the code by myself.
View 8 Replies
View Related
Apr 19, 2008
I have a workbook consisting of about 20 worksheets. I have VBA code that prints any sheet that is used but skips any pages that are not used. This works fine but if there are more than one user printing sheets at the same time on our network printer, the pages get all mixed together and they have to pick through the stack to find their pages. Is there a way that the used pages can be assembled and then all printed as one printjob so that each users pages will all print together simplifying the sorting process.
View 9 Replies
View Related
Apr 14, 2014
I'm trying to select multiple sheets and print them out. At this time the code is only printing out the "Work Order" sheet. I'm guessing it's something to do with the PrintOut command trying to print the active sheet and not the array?
Code:
Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
ActiveSheet.PrintOut Copies:=1, Collate:=True
I have also tried the following but it just prints out every page in the workbook.
Code:
Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
Sheets.PrintOut Copies:=1, Collate:=True
I've also tried the PIDOOMA approach with this and failed
Code:
Dim TechnicianPack As Variant
TechnicianPack = Sheets(Array("Work Order", "Timesheet", "Communications")).Select
Sheets("Work Order").Activate
TechnicianPack.PrintOut Copies:=1, Collate:=True
View 7 Replies
View Related
Jun 23, 2014
In making my label printer, I will need to set the print area for multiple sheets based on a range generated in a cell. E.g. Sheet1 might need cells A1:P1200 set as print area, Sheet2 might need cells A1:G694 set as the print area, Sheet3 might not need to be printed, etc..
I have no problem generating a formula to state what the print area for each sheet should be, respectively, but passing that info into VBA has me really stumped. (Using indirect in the Page Setup menu doesn't work after the value has changed, it changes the value to a static reference, which is bizarre). I suspect I'd want to use the Sub Workbook_BeforePrint, but I'm not 100% on that. I will generally have 3 or more specific sheets selected using a button-triggered macro that gets a cell value of sheet names and selects those sheets.
View 4 Replies
View Related
Jun 13, 2007
I'm trying to write a macro to select all the Sheets in a Workbook, and set some properties [Auto ColumnWidths, Landscape, and Fit to 1 page wide] for all of them.
I don't know the names of the sheets, nor how many there will be - this part is tagged on the end of a long macro that creates new files and pastes various data into them. The code below only seems to work on the Active sheet - not any of the others selected. Curiously, I can set a specific column width for all sheets, but not Auto Widths.
With ActiveWorkbook
Sheets.Select
With ActiveSheet.PageSetup
.PrintTitleRows = "$1:$1"
.Orientation = xlLandscape
.Zoom = False
.FitToPagesWide = 1
.FitToPagesTall = False
End With
End With
Sheets.Select
ActiveWindow.Zoom = 80 ' This line works!
Cells.Select
Selection.RowHeight = 13.5
Selection.EntireColumn.AutoFit
Columns("C:C").Select
Selection.ColumnWidth = 34 ' This line works
Range("A1").Select
View 2 Replies
View Related
Jun 11, 2008
I am working on a userform in which user can select diffrent sheets for print.
I want all selected sheets to be printed in a single pdf and it could be great if it not ask for a path to save file.
View 21 Replies
View Related
Jan 24, 2010
Sub DynaSort()
Dim wsSheet As Worksheet
iRow = ActiveSheet.Columns("A").End(xlDown).Row
For Each wsSheet In Worksheets
Select Case wsSheet.CodeName
Case "Sheet2", "Sheet3", "Sheet4"
wsSheet.sort.SortFields.Clear
Range("A3:I" & iRow).Select
wsSheet.sort.SortFields.Add Key:=Range("F2:F" & iRow) _
, SortOn:=xlSortOnValues, order:=xlAscending, DataOption:=xlSortNormal
wsSheet.sort.SortFields.Add Key:=Range _
("H2:H" & iRow), SortOn:=xlSortOnValues, order:=xlDescending, DataOption:= _ ...................
The problem that I has is that I cannot put focus on a cell after the sort. Xl keeps the columns selected and then when I'm trying to put in the next data Excel selects all the rows in Sheet1 also. I know how to get rid of it and continue, the users on the other hand are not that experienced with excel. fun thing, even thou the, Range.value is inside the IF it putt "pucko" in sheet1. I have a code that copies the data and then put some several functions in each sheet, after that I call the sort routine.
View 8 Replies
View Related
Dec 29, 2012
I have a workbook that has multiple sheets for patients (each named with SSN) with all such sheets having identical structure. I have the VBA code below that prints all sheets. Each sheet will have 7 pages when printed. Is it possible to amend the code so that it prints page 1 and page 3 from each sheets only?
VB:
Sub PrintSpecificSheets()
Dim WS As Worksheet, mySheets()
Redim mySheets(0)
[Code]....
View 1 Replies
View Related
Sep 5, 2007
I am trying to find a way to select and print multiple sheets based on a criteria or a list. I have a large worksheet with many sheets. Each sheet falls into one of three categories, and I want to be able to automatically print all tabs in each category. I have all of the sheets rolled up into a summary where I have access to all of the sheet name and print criteria.
View 4 Replies
View Related
Feb 23, 2012
Code:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Intersect(ActiveCell, Sheet8.Range("C16:Y1000")) Is Nothing Then Exit Sub
Dim rw As Integer
Dim arw As Integer
rw = 16
[Code] .....
As you can see, my code is located in the sheet8 worksheet object. Now, I have a few questions about this. Because I am located in the sheet8 worksheet object does that mean my code can only work in sheet8, i.e., the following won't work because I am in a Sheet8 worksheet object?
Sheet10.Range("B12) = ..... ....... .....
This is not returning a value in Sheet10? My question is how do I make my code return a value in Sheet10?
View 6 Replies
View Related
Mar 29, 2012
I need to clear the the text in the same cell ranges on multiple worksheets. on a regular basis.
F7:K13
Q7:Q13
Is there a simple way to do this?
View 7 Replies
View Related
Jul 20, 2009
Is it possible to form a single Range object from ranges on multiple sheets. So for example, would it be possible to set a Range object equal to cells A1:D146 from Sheet 1 and A1:B49 from Sheet 2 and if so, what would be the syntax? For the first I have:
Sheets("SelectData").Range("A1:D146").SpecialCells(xlCellTypeVisible)
but I wouldn't know how to proceed from there.
View 9 Replies
View Related
Sep 9, 2006
I'm in the process of placing a button on sheet#1 that will print any of the 5 sheets in the workbook that have been used... a sheets use is determined by cell I53 being > 0...
I've initially used this code to test I53, but it shows all sheets, regardless of I53 value...
Sub PrntUsedShts()
Dim ws As Worksheet
For Each ws In Worksheets
If Range("I53").Value > 0 Then
ws.PrintPreview
End If
Next ws
End Sub
Ideally, the finished code will send all selected sheets collated as a single print job...
Should I be looking at putting test positive sheets into a sheet array?, then printing the sheet array?
Once again, your valued input is greatly appreciated...
View 9 Replies
View Related
Mar 16, 2009
in creating a macro so that I can print from sheet 1 to sheet name "XYZ" as an array. In other words I want to print selected sheets as one command so that page number in the footer will automatically change.
View 14 Replies
View Related
Nov 9, 2011
with an event macro. Basically I have a workbook with 11 worksheets and have created a macro that increases the value of cell J3 by 1 in Sheet1. However at present it only works from Sheet5, but I now need it to run from Sheet6 and Sheet11 as well and not from any other worksheets. The code I have is as follows
Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.CodeName = "Sheet5" Then
With Sheet1
.Range("J3").Value = .Range("J3").Value + 1
End With
End If
End Sub
View 1 Replies
View Related
Feb 15, 2010
using a macro to print all sheets in a workbook. During this process each sheet has a print area to print as well. A button will be placed on all the sheets. No matter which sheet you are on I would like it to print all sheets. The code I have works I think, but I would like to not have to go by sheet names in case there are some added later, etc. Also, is there a way to do this without going to each sheet and the user seeing all this? Would like to do it in the "background" so to speak.
Sheets("sheet1").Select
'Range("B2").Select
Range("H1:K1").EntireColumn.Hidden = True
Range("O1:S1").EntireColumn.Hidden = True
Range("V1:W1").EntireColumn.Hidden = True
Range("Y1:AC1").EntireColumn.Hidden = True
Range("L1:N1").EntireColumn.Hidden = False
'Print range column A thru AD
ActiveSheet.PageSetup.PrintArea = "A1:AD84"
With ActiveSheet.PageSetup
.Orientation = xlPortrait
.Zoom = 58
' .FitToPagesWide = 1
.BlackAndWhite = True
.PrintComments = xlPrintNoComments
.CenterHorizontally = True
End With
ActiveWindow.SelectedSheets.PrintOut Copies:=1, Preview:=False, collate:=True
Range("H1:K1").EntireColumn.Hidden = False
Range("O1:S1").EntireColumn.Hidden = False
Range("V1:W1").EntireColumn.Hidden = False
Range("Y1:AC1").EntireColumn.Hidden = False
Range("L1:N1").EntireColumn.Hidden = True
Range("A1").Select
View 9 Replies
View Related
Feb 10, 2014
I'm using Excel 2010 and would like to know if it's possible to convert selected ranges in multiple sheets into one PDF file? For example, I want to select range("A1:O10) in Sheet1 and range("A1:N25") in Sheet2, then convert both Excel sheets into PDF file with two pages.
View 2 Replies
View Related
Apr 18, 2013
I am working on a "3 worksheet" excel workbook. The first worksheet does not require any header.
I'd like to enter data into the second sheet (say cells A1 and B1), and use VBA to pull from those cells to generate the same custom header for both the second and third worksheets.
For example, I'd like the header to pull "# 123456" from cell A1, and "789" from B1 in sheet two, putting them in a centered header for both sheets two and three (same reference cells from sheet two for both, not new values of A1 and B1 from sheet 3 for sheet 3 header). I'd like to format in a way that looks something like this:
#123456
789
I'm currently running Excel 07, and was able to pull from a cell on one worksheet into that sheet's header but couldn't get it to span multiple sheets.
View 3 Replies
View Related
Apr 17, 2014
I am relatively new to VBA macros. I am having two sheets, in one sheet I have a non-contiguous 20 rows range and in the other sheet I have a 20 row contiguous range. I need a macro which will compare data between the two ranges(one to one compare) meaning 1st row of the first range should compare with 1st row of the second range and if it matches then it should populate the adjacent column in the second sheet with true or false accordingly.
View 2 Replies
View Related
