CODEWITHSUNDEEP
vbaexcel
Amasian21
Amasian21

Reputation: 55

Copy images only to a new workbook

I have created code correctly which will copy a sheet from the current workbook(main) and paste it into a new workbook(wbnew). The paste will do values only to prevent formulas from transferring. I'd like to do the same with graphs I have on a sheet called "histograms". You can copy a graph, and paste it as a picture. How can I do this with a sheet with multiple graphs on it?

        Main.Worksheets("MyData").Copy Before:=wbnew.Sheets(1)
With wbnew.Sheets(1).UsedRange
    .value = .value 'converts formulas to values
End With


        Main.Worksheets("Histograms").Copy Before:=wbnew.Sheets(1)
With wbnew.Sheets(1).UsedRange
    .Pictures = .Pictures 'converts graphs to pictures
End With

Upvotes: 0

Views: 75

Answers (1)

GSD
GSD

Reputation: 1252

You need to use the chartobjects collection, loop through each chart on your data sheet and paste as picture to your pictures worksheet:

Sub CopyChartsToPics()
Dim oChart As ChartObject

    ActiveWorkbook.Worksheets("charts").Activate

    For Each oChart In ActiveSheet.ChartObjects
        oChart.Select
        oChart.Copy
        ActiveWorkbook.Worksheets("pictures").Select
        ActiveWorkbook.Worksheets("pictures").Pictures.Paste
        ActiveWorkbook.Worksheets("charts").Activate
    Next
End Sub

Upvotes: 1

Related Questions