I have a workbook with 150-some-odd sheets, each with a store number. I would like to loop through each sheet & print that sheet to the printer with the same name in my printers & faxes queue.
(eg. sheet 9604 would print to printer name oki9604)
I have a spreadsheet where I am trying to print barcode labels. The barcode is found in cell A1. I have looked in a lot of places and I can't really figure out exactly how to do it. This is the code that I have so far but it gives me an error. The printer name should be right. Some of this code I just copied from another thread.
[Code].....
Is there a Macro to automatically print the selected print area (print area is also established within the macro itself) to a user's default printer; whatever that printer may be? Note that this printer and/or printer name could change depending on the user.
I would like this to actually select and print to the default printer without Excel having to ask you. I don't even want the "OK" box to show up if possible. I just want them to run the macro, execute all it's duties, and print, all in 1 shot.
i have a macro that loads a userform for previewing and printing selected sheets. The macro works fine for previewing sheets but when i choose the print button the marco trys to save the file as a .mdi, i want this to select the default printer on the pc instead, the excel file will be onpened on many different pcs so the printers will be different on each machine, hence why i want a default printer selected. This is the section of code from the form
View 2 Replies View RelatedI am trying to swith between different printers to print out sheets within a single workbook. Recording the macro doesn't work very well and the code I found online doesn't work either (error: object doesn't support property).
How can I set this up to be a dynamic printer setting?
Code I tried:
I have done a userform which allows the user to select printing to each section(All, A, B or C) of the report to either paper size A3 or A4 looking something like below -
Print All A3 A4 (these buttons are option buttons)
Print A A3 A4
Prtint B A3 A4
Print C A3 A4
I have coded and part recorded a macro to change the paper size to either A3 or A4 depending on the user's selection.
The problem i have is if i take out the print command, it seems to change the paper sizes as it should but when i print it prints to the same size as the previous print i did, regardless of what was selected on the userform.
I have a printer with multiple tray sizes, and since I need to change paper sizes, I assume i need to change trays as well.
Below is the two macros -
'''''''''''''Set Print area and print to A3'''''''''''''''''
Private Sub PrintAllA3_Click()
Dim LastRow As Long
Dim LastCol As Long
With ActiveSheet
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(2, .Columns.Count).End(xlToLeft).Column
End With
[Code]....
What code can I write to print Sheet1 and Sheet2 on specific Printer and Sheet 3 to another printer, then all other sheets to another printer.
View 9 Replies View RelatedI want to automate (with the click of a macro button, or hot key sequence) is to:
· take a pre-set print area (which is always the same – 1 page wide x 1 page tall)
· send to the printer
· select the PDF printer from the list (not the default printer for the computer)
· Automatically change the save PDF file name to “(Cell A1) – Weekly Update – (Today’s Date).pdf”
· Change location to save file on Desktop
Feels like a pretty lofty goal for a macro to do all of that – please let me know if that is even possible to set up.
I have a macro that prints out several worksheets. My problem is that I want to print these worksheets to a specific color printer. There are many different users and most of them will have a black-and-white printer setup as their default. I want to be able to print using the ip address of the color printer.
I have tried setting the active printer using:
Application.ActivePrinter = "\WP010110.117.3.36"
with little luck... This seems like such a simple thing! I have done a lot of research and can't find a good solution although I have seen the question raised several times!
Ozgrid has been a great source of information and solutions in the past. Please help again!
I have developed one file for invoice printing using VBA coding.
When i take print out from excel workbook the print out is comming in Windows Font (i want like Dos prompt printing).
I want to take printout in Dot matrics printer for invoice printing and the print out has to come like Lotus123 spreadsheet print out (dos mode).
is it possible to get print out like Lotus123? If possible can u please tell me how to change the printing property in excel spreadsheet.
I have the following code to print a selection of data which is fine but i need to have in the code a different printer. So, for example my default printer needs to stay the same but when this spreadsheet is open i need the data to be printed to another printer.
View 3 Replies View RelatedWe have a an existing spreadsheet which uses a query from Excel to Sage Line50 Accounting software to create labels for stock items. The number of times a label is printed is dictated by the number of items that are required for a particular sales order.
For example -
1006abc 100 No.
2003abc 20 No.
The program would produce 100 labels for item code 1006abc, and would produce 20 labels for item number 2003abc.
However we have recently acquired a heavy duty industrial printer which prints to labels on a continuous roll and I am unable to alter the spreadsheet to work with the new printer layout.
I have attached the spreadsheet and, I hope, the VBA code. PLease also see this link to my previous post - Print Labels Dependant on Content
activesheet.printpreview , Excel crashes if there are no printers installed on the machine. How can i check for printers before calling the printpreview, or else stop the program from crashing?
View 2 Replies View RelatedThe below code opens up the printpreview window. This does not allow to select the correct (or change) printer it will automatic use the default printer.
Is there a way for the code to open the print window so I can change the printer and select number of copies?
Code:
Sub Print_Button()
Dim ws As Worksheet, cell As Range
Set ws = Sheets("main")
Set cell = Range("g2000").End(xlUp)
Do Until cell.Value ""
Set cell = cell.Offset(-1, 0)
Loop
ws.PageSetup.PrintArea = ("A2:" & cell.Address)
ws.PrintPreview
End Sub
Since upgrading to Windows 2007 (I was already using Excel 2007) I am having issues with the content in the cells on the worksheet not appearing the same on Print Preview and when I print. On the worksheet the cell show to be at the best fit both horizontally and vertically. When I look at the contents under print preview, the contents are squashed from the top and cut off from the left. This happens whether I have the format in Top or Central align and is even worse if I use Bottom align. It is also somewhat worse if I have thickened boarders.
I am using TrueType Fonts.
I just converted from Excel 2003 to 2007 and print previewed a worksheet and find a tiny image with a small portion of the print area. When I look at the sheet in page break preview mode, I see the 8.5 x 11 sheet broken up into approx 77 smaller sheets. When I try to move the page breaks to include the whole sheet it states the change cannot be made as it will result in an image less than 10%. I have attached two images - the first shows the print preview I get, and the second show the multiple pages when I view in page break.
View 4 Replies View RelatedI have a workbook that I am sending out to others for them to fill out. I would like to have them be able to click on a cell (or text within a cell) and have Excel print that worksheet in landscape mode or at least open the print dialog box.
View 9 Replies View Relatedwhat is the vba to print every worksheet within an excel workbook?
also, i've been working on saving each worksheet and have the code for that, therefore, can show me how to loop through the count of worksheets (which will be changing every time i run the macro).
is it somelike like count the worksheet(s) first and then loop all of them. from there within the loop i can call the sub SAVE() and use the cmd "ActiveSheet.PrintOut".
i am trying to use "Worksheets.Move After:=Sheets(Sheets.Count)"
but i am not getting there.
Subscript 9 error that Ive been getting when I run this macro. It has worked in the past, but when I tried to test it today I keep getting errors on the array portion.
[Code] .....
I finally found a vba that will print my excel worksheet as a pdf.
Sub PrinttoPDF()
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"CutePDF Writer on CPW2:", Collate:=True
End Sub
My questions are:
-How do I set a path for this printout. right now its printing out the pdf on my desktop.
-How can I name the pdf file to a value in a cell. right now i have to manually name.
-Is there a way to email this file from the path i just saved it in?
I've set up a BeforePrint procedure as follows:
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Application.ScreenUpdating = False
Dim ws As Excel.Worksheet
Set ws = ThisWorkbook.Worksheets("Objectives")
Dim i As Integer
With ws
For i = 9 To 33
If .Cells(i, 16) 1 Then GoTo NOTONE
If .Cells(i, 4).Value = "" Then
.Rows(i).EntireRow.Hidden = True
Else
.Cells(i, 4).Interior.ColorIndex = 2
End If
NOTONE:
Next i
End With
Application.ScreenUpdating = True
End Sub
... which works brilliantly....
My problem is that I don't know how to reset the sheet after the print!
Can anyone shed any light for me, please?
At my company we need to print our workbooks containing 3 to 24 worksheets. The first page should be printed on different paper than the other pages. From various forums I gathered that it's not possible to set the papertray using vba. Those forums suggest 3 things: 1. Windows Api functions (don't work at our company(security)), 2. SendKeys (don't work because of different printers, office versions and future-proofness) and 3.
Define printerdrivers per tray.
This last I did, so there are printerdrivers for tray3 and tray4.
With the following code I try to print the workbook:
Public Sub printSheets(strP1 As String, strP2 As String)
Dim curPrinter As String, firstPage As Worksheet, otherSheet As Worksheet, x As Integer
Set firstPage = Application. ActiveWorkbook.Sheets(1)
curPrinter = Application.ActivePrinter
Application.ActivePrinter = strP1
firstPage.PrintOut
The problem: The sheets are sent to the printers specified as strP1 and strP2, but the tray-settings from these printers are "overruled" by Excel; they are printed from the papertray that was the default of the printer when Excel was started.
Also, using the default printer dialog from Excel has the same problem; when the (windows-)default printer is Tray3 then all the pages come from there, even when printer Tray4 is selected as the printer. In Word or Acrobat etc the prints come from the right tray.
I have a worksheet set up with a number of pivot tables to show job summaries for each project manager. I created buttons labeled with each project manager that trigger a macro to filter the results to only show jobs for that specific project manager. There's one button for each project manager.
I'd like to create a macro that will essentially show each project manager's report and compile the result into one PDF. I only have 5 project managers so I don't need an array or anything if that makes it easier, I could write the code manually for each one.
So basically, it would do this:
1. Run macro to filter results to only show Project Manager #1 jobs.
2. "temporarily" print to PDF.
3. Run macro to filter results to only show Project Manager #2 jobs.
4. "temporarily" print to PDF.
5. Run Macro for Project Manager #3, etc.....
...............
Final step. Compile all the "temporary" pdfs into one file.
I am trying to copy the print settings(including the headers and footers) for one excel worksheet to another. I have found the following hint on internet and it works fine for copying the print setting(including the head/foot) but it is also grouping the worksheets. Click on the tab of the worksheet you want to copy. Then hold down the Shift or Ctrl key and click on the tab of the worksheet where you want the settings and click on File, Page Setup and OK. How can I just copy the print setting without grouping the worksheets?
View 4 Replies View RelatedI would like to set the printer area of an Excel worksheet. I would like to print one page starting from row A3005-V3220. However, I cannot change the print area. Excel wants each row to be another page. When I drag the blue print area lines, nothing happens. So right now I have 216 pages and only want one!
View 3 Replies View RelatedIn Column C (C11:C28) I have Sheet Names - Descriptors and then in Column D (D11:D28) I have the "YES" or "NO" for whether the sheet should print There could be blanks rows or rows with just heading text on them.
This project uses Table of Contents and what is on the specific sheet I use a Formula [=CONCATENATE("Agenda1 - ",Agenda1!H1)] on some of the Rows. These all then Hyperlink to the appropriate worksheet. Not all Rows will have the Formula if it is easily identifiable.
The name of the sheet is Either the Name As Is Unless there is a hyphen and if there is a hyphen then the sheet name is the first word up to the space.
I'm sure this is doable with VBA I just don't know how to manipulate the - out of the names and do the appropriate sheet selections so that I can print all of the selected sheets.
My current data looks something like this:
Info Pages Print (Merged Cell Header Row)
Title YES
Implementation NO
<blank row>
Agendas Print (Another Merged Cell Header Row)
Agenda1 - FORMULA NAME1 YES
Agenda2 - FORMULA NAME2 NO
Agenda3 - FORMULA NAME3 NO
... thru Agenda7
Other Print (Merged Cell Header Row)
Other1 - Formula Name NO
Other2 - NO
Other3 - NO
My workbook has worksheets with names: Title, Implementation, Agenda1, Agenda2, Agenda3,...Other1, Other2, Other3
I want to select all of them that have the YES to Print and then send them to my printer.
I tried recording a macro but I am keep on getting an error. I need a macro which will print the selection of A1:D28 on a worksheet called Invoice. The print needs to be scaled at 165% of its normal size. i need it to be normal margins and if possible any printer.
View 1 Replies View RelatedI am trying to create a simple macro to print a worksheet with a button. The worksheet will have additions/deletions on a daily basis, so it need to adjust accordingly. I cannot figure out how to do the loop macro.
View 9 Replies View RelatedI have a some code that we use in our office that works ok on my pc. When others in the office try to use the macro the year gets changed to 19 instead of 09. The only problem I have is that the code seems to slow down when it is trying to print the worksheet out. Can the code be shortened up/cleaned up. We are looking for gridlines with inside /outside lines, landscape and left/right margins of .25
Sub IC_Delays()
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Add
Windows("Delays List.xls").Activate
Range("F15:N" & Cells(Rows.Count, "F").End(xlUp).Row).Copy
Windows("Book4").Activate
Selection.PasteSpecial Paste:=xlPasteValuesAndNumberFormats
Range("A1:I" & Cells(Rows.Count, "A").End(xlUp).Row).Select.........
I made a macro to print the Invoice worksheet and I added a button to run the macro but I don't think I did it correctly.
Sub Print_Invoice()
Range("A1:G35").Select
Selection.PrintOut Copies:=1, Collate:=True
End Sub
This is the information I used for the command button:
Private Sub CommandButton1_Click()
Range("A2:G34").Select
Selection.PrintOut Copies:=1, Collate:=True
End Sub
