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
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,007
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!
 

filido

New Member
Joined
Jun 7, 2019
Messages
21
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.
 

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,007
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.
 

filido

New Member
Joined
Jun 7, 2019
Messages
21
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

New Member
Joined
Jun 7, 2019
Messages
21
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!! :)
 

Forum statistics

Threads
1,081,981
Messages
5,362,535
Members
400,679
Latest member
alecalec202

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top