Excel VBA Help in relation to selection of charts

[VBABeginner1996]

Hi everyone,

I have been learning VBA over the past couple of months and have come up with a problem. I have been trying to write code that will paste Selected charts in one sheet and paste them to another. I have multiple charts on one sheet but only want to paste a few of them to another. To do this i have written the code below.

Sub copyandpaste()

Dim i As Integer


For i = 1 To Selection.Count


    Selection(i).Copy
    ActiveWorkbook.Worksheets("Sheet2").Paste
    
Next i


End Sub

Whilst the number of charts pasted is always correct no matter which charts i select the ones that are pasted do not depend on the selection but the order of the chart creation. So, if I had 3 charts and selected charts 2 and 3 and ran the code, charts 1 and 2 would be pasted instead. If anyone help it would be much appreciated.

 

[silentwolf]

Hi,
maybe this link will get you on the way
https://stackoverflow.com/questions/45190863/copy-charts-from-one-sheet-to-another

 

[VBABeginner1996]

Hi, thanks for the response, however, I am dealing with just selected charts instead of all charts.

 

[Fluff]

Cross posted https://www.excelforum.com/excel-pr...-help-in-relation-to-selection-of-charts.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

 

[VBABeginner1996]

Didnt know, thanks for the heads up though.

 

[Repush]

this code snippet might be helpful, I used it to make certain changes to selected charts

    Dim objChart As Chart 'Chart to format
    Dim varCycle As Variant 'Cycler for chartobjects and other objects in selection
    'Check selection
    If TypeName(Selection) = "ChartObjects" Or TypeName(Selection) = "DrawingObjects" Then
        For Each varCycle In Selection
            If TypeName(varCycle) = "ChartObject" Then
                Select Case Action
                    ' do stuff
                End Select
            End If
        Next varCycle

 

[VBABeginner1996]

Thanks for the response. But I am more interested in why when I have 3 charts and selected only 2 charts and run the code, that the First 2 created charts are pasted instead of the 2 selected charts.

 

[Kaper]

Try:

Sub Test2()
Dim Item As Object
For Each Item In Selection
  If TypeName(Item) = "ChartObject" Then
    Item.Copy
    ActiveWorkbook.Worksheets("Sheet2").Paste
  Else
    MsgBox "Not Chart selected!"
  End If
Next Item
End Sub

 

[Jon Peltier]

If only one chart is selected, it is the active chart. If more than one charts are selected, there is no active chart, and the type of the selection is "DrawingObjects".

So copy either the active chart (specifically its ChartArea) or the selection, then paste onto Sheet2. The active chart is pasted with its top left corner on the active cell of Sheet2. The selected charts are pasted en masse, with the top left corner or the rectangle that bounds the selected charts on the active cell of Sheet2.

Sub CopySelectedChartsToOtherSheetEnMasse()
  If Not ActiveChart Is Nothing Then
    ActiveChart.ChartArea.Copy
    Worksheets("Sheet2").Paste
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    Selection.Copy
    Worksheets("Sheet2").Paste
  End If
End Sub

If for some reason you need to loop through the selected items, you would use this code.

Sub CopySelectedChartsToOtherSheetOneByOne()
  Dim sh As Shape
  If Not ActiveChart Is Nothing Then
    ActiveChart.ChartArea.Copy
    Worksheets("Sheet2").Paste
  ElseIf TypeName(Selection) = "DrawingObjects" Then
    For Each sh In Selection.ShapeRange
      If sh.HasChart Then
        sh.Chart.ChartArea.Copy
        Worksheets("Sheet2").Paste
      End If
    Next
  End If
End Sub

Positioning of pasted charts is erratic, but after the paste you could position the shapes, something like this:

        Worksheets("Sheet2").Paste
        With Worksheets("Sheet2").Shapes(Worksheets("Sheet2").Shapes.Count)
          .Top = something
          .Left = something
        End With