Free Microsoft Excel 2013 Quick Reference

select printer macro

morning,
i'm using Office 2007 on Vista, and i have the following event recorded in
"thisworkbook"

Private Sub Workbook_BeforePrint(Cancel As Boolean)
If ActiveSheet.Name = "Purchase Order (Inventory)" Then
Cancel = True
Application.EnableEvents = False
With ActiveSheet
.PrintOut
Call POInv
End With
Application.EnableEvents = True
End If
End Sub

it works, but the problem is it sends the print job to the last used printer
or the default printer. i want to beable to select the printer before it
prints - maybe another macro gets called or something. any suggestions on
getting the macro to select a printer.

thank you,

jat


I currently have a spreadsheet that I run a macro on to save the sheet and print 2 copies out. This works OK.

In some cases I now need to output as a PDF and print 1 copy.

I have found the relevant code for swapping the active printer to my PDF printer and back, however, what I would like is one print button that knows whether I require 2 printed copies or 1 PDF+1 print.

I have put a vlookup on my sheet to find and show this setting in cell L2; so basically I want the macro to run

IF L2 <> PDF then print 2 copies ELSE print 1 to pdfprinter and 1 to printer.

How do I build this IF statement into my current code?

Thanks

I have recently upgraded to Office 2010, which has proved to be a massive mistake on the VBA side of things. Most of the kinks i have managed to work out, although my code runs extreamly slow, compared to office 2007 (i guess windows 7 x64 could be an issue there)

Bu the one problem i cannot solve is that now office 2010 will not record a macro when i enter the file/print, so i cannot see the path that excel uses to find my printer.

Which i now need as the set page size on excel 2010 is now printer dependent.. so if i have the wrong printer selected the code fails as my printers have different code for page size 11x17 (one calls it tabloid)

Bellow is the code, in yellow is marked how i am trying to tell excel to select printer.. I have tried "servernameprintername" but no luck and because office 2010 will not record me selecting printer i cannot work it out

Any suggestions???

Thanks in advance

p.s. this code worked perfectly with excel 2007, no matter which printer was selected...

Sub Print2()

    Application.ScreenUpdating = False

    Sheets("Version").Select
    Cells.Select
    Selection.EntireColumn.Hidden = False

    Application.ScreenUpdating = False
    
        Columns("A:B").Select
        Selection.ColumnWidth = 3.14
        Range("C:C,AI:AI,AJ:AJ,AR:AR").Select
        Selection.ColumnWidth = 24
        Columns("D:D").Select
        Selection.ColumnWidth = 30
        Range("E:E,G:G").Select
        Selection.ColumnWidth = 35
        Columns("F:F").Select
        Selection.ColumnWidth = 10
        Range("O:O,S:S").Select
        Selection.ColumnWidth = 6
        Range("H:K,M:M,P:Q,T:T,V:V,X:AA,AC:AG,AK:AM,AO:AQ").Select
        Selection.ColumnWidth = 5
        Range("L:L,N:N,R:R,U:U,W:W,Y:Y,AB:AB,AH:AH").Select
        Selection.ColumnWidth = 15
        Rows("1:1").Select
        Selection.RowHeight = 45
        Rows("2:500").Select
        Selection.RowHeight = 35
    
    Application.ActivePrinter = ""
    
    ActiveSheet.PageSetup.PrintArea = "mydata2"
    With ActiveSheet.PageSetup
        .LeftHeader = "&""-,Bold""" &
Worksheets("Front").Range("A9") & _
        "&""-,Regular""" & " - " &
Worksheets("Front").Range("A14") & " " & _
        Worksheets("Front").Range("B13") & " - " &
Worksheets("Front").Range("B14") & _
        Chr(10) & "&""-,Bold""" &
Worksheets("Front").Range("A11") & ""
        .RightHeader = "&12&D - &T "
        .LeftFooter = "&12&F"
        .CenterFooter = ""
        .RightFooter = "&12&P"
        .LeftMargin = Application.InchesToPoints(0.7)
        .RightMargin = Application.InchesToPoints(0.7)
        .TopMargin = Application.InchesToPoints(0.75)
        .BottomMargin = Application.InchesToPoints(0.75)
        .HeaderMargin = Application.InchesToPoints(0.3)
        .FooterMargin = Application.InchesToPoints(0.3)
        .PrintHeadings = False
        .PrintGridlines = False
        .PrintComments = xlPrintNoComments
        .CenterHorizontally = False
        .CenterVertically = False
        .Orientation = xlLandscape
        .PaperSize = xlPaper11x17
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = 100
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
    End With
    
    Range("A:A,F:F,H:AR").Select
    Selection.EntireColumn.Hidden = True
    ActiveWorkbook.Worksheets("Version").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Version").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "D1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Version").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    ActiveWorkbook.Worksheets("Version").AutoFilter.Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Version").AutoFilter.Sort.SortFields.Add Key:=Range( _
        "C1"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Version").AutoFilter.Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    With ActiveSheet.PageSetup
     .Zoom = False
     .FitToPagesWide = 1
     End With
    
    ActiveWindow.View = xlPageLayoutView
    Application.ScreenUpdating = True
    
    Application.Dialogs(xlDialogPrint).Show
    
End Sub


I have a somewhat large spreadsheet that I'd like to create some selective printing macros for.

I have a list of names on one sheet. I have 4 sheets for each of those names.
example:
anderson
anderson1
anderson2
anderson3

I'd like to create a macro that will print the main sheet and a specific numbered sheet for each person on the 'name' list. For example, I'd want to print "anderson" and "anderson1" and keep going with the next person etc.

I'd like to have a 2nd macro that prints "anderson" and "anderson2" and another for "anderson" and "anderson3" (and continuing for all the names on the list)

Is there a way I could get VB to do this?

Is there a command to bring up the select printer/options menu instead of just sending the data to the default printer? Here is the code I am using now:

Range("B12:BQ67").Select
ActiveSheet.PageSetup.PrintArea = "$B$12:$BQ$67"
ActiveWindow.SelectedSheets.PrintOut Copies:=1
ActiveWindow.SelectedSheets.PrintOut

Hello All
I have a list of data in col A (labelled mileage),as per attached workbook sheet1.I am using a select case macro as shown on sheet 2,to produce the required distance value in col B,eg, If A1 = 1M ,col B1 value = 8.I keep getting a compile error message.I am a complete novice at VBA,and would be gratful if anyone could make this macro work.I would also like to add furthur data to col A in future,would it be possible to add a loop to the macro,which will run until there is nothing in col A.
Many thanks for help given.

I am trying to put together a Select Case macro and am having problems with the Left function on the first line. If i don't have it there the macro works fine. Does anyone have any ideas?

Sub Macro1()

  CurrentSelectedCell = ActiveCell.Row

   Select Case Left(Cells(CurrentSelectedCell, 1), 6).Value & " " & Cells(CurrentSelectedCell, 2).Value
& " " & Cells(CurrentSelectedCell, 3).Value
        Case "542851 Loadsa AirMiles"
            Cells(CurrentSelectedCell, 4) = "FSA"
        Case "542852 Loadsa AirMiles"
            Cells(CurrentSelectedCell, 4) = "FSA2"
        Case "542851 Many Many 3rd Party Error"
            Cells(CurrentSelectedCell, 4) = "IPA"
            
        Case Else
            Cells(CurrentSelectedCell, 4) = ""
      End Select
End Sub
Many Thanks

Greetings,

I'm writing some code that analises data and I have been adviced that it is good practice to minimise the function .Select in macros, especially when i'm just copying and pasting stuff. (as .select will make the screen jump from one sheet to the other and back etc.

but how do I minimise .Select in this example:
Sheets("sheet1").Select
    Range("A13").Select
    Selection.Copy
    Sheets("sheet2").Select
    Range("F12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
Many thanks
Michiel

Hi all,

I have a selection change macro that continuously runs and will not stop unless I interrupt it. It starts when cells F10:H12 are changed but then keeps repeating the code! How do I modify it to only run once?
Any ideas would be appreciated.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Range("AT7") = 1 Then Exit Sub
    If Not Intersect(Range("F10:H12"), Target) Is Nothing Then
    Range("F12:H12").Copy
    Range("AT8:AV8").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AT9").Select
    Application.CutCopyMode = False
    Range("AT9").GoalSeek Goal:=0, ChangingCell:=Range("F12")
    Range("AT10").Copy
    Range("AT11").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("AT8:AV8").Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("F12:H12").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    End If
End Sub


I am writing a simple macro to allow some of the people in our company to
click on a toolbar button that will select our fax printer, send a fax, and
then reset the selected printer to the default.

What do I put after this:

Application.ActivePrinter =

Thanks,

Charlie

It looks like I am trying to do the opposite of what other people are posting
about. I have created a print macro that enablesl the user to select which
worksheets they want to print through a checkbox form. This works great, but
the worksheets automatically print on the user's default printe.r I would
like the print dialog box to let the user select a printer. I have written
code (with much help from another user forum) that enables me to bring up the
print dialog box, but then nothing prints.

This code worked – the sheets printed – but printed to the default printer:

If CheckBox1.Value = True Then Sheets(Array("Total Summary", "VZ Total
Summary")).PrintOut Copies:=1

This code brings up the printer dialog box, but doesn’t print anything:

If CheckBox1.Value = True Then Sheets(Array("Total Summary", "VZ Total
Summary")).Application.Dialogs(xlDialogPrint).Show

Thoughts?
thanks in advance!

I am new to macros and I have this code to select and print certain sheets;

Sub PrintRec()
Dim sht As Worksheet

For Each sht In ThisWorkbook.Worksheets
If sht.Range("A60").Value = "Print" Then
sht.PrintOut
End If
Next sht

End Sub

But I need to select a certain printer, which is not the default, how do I do this?

I am writing a simple macro to allow some of the people in our company to
click on a toolbar button that will select our fax printer, send a fax, and
then reset the selected printer to the default.

What do I put after this:

Application.ActivePrinter =

Thanks,

Charlie

Hi All

I have a network of 133 printers. I have been able to set up macros that will print to groups of 15-20 which is fine if I always want to print to those set groups.

What I would like to do is set up macros that ask me for a list of say 10 names, it would then look up the print queues of those names and print to those printers. That way I can print to different groups all the time!

Love your work in advance!

Regards

Dave

I am trying to write a macro that enables the user to:

Select a printer and then automatically print a hidden worksheet. The user will think s/he is printing the active sheet, but the hidden sheet is what should actually print out.

Here is the code I have thus far (I think I just need to add the prompt to select the printer):
Sheets("Print - Instructions").Visible = xlSheetVisible
Sheets("Print - Instructions").PrintOut
Sheets("Print - Instructions").Visible = xlSheetHidden

Note: the reason I have the worksheet that actually prints hidden is because if I print the active page, the pictures show up on top of the words in my text box (in case anyone was wondering).

I am trying to set up a print macro which automatically print to a
pre-selected printer (LABEL) instead of the default printer on the computer
(KYOCERA).

When complete, the macro must switch back to the default printer (KYOCERA).
This macro is used on several machines in the office and each machine has a
different Ne__ numbers for the the same printers.

ActiveSheet.PageSetup.PrintArea = "$P$8:$Y$13"
Application.ActivePrinter = "label on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"label on Ne05:", Collate:=True
Application.ActivePrinter = "KYOCERA FS3820N on Ne06:"

The Ne__ number appears to be the printer's installation order. When you
change printers (as we have needed to do), the Ne number changes rendering
the macro useless. I have had to change these things SEVERAL TIMES on
SEVERAL machines.

I want to find a way to define the KYOCERA and LABEL printers without using
those Ne__ numbers so the same macro will work throughout the office and if
one of the available printers is uninstalled, order changes, the macro will
continue to work. All the machines in question use Windows XP Pro on a
network. This problem did not appear when we were on windows 98!

How do I create the pause in the macro to stop at the printer menu command to
allow the user to selct the printer and number of copies

I have a macro that does some page formatting, prints the document, and then
reformats the page. Now I have a designated printer but would like to have
the ability to pick which printer I want to use then continue with the
reformating.

How do I do this??

How do I create the pause in the macro to stop at the printer menu command to
allow the user to selct the printer and number of copies

I am trying to set up a print macro which automatically print to a
pre-selected printer (LABEL) instead of the default printer on the computer
(KYOCERA).

When complete, the macro must switch back to the default printer (KYOCERA).
This macro is used on several machines in the office and each machine has a
different Ne__ numbers for the the same printers.

ActiveSheet.PageSetup.PrintArea = "$P$8:$Y$13"
Application.ActivePrinter = "label on Ne05:"
ActiveWindow.SelectedSheets.PrintOut Copies:=1, ActivePrinter:= _
"label on Ne05:", Collate:=True
Application.ActivePrinter = "KYOCERA FS3820N on Ne06:"

The Ne__ number appears to be the printer's installation order. When you
change printers (as we have needed to do), the Ne number changes rendering
the macro useless. I have had to change these things SEVERAL TIMES on
SEVERAL machines.

I want to find a way to define the KYOCERA and LABEL printers without using
those Ne__ numbers so the same macro will work throughout the office and if
one of the available printers is uninstalled, order changes, the macro will
continue to work. All the machines in question use Windows XP Pro on a
network. This problem did not appear when we were on windows 98!

I have set up a macro that will print dozens of pages after selecting the
colour printer in the office. This is done by specifying the exact printer in
the macro itself.

I want to send this to people in different locations (these people are not
very clued up on excel). I want to allow them to print but obviously the
printer will be different.

Is there a way I can bring up a drop down box that will allow them to select
their desired printer?

If so what would be the coding (I am only just getting into writing macros
myself).

Thanks in advance.

Hey gang. I have a macro that prints a summary sheet from Workbook A, then searches for the same customer in Workbook B and prints the detailed page, then moves back to Workbook A for the next customer where it does the same thing until all customers have been printed.

My problem. I need the user to be able to select a printer for the summary sheets and a different printer for the detailed sheets.

I have already used the:

	VB:
	
Application.Dialogs(xlDialogPrinterSetup).Show 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
code to allow the user to select a printer, but right now my macro prints all summary and detailed pages to the one selected printer. I want the user to be able to select a printer for printing Workbook A, then select a printer for Workbook B, then have the macro run and print summary page, detail page, summary page, detail page, so on and so forth without the user having to select the printer each time.

Below is roughly the macro I am currently using.

	VB:
	
 print_customer_copy() 
     'Select which printer to print to
    Application.Dialogs(xlDialogPrinterSetup).Show 
     
     'Move to Each cusotmers sheet
    Sheets("Customer 1").Select 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
    Part_II 
    Sheets("Customer 2").Select 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
    Part_II 
    Sheets("Customer 3").Select 
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
    Part_II 
End Sub 
 
Sub Part_II() 
    Metretek_ID = Left(Range("H1").Value, 8) 
     'Select Workbook B
    Windows("WorkBook B").Activate 
    WCount = Worksheets.Count 
     
    For i = 1 To WCount 
        If Worksheets(i).Visible Then 
            Worksheets(i).Select 
             
            If Left(ActiveSheet.Name, 6) = Metretek_ID Then 
                ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True 
                Goto Here 
            Else 
            End If 
             
        End If 
    Next i 
     
Here: 
    Windows("Workbook A").Activate 
End Sub 

If you like these VB formatting tags please consider sponsoring the author in support of injured Royal Marines
Thanks in advance for any and all help.

I would like to be able to record a Macro, but pause when it comes to print (so I can select printer and page setup) and then re-start.

But for some reason when i start to record, and want to pause it will not let me !!!!

Any ideas please

cheers

I have a 3 questions.

#1. How can I change range selection to where it will automatically go to the next row each time the macro is ran?

#2. I would like the macro ran from sheet 2.

Is there a way that I can do this or will I always have to start from sheet 3?

#3. Is there anything that I can do to shorten this code??


	VB:
	
 Macro16() 
     '
     ' Macro16 Macro
     ' Macro recorded 1/4/2006 by K. Bates
     '
     
    Range("AE5:BN5").Select 
    Selection.Copy 
    Application.CutCopyMode = False 
    Selection.Copy 
    Sheets("evening results").Select 
    ActiveWindow.SmallScroll Down:=-3 
    Range("C2").Select 
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _ 
    :=False, Transpose:=True 
    ActiveWindow.SmallScroll Down:=21 
End Sub 

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


Thank you for your help.
I'm trying to change Worksheet Tab Colors based on the name of the person in cell "A4". This macros changes the tab color on the first worksheet and then does nothing. I'll activate the code by using a button on each Worksheet.


	VB:
	
 
Sub OzGridtestColorTabSelectCase() 
    Dim sh As Worksheet 
    For Each sh In ActiveWorkbook.Worksheets 
         
         
        Select Case Range("A4").Text 
        Case "Agent 1" 
            ActiveSheet.Tab.ColorIndex = 34 
        Case "Agent 2" 
            ActiveSheet.Tab.ColorIndex = 17 
        Case "Agent 3" 
            ActiveSheet.Tab.ColorIndex = 36 
             
             ' In case there isn't a name or there is a different name in cell "A4", then turn worksheet tab RED.
        Case Else 
            ActiveSheet.Tab.ColorIndex = 3 
        End Select 
    Next sh 
End Sub 

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