Copying range w/o non-print objects

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Copying range w/o non-print objects

Post by dasadler »

I have an Excel 2007 worksheet that includes a couple of instructional boxes that both have the 'do not print' property. Yet, when I use the following macro (which was originally done in Excel 2003) the instructional boxes show up.

Code: Select all

Sub Copy_Detail()
    Range("A1:R19").Select
    Selection.CopyPicture Appearance:=xlPrinter, Format:=xlPicture
End Sub
How should I modify this macro so it excludes the no print boxes?
Don

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copying range w/o non-print objects

Post by HansV »

Sadly, the Copy Picture... > As Shown When Printed command ignores the print setting of shapes.
You could loop through all shapes on the sheet, hide the ones that are set not to print, then copy the picture, and finally unhide the shapes again:

Code: Select all

Sub Copy_Detail()
  Dim i As Long
  Dim arr
  ReDim arr(1 To ActiveSheet.Shapes.Count)
  For i = 1 To ActiveSheet.Shapes.Count
    arr(i) = ActiveSheet.Shapes(i).ControlFormat.PrintObject
    If arr(i) = False Then
      ActiveSheet.Shapes(i).Visible = False
    End If
  Next i
  Range("A1:R19").CopyPicture Appearance:=xlPrinter
  For i = 1 To ActiveSheet.Shapes.Count
    If arr(i) = False Then
      ActiveSheet.Shapes(i).Visible = True
    End If
  Next i
End Sub
Note that the range doesn't have to be selected to be copied.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Copying range w/o non-print objects

Post by dasadler »

Thanks Hans. Maybe I don't remember correctly but it seems this worked fine for me back in 2005/6. Would it have worked in Excel 2003?
Don

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copying range w/o non-print objects

Post by HansV »

I can't remember. I'll take a look at it when I'm on a PC with Excel 2003 (tomorrow).
Best wishes,
Hans

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copying range w/o non-print objects

Post by HansV »

I have now tested it in Excel 2003. Copy Picture... > As Shown When Printed does respect the print setting of shapes there!

So this is a new bug introduced in Excel 2007. :hairout:
I can't find any mention of it, so chances are it hasn't been reported, and it will probably still be present in Excel 2010.
Best wishes,
Hans

User avatar
Jan Karel Pieterse
Microsoft MVP
Posts: 656
Joined: 24 Jan 2010, 17:51
Status: Microsoft MVP
Location: Weert, The Netherlands

Re: Copying range w/o non-print objects

Post by Jan Karel Pieterse »

Hans, Unfortunately, Excel 2010 also insists on showing the shape.
I'll issue a bug.
Regards,

Jan Karel Pieterse
Excel MVP jkp-ads.com

User avatar
HansV
Administrator
Posts: 79030
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Copying range w/o non-print objects

Post by HansV »

Thanks for the confirmation.
Best wishes,
Hans

dasadler
5StarLounger
Posts: 889
Joined: 25 Jan 2010, 16:26
Location: Garden Grove, CA 92844 USA

Re: Copying range w/o non-print objects

Post by dasadler »

Good to know that I remembered correctly. Thanks for checking it out and letting me know.
Don