VBA With statement: How to copy grouped items?

[filido]

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

 

[Domenic]

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

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

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

Hope this helps!

 

[filido]

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

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

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

 

[Domenic]

On which line does the error occur?

 

[Domenic]

CopyChart can be re-written without having to activate and select...

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.

 

[filido]

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

 

[filido]

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!!