VBA To Print Multiple Reports In One PDF
Jul 26, 2013
How to print multiple reports and have come up with the following code which does exactly what I want, except it prints the reports out individually. I need them all combined into one PDF.
Sub PrintCombined()Dim c As RangeApplication.ScreenUpdating = FalseWith Sheets("Key Metrics Report")
For Each c In .Range("S1", .Range("S1").End(xlDown)).Range("C2").Value = c.Value.PrintPreview 'PrintOutNext c
End With Application.ScreenUpdating = TrueEnd Sub
View 3 Replies
ADVERTISEMENT
Feb 17, 2014
I have an expense workbook (Data) with 4 columns (ID, Item, Cost, Date). I would like to create macros that will generate 3 different reports and write to 3 different worksheets.
The first report is sorted and sum up the cost for each item. Please see the worksheet "Item".
The second report is sorted and sum up the cost for each part ID. Please see the worksheet "ID".
The third report is the cost for each month and Year-to-Date cost right next to it. Please see worksheet "Summary".
write the macros for each of these reports assuming that we don't know the number of rows in the "Data" worksheet.
View 4 Replies
View Related
Nov 3, 2006
I'd like to be able to drop one table of data into a workbook that already has sheets of two or more master tables, and two or more report pages. A formula on the report pages would compare aspects of the data to the master tables, and report the data on the correct sheet based on certain criterion. The reports would not have blank spaces, need to be sorted further, etc.
My initial impulse was to think in terms of ol' verable VLOOKUP, but I can't figure out how to get the report formula to move on to the next line of the data table if it doesn't find a match for the first line - short of adding another VLOOKUP (and another, and...), which is totally impractical given that the data table will likely have 20,000+ lines.
View 4 Replies
View Related
Jun 24, 2014
I am creating a report where there are multiple conditions and I am using MS Excel 2010.
Summary sheet will display
Date of Completion - If the exam has been taken already
In Progress - if the exam has been started but not yet completed
Not Started - if the exam has not yet been taken
Available data in Source sheet
Completion_Date - Date when the exam was taken or "Blank" if the exam is still In Progress
Completion_Status - Completed & In Progress only (all subjects that are not in the Source sheet shall be tagged as "Not Started"
View 4 Replies
View Related
Sep 30, 2013
I have a workbook that contains
Tab 1 --a datatable with mulitiple suppliers. Data changes once a week.
Tab 2-25 --Report Tab for each client--to be sent out to client weekly to update status of deliveries.
Current process:
1. copy and paste data table into excel model Tab 1
2. use advanced filtering to pull data from the datatable into each tab one tab at a time--25 times!
3. Sort the date in the report tab--25 times
4. Format the report tab--25 times
5. Copy and Paste into a separate workbook--25 times
6. Send each report--25 times
How do I reduce the number of steps in this reporting process?
View 1 Replies
View Related
Feb 14, 2007
I have a spreadsheet with 28 visible tabs. When I select all tabs and print, I get 3 cover (or banner) pages...I like having the first banner page, but the other two are in the middle of my report. I assume excel is sending 3 print jobs and I'm not sure why.
I've seen a couple of posts regarding this issue when using VBA, but I'm not using VBA...just selecting all the sheets and sending a single print job, but get 3 banner pages.
View 9 Replies
View Related
Jun 2, 2008
I'm using VBA code to select several worksheets at once, and print them. The code runs fine, but the result is multiple print jobs, not one job. This is OK when printing to the printer...but undesireable if you want to print to a pdf file, for example.
I've searched the site and found some references to this possibly being an excel problem. Is that true?
I've also checked the page setup setting to make sure that the print resolutions are all the same...that doesn't appear to be the problem either.
View 6 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
Dec 22, 2008
I have 3 sheets in the same workbook that I want to print in 1 PDF report, is there a way that I can do this?
View 2 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
Aug 10, 2007
I have around 100 pdf files. I need to print specific pages from those files (e.g. 5 to 10). I want to build a macro to do this task automatically.
View 8 Replies
View Related
Apr 22, 2009
Is there a way of selecting several print areas on several worksheets and printing it on ONE page? Selections will not be the same size.
Also is there a way to lock your printareas? I would like to save the spreadsheet and open it up next month and the printareas are still there ready for me to print. It seems to reset after i have printed it
View 3 Replies
View Related
Feb 16, 2014
How to print multiple sheet using one command ?
View 3 Replies
View Related
Nov 8, 2008
I have a macro that cycles through several student ID numbers and prints a progress report for each one. It works great, but I would like to add an option to pick which printer to use at the beginning of the code. In other words, when I run the macro, the printer box opens and I can pick the printer. When I click okay to print, the macro continues.
View 4 Replies
View Related
Aug 25, 2013
In my excel, i have 3 worksheets. On the first worksheet, i have 2 pages since i have 2 sections side by side. I can't stack the sections due to different column widths. I am trying to see if there is a macro to stack the 2 pages on the first worksheet as one pdf page, and then have worksheets 2 and 3 as pages 2 and 3 of the pdf.
View 1 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
Apr 23, 2014
I am looking to design a Userform with "OptionButtons" in which the user selects 1 of 4 print formats. I was going to have one single button that stimulates the userform mentioned which allows the user to pick from 4 options. Then there will be a commandbutton1 on userform which allows them to "Print Preview" the selected print style and a commandbutton2 on the userform which allows them to "Print" with the chosen style. The following are the 4 Print Formats:
Print1:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Rows 1-17 are Normal
From row18 on every row with text in columnA becomes a print break with row16 as a header before it. (the point of this is that row16 is a header but since it will always on the first page and row17 is a text row that would kick it on the next page)
Print2:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break
Print3:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break. Hide ColumnE and ColumnF after row15.
Print4:
.2 Margins (Top/Bottom/Sides)
Fit all Columns To Page
Cells A1:F15 select fit to page and center (This will be the 1st page)
Row16 becomes header for the rest of the sheets. Then each row with text in columnA becomes a print break. Hide Column A, B, E, F after row15.
View 1 Replies
View Related
May 14, 2014
I am in need of creating a shortcut in excel where I can prompt a command to print a specified set of worksheets in an excel file, so in the future I can skip the process of manual selection.
I have an excel file with 50 tabs, naming such as : Microsoft, HP, IBM, Cisco, Google, Facebook, Twitter, Kaiser, BlueCross, BlueShield..... etc etc.
I want to create a "shortcut" to command excel to print a specific group of tabs with just one click. For example: Healthcare companies (Kaiser, Bluecross, Blueshield). Instead of manually select these companies each time, is there a way to create a one click shortcut that will automatically select the tabs I needed?
View 9 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
Jan 10, 2007
I have about 100 different workbooks that all need to have the same format and page set up for printing. The different workbooks just all need to be setup to have landscape.
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
Nov 16, 2012
I have an Excel sheet with several thousand rows. Now I want to create a report using this Excel file such that when I print the Excel File it must be limited to a specific number of row for instance 100 rows per page and at the bottom of that page I want Excel to display the sum of the above 100 rows for one single column. If I do this manually it would take hours therefore I want either a Macro for this or some shortcut to achieve what I am looking for.
View 1 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
Jul 12, 2008
The file consists of 3 sheets:Orders,Order Form PO1 & Purchase Order. When I mark "x" in the row that I want to print, Order Form PO1 will be displayed with all data in the specific columns. However, it only allows me to select one row at a time. Now, I want to select multiple rows so that it will pass the data to the sheet Purchase Order.
View 13 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
Aug 13, 2012
I'm trying to find a solution to print to PDF specific worksheets in multiple Excel files. The files reside in the same directory, and I would like to be able to print them all at once to PDFs.
View 3 Replies
View Related
Jan 25, 2013
I have data range which have three sections identical in shape and size.
How should i set the page setup so that each section print on a separate page.
View 1 Replies
View Related
