Copying range w/o non-print objects

[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?

[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.

[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?

[HansV]

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

[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.
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.

[Jan Karel Pieterse]

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

[HansV]

Thanks for the confirmation.

[dasadler]

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