Hello!
I have a macro to move some data to a different area on the sheet, print it and then move the data back. The purpose is because it looks better on a screen with the data in one spot and prints better in the other spot.
The macro works great - however since I give the user the option to choose how many copies they want printed, I end up getting an error if the user chooses "cancel" or enters 0 instead of inputting a value. Even if I "end macro" at this point, the data has already been moved to the new spot and is not moved back to the original spot I want it to stay displayed.
Can someone help me determine what I can add into the code that would ensure the data either doesn't get moved or gets moved back prior to exiting the sub so that when the user chooses "Cancel" or enters "0", it is as if nothing happened?
<p>
Sub PrintData()
'
' PrintData Macro
'
'
Dim iCopies As String
iCopies = Application.InputBox("How many copies do you want to print?", Type:=2)
If iCopies <> vbNullString Then
Range("P1:R11").Select
Selection.Cut Destination:=Range("A34:E45")
Range("A34:E45").Select
ActiveWindow.SelectedSheets.PrintOut Copies:=iCopies, ActivePrinter:="\homs9Secure_CLR_Xerox", Collate:=True, _
IgnorePrintAreas:=False
Selection.Cut Destination:=Range("P1:R11")
Range("A1").Select
ActiveWorkbook.Save
Else
Exit Sub
End If
End Sub
</p>
Hi
Give this a try. No need to cut and paste. Just copy the data to the required range, print and then delete the range.
Sub PrintData()
Dim iCopies As Long
iCopies = Application.InputBox("How many copies do you want to print?", Type:=1)
If iCopies <> 0 Then
'Copy data
Range("P1:R11").Copy Range("A34:E45")
'Set the print area
ActiveSheet.PageSetup.PrintArea = "A34:E45"
'Print
ActiveWindow.SelectedSheets.PrintOut Copies:=iCopies, ActivePrinter:="\homs9Secure_CLR_Xerox", Collate:=True, _
IgnorePrintAreas:=False
'Clear the copied data
Range("A34:E45").ClearContents
Range("A1").Select
ActiveWorkbook.Save
End If
End Sub
Hope this helps.
Sunny
Thank you - one follow up question - what command can I use to only paste values & formatting. Currently it is pasting the formulas which I do not want it to do.
'Copy data
Range("P1:R11").Copy Range("A34:E45")
Try changing it to:
'Copy data
Range("P1:R11").Copy
With Range("A34:E45")
.PasteSpecial xlPasteValues
.PasteSpecial xlPasteFormats
End With
