VBA With statement: How to copy grouped items?

filido

New Member
Joined
Jun 7, 2019
Messages
21
Hi, I have previously created a chart and copied it to another sheet. But now I have added a text box and grouped the text box and chart together (not in VBA). I want to be able to copy the group to another sheet as a picture. I believe instead of "Chart" it's "Shape" in VBA. But I don't know how to write the code. Now my code is like this. I have highlighted the parts that I believe should be changed. And I would like to keep the With...end with -statement because it seems working well.


Sub CreateCharts ()

Dim wsData As Worksheet
Dim wsPIA As Worksheet
Dim rngDst As Range
Dim chtCx As ChartObject
Dim loData As ListObject


'Get reference
Set wsData = ThisWorkbook.Worksheets("Data")
Set wsPIA = ThisWorkbook.Worksheets("PIA")
Set loData = wsData.ListObjects("Table1")

'Filter data
loData.Range.AutoFilter Field:=11, Criteria1:="France"


'CopyPaste boxplot chart
With ThisWorkbook.Worksheets("Sales")
CopyChart wsPIA.ChartObjects("Country"), .Range("B2"), "France_Country"
End With


End Sub



Private Sub CopyChart(Cht As ChartObject, rngDst As Range, ChtName As String)

Dim o As Object
rngDst.Worksheet.Activate
rngDst.Cells(1, 1).Select
Cht.CopyPicture
rngDst.Worksheet.Pictures.Paste.Name = ChtName

End Sub
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Grouped items become part of the Shapes collection. So you can refer to them using the Shapes object. So, for example, let's say that the grouped items are named "Group 1", CopyChart would be amended as follows...

Code:
Private Sub CopyChart(Shp As Shape, rngDst As Range, ChtName As String)

    Shp.CopyPicture
    With rngDst
        .Worksheet.Activate
        .Select
        .Worksheet.Pictures.Paste.Name = ChtName
    End With
    
End Sub

Then you would call the procedure this way...

Code:
'CopyPaste boxplot chart
With ThisWorkbook.Worksheets("Sales")
    CopyChart wsPIA.Shapes("Group 1"), .Range("B2"), "France_Country"
End With

Hope this helps!
 
Upvote 0
Grouped items become part of the Shapes collection. So you can refer to them using the Shapes object. So, for example, let's say that the grouped items are named "Group 1", CopyChart would be amended as follows...

Code:
Private Sub CopyChart(Shp As Shape, rngDst As Range, ChtName As String)

    Shp.CopyPicture
    With rngDst
        .Worksheet.Activate
        .Select
        .Worksheet.Pictures.Paste.Name = ChtName
    End With
    
End Sub

Then you would call the procedure this way...

Code:
'CopyPaste boxplot chart
With ThisWorkbook.Worksheets("Sales")
    CopyChart wsPIA.Shapes("Group 1"), .Range("B2"), "France_Country"
End With

Hope this helps!

Hi, I tried this code. It gives me Error: Type mismatch. I wonder is it suppose to be "CopyChart wsPIA.Shapes...." or "Copy___" something else than Chart? Or is that Private Sub suppose to be before the CopyPaste part? I do have the regular CopyChart with ChartObjects for other charts too in the code, could this affect the procedure? Since the rngDst As Range & ChtName As String is included in two different Private Subs.
 
Upvote 0
CopyChart can be re-written without having to activate and select...

Code:
Private Sub CopyChart(Shp As Shape, rngDst As Range, ChtName As String)

    Shp.CopyPicture
    With rngDst.Worksheet
        .Paste rngDst
        .Shapes(.Shapes.Count).Name = ChtName
    End With
    
End Sub

If you're still getting an error, please confirm which error you're getting, and which line causes the error. If you've made changes to the code, please post the exact code that you're using.
 
Upvote 0
I tried to re-write the CopyChart without Activate and Select and still, I get "Error 13: Type mismatch" and the line is CopyChart wsPIA.Shapes("Group 1"), .Range("B2"), "France_Country".
 
Upvote 0
Hi @Domenic

I solved the issue. I had CopyChart wsPIA.Shapes("Group 1"), .Range("B2"), "France_Country". and Private Sub CopyShape so that's why Excel gave the error message. I changed the first line with CopyShape wsPIA.... Now it works like a charm. Thank you so much for your help!! :)
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top