PDA

View Full Version : Solved: Print different sheets - Print area does not clear - Print_area already exists



sich48
12-28-2010, 05:17 AM
I have a workbook with several sheets and a userform with a commandbutton to show a OWC spreadsheet and another to print a selected area of this spreadsheet. On a regular basis (not always!) a message pops up:
“A form or sheets you want to move contains the name “Print_Area” which already exists on the destination worksheet. Do you want to use this version of the name? To use the name as defined in the destination sheet, click Yes. To create a new name for this form or sheet, click No.” The Yes button shows as the default one.
The error routine of course overrules this message but it nevertheless is visible for a short moment. Without the error routine in place, the code just stops and waits for the user’s response.
I’ve been searching desperately the internet to find similar issues. Is there something wrong with my code and how can I avoid this message appearing??

Here is my code:




Private Sub cmdPrint_Click()
cmdShowSpread_Click
End Sub


Private Sub cmdShowSpread_Click()
Dim ws As Worksheet

‘ here is the code to determine sline, eline
‘ ………and to Set ws as one of 4 worksheets [i.g. Set ws = “Worksheets(“November”)]
‘ ………
'Printing titlerows + selected area
Application.ScreenUpdating = False
On Error GoTo PrtError

With ws
.PageSetup.PrintArea = ""
.Range("A" & CStr(sline) & ":G" & CStr(eline)).Name = "Print_Area"
.PageSetup.PrintTitleRows = ""
.PageSetup.PrintTitleRows = .Range("$3:$5).Address
.PrintOut Copies:=1
End With
Application.ScreenUpdating = True
Exit Sub

PrtError:
MsgBox "Printer not found. Please check.", vbExclamation
Application.StatusBar = False
End Sub

austenr
12-28-2010, 08:34 AM
Everytime you print Excel creates a defined name for the print range.
When you are copying from one sheet to another the named ranges go along. In this case it means you would have two identical name in the workbook.

You can probably eliminate this message by 1. Selecting a range on the
source sheet that you want to copy, not the whole sheet, 2. Copy, 3. Move to the target sheet and Paste.

sich48
12-28-2010, 04:41 PM
Solved: found out that I needed to clear ALL namedrages, not only the PrintArea. PrintTitleRows also creates a named range so looping through each name and deleting them soved the problem.