Excel VBA Help in relation to selection of charts

VBABeginner1996

New Member
Joined
Sep 14, 2018
Messages
5
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.

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

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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.
 
Upvote 0
this code snippet might be helpful, I used it to make certain changes to selected charts
Code:
    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
 
Upvote 0
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.
 
Upvote 0
Try:
Code:
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
 
Upvote 0
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.

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

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:

Code:
        Worksheets("Sheet2").Paste
        With Worksheets("Sheet2").Shapes(Worksheets("Sheet2").Shapes.Count)
          .Top = something
          .Left = something
        End With
 
Last edited:
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

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