Backup to a second sheet and then print

[basher]

Hello,

I am looking to build a macro to take the values from two cells of the sheet "Label" and paste them in the first empty row on the sheet "Labels Printed". I was able to use macro record so that when I hit ctrl+p it copies the 2 cells over and prints the label but it copies over the last row Which I would like to prevent.

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 4/19/2011 by LENOVO-StoreOps
'
' Keyboard Shortcut: Ctrl+p
'
    Range("D4:F4").Select
    Selection.Copy
    Sheets("Labels Printed").Select
    Range("A2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Sheets("Label").Select
    Range("A6:F6").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("Labels Printed").Select
    Range("B2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
    Range("A3").Select
    Sheets("Label").Select
    Application.CutCopyMode = False
    ActiveWindow.SelectedSheets.PrintOut Copies:=1, Collate:=True
End Sub

Thank You,
Matthew Brown

 

[VoG]

Try

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 4/19/2011 by LENOVO-StoreOps
'
' Keyboard Shortcut: Ctrl+p
'
    Range("D4:F4").Copy
    Sheets("Labels Printed").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Sheets("Label").Range("A6:F6").Copy
    Sheets("Labels Printed").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Sheets("Label").PrintOut
End Sub

 

[basher]

Try

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 4/19/2011 by LENOVO-StoreOps
'
' Keyboard Shortcut: Ctrl+p
'
    Range("D4:F4").Copy
    Sheets("Labels Printed").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Sheets("Label").Range("A6:F6").Copy
    Sheets("Labels Printed").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Sheets("Label").PrintOut
End Sub

Thanks! It works if I run it with VBE but the CTRL + P shorcut no longer works?

Also is there a way that it can automaticlly save after it backsup?

Thank you,
Matthew Brown

 

[VoG]

If you go to run the macro but instead of Run click Options you should be able to set the shortcut key combination.


To save try

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 4/19/2011 by LENOVO-StoreOps
'
' Keyboard Shortcut: Ctrl+p
'
    Range("D4:F4").Copy
    Sheets("Labels Printed").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Sheets("Label").Range("A6:F6").Copy
    Sheets("Labels Printed").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Sheets("Label").PrintOut
    ThisWorkbook.Save
End Sub

 

[basher]

Thanks that worked great! One more thing then I will dissapear. It worked with ctrl + P but I forgot.... what if someone clicks the printer Icon? Is there away to backup and auto save if they do that?

Thank you,
Matthew Brown

 

[VoG]

Yes: press ALT + F11 to open the Visual Basic Editor, double click on ThisWorkbook in the Project window and paste in

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel=True
Application.EnableEvents=False
Call Macro4
Application.EnableEvents=True
End Sub

 

[basher]

Hello VoG,

I did this and it printed but thats it. It did not back up or save. I hit alt + F11 Double clicked and pasted into ThisWorkbook:

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Cancel = True
Application.EnableEvents = False
Call Macro4
Application.EnableEvents = True
End Sub

Sub Macro4()
'
' Macro4 Macro
' Macro recorded 4/19/2011 by LENOVO-StoreOps
'
' Keyboard Shortcut: Ctrl+p
'
    Range("D4:F4").Copy
    Sheets("Labels Printed").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Sheets("Label").Range("A6:F6").Copy
    Sheets("Labels Printed").Range("B" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
    Range("D4").Value = Range("D4") + 1
    ThisWorkbook.Save
End Sub

 

[basher]

Got it! I guess I just had to close it and reopen it sorry!