'Paste Special' problem with macro

**katmc** · 11-14-2008, 03:47 PM

I have a macro that copies the main report sheet in my workbook. The point of the macro is to get a copy of the report data without allowing anyone to see the formulas/data behind it - when it pastes, it pastes only the values in the tables and then copies the charts, deletes the live ones, and pastes only the images back on the sheet. The macro works on my computer and my partner's computer, but not on anyone else's. It stops at this point:

 ActiveSheet.PasteSpecial Format:="Picture (PNG)", Link:=False, _
        DisplayAsIcon:=False

and either gives them a runtime error or Excel stops working entirely (interestingly, this differs based on what type of computer they use).

Why does this happen? Does it have something to do with Excel settings?

**royUK** · 11-14-2008, 03:53 PM

Please read the Forum Rules & use Code Tags in future.

**royUK** · 11-14-2008, 03:55 PM

Why don't you just use sheet protection, you can choose to hide formulas

**katmc** · 11-14-2008, 04:08 PM

Apologies, I'll use code tags in the future.

We don't use the hide feature for the reports page because some recipients need to be able to see formulas in case they have questions about the data, which is included on other sheets. The macro also only copies a certain part of the page, which is a convenience for recipients. So there is no reason the VBA code shouldn't be working?

**Leith Ross** · 11-14-2008, 04:42 PM

Hello katmc,

Most likely the PNG format is not part of the other computers basic clipboard formats. Try using standard formats like bitmap, metafile, or enhanced metafile.

Sincerely,
Leith Ross