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

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

"joecrabtree" <(E-Mail Removed)> wrote in message
news:fdf0b4d3-b217-4769-8e7c-(E-Mail Removed)...
> 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

Typo -

change

> Set wb = Workbooks("Book2")

to
Set wb = Workbooks(sBook)

Peter T

"Peter T" <peter_t@discussions> wrote in message
news:(E-Mail Removed)...
> 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
>
> "joecrabtree" <(E-Mail Removed)> wrote in message
> news:fdf0b4d3-b217-4769-8e7c-(E-Mail Removed)...
> > 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
>
>