Copy embedded chart with updated data request

[joecrabtree]

To all,

I have an embedded chart in worksheet 'test' in workbook testmacro.xls

I want the user to click on the chart, which will then copy the chart
(not as a picture) to a seperate workbook entitled testmacro2.xls into
worksheet 'test2' at range "B2".

How can I then make it so that any changes in the orginal embedded
chart in 'test' i.e. change in data range etc, are reflected in the
copied chart in testmacro2.xls?

Any help you could give me on this would be appreciated.

Regards

Joseph Crabtree

 

[Peter T]

Hi Joseph,

Right click your chart, Assign Macro...

Sub CopyMe()
Dim sBook As String, sSheet As String
Dim ans As Long
Dim ws As Worksheet
Dim wb As Workbook
Dim chtObj As ChartObject
Dim obj

sBook = "Book2" ' in my testing
'sBook = "testmacro.xls" ' uncomment
sSheet = "test2"

On Error Resume Next
Set obj = ActiveSheet.ChartObjects(Application.Caller)
If TypeName(obj) = "ChartObject" Then
Set wb = Workbooks("Book2")
If wb Is Nothing Then
MsgBox "the workbook " & sBook & " is not available"
Else
Set ws = wb.Worksheets(sSheet)

If ws Is Nothing Then
Set ws = wb.Worksheets.Add
ws.Name = "test2"
End If
wb.Activate
ws.Activate
For Each chtObj In ws.ChartObjects
If chtObj.TopLeftCell.Address = "$B$2" Then
ans = MsgBox("Delete exisiting chart", vbYesNoCancel)
If ans = vbYes Then
chtObj.Delete
ElseIf ans = vbCancel Then Exit Sub
End If
Exit For
End If
Next

ws.Range("B2").Select
obj.Chart.ChartArea.Copy
ActiveSheet.Paste
ws.Range("A1").Select

End If
End If

End Sub

Regards,
Peter T

 

[Peter T]

Typo -

change

Set wb = Workbooks("Book2")

to
Set wb = Workbooks(sBook)

Peter T