CODEWITHSUNDEEP
vbaexcelchartsbitmapcopy-paste
JumboFive
JumboFive

Reputation: 45

How to use pastepecial to paste a chart as bitmap to another sheet in vba

Is there a way to use the pastspecial method to paste a copyied chart as a bitmap to another worksheet. Currently this is my syntax-

PasteSheet.PasteSpecial (Format:="Bitmap", Link:=False, DisplayAsIcon:=False)

Where PasteSheet is the other worksheet i want to paste to. Currently with this code, it is only pasting in the active sheet. Do i have to use select to copy then select the page i want to paste to, then change back to the sheet I copied from? I hope not as I have a lot of sheets haha.

Thank you

Edit: I have found out that if I Copy the chart as a shape rather than a chartobject I can use the pasteSpecial method to paste to another sheet. That being said it now pastes charts into one another creating one mega chart haha.

GraphSheet.Shapes(chtName).Copy 
PasteSheet.PasteSpecial Format:="Microsoft Office Drawing Object", Link:=False , _
     DisplayAsIcon:=False

Upvotes: 1

Views: 23606

Answers (2)

Tim Williams
Tim Williams

Reputation: 166306

This will work without needing to activate/select Sheet2:

Sheet1.ChartObjects(1).Chart.CopyPicture
Sheet2.Paste

Upvotes: 2

Floris
Floris

Reputation: 46375

Do i have to use select to copy then select the page i want to paste to, then change back to the sheet I copied from?

Yes - the sheet you paste into must be active. Use Sheets("mytargetname").Select - just using Activate isn't enough...

If you set 

Application.ScreenUpdating = False

your screen won't flash while you do this...

Upvotes: 1

Related Questions