Odyrus
05-21-2014, 06:24 AM
Hello,
I have a few bits of code that work well independently. My objective is to put them together into one macro and this is where I'm having trouble.
My goal upon selecting the print button is for the user to input the corresponding number to print that list, be given a choice of printers, and then print. Pretty straight forward I think. I'm having difficulty nesting the steps.
Appreciate any help! Cheers!
This bit of code for Printer Selection works great:
'select printer
Dim x As Boolean
x = Application.Dialogs(xlDialogPrinterSetup).Show
If x = True Then
'Print workbook
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
Else
x = MsgBox(" Print Cancelled", vbOKOnly, "Cancel Print")
End
End If
This bit of code "mostly" works. It will print the team but it's randomly pasting a name into the current worksheet. I haven't figured that out yet.
Application.ScreenUpdating = False
Dim MyValue As Long, Response As Long
MyValue = Application.InputBox( _
Prompt:="To Print your team enter the correct number" & Chr(13) & "1 = David, 2 = Jason" & Chr(13) & "3 = Monte, 4 = Natalie" & Chr(13) & "5 = Oscar, 6 = Joe" & Chr(13) & "7 = Erica", _
Title:="Print Your Team", _
Type:=1)
If (MyValue = 1) Then
For Each MyCell In Range("David")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 2) Then
For Each MyCell In Range("Jason")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 3) Then
For Each MyCell In Range("Monte")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 4) Then
For Each MyCell In Range("Natalie")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 5) Then
For Each MyCell In Range("Oscar")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 6) Then
For Each MyCell In Range("Joe")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 7) Then
For Each MyCell In Range("Erica")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
Application.ScreenUpdating = True
Again, thanks for the extra eyes! Cheers!!
I have a few bits of code that work well independently. My objective is to put them together into one macro and this is where I'm having trouble.
My goal upon selecting the print button is for the user to input the corresponding number to print that list, be given a choice of printers, and then print. Pretty straight forward I think. I'm having difficulty nesting the steps.
Appreciate any help! Cheers!
This bit of code for Printer Selection works great:
'select printer
Dim x As Boolean
x = Application.Dialogs(xlDialogPrinterSetup).Show
If x = True Then
'Print workbook
ActiveWorkbook.PrintOut Copies:=1, Collate:=True
Else
x = MsgBox(" Print Cancelled", vbOKOnly, "Cancel Print")
End
End If
This bit of code "mostly" works. It will print the team but it's randomly pasting a name into the current worksheet. I haven't figured that out yet.
Application.ScreenUpdating = False
Dim MyValue As Long, Response As Long
MyValue = Application.InputBox( _
Prompt:="To Print your team enter the correct number" & Chr(13) & "1 = David, 2 = Jason" & Chr(13) & "3 = Monte, 4 = Natalie" & Chr(13) & "5 = Oscar, 6 = Joe" & Chr(13) & "7 = Erica", _
Title:="Print Your Team", _
Type:=1)
If (MyValue = 1) Then
For Each MyCell In Range("David")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 2) Then
For Each MyCell In Range("Jason")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 3) Then
For Each MyCell In Range("Monte")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 4) Then
For Each MyCell In Range("Natalie")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 5) Then
For Each MyCell In Range("Oscar")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 6) Then
For Each MyCell In Range("Joe")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
If (MyValue = 7) Then
For Each MyCell In Range("Erica")
ActiveCell.FormulaR1C1 = MyCell.Formula
Range("B5").Select
ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True
Next
End If
Application.ScreenUpdating = True
Again, thanks for the extra eyes! Cheers!!