Copy charts from one sheet to another sheet with the same name as the chart itself

[Johan W]

Hi
I have a problem that i hope someone can help me with.

I got a excelfile containing 50 sheets , named "15HP", "03AE" etc. , and one sheet that contains charts,
The sheets names is the same as the names of the charts.

So, what i want to do is to get the charts with the same name as my worksheets, copyed to the worksheet
Instead of coding with hard values, like my code below, i want to make my macro more dynamic.
With dynamic, i mean that the macro should loop thru each sheet and copy the right chart.



Regards from Sweden

Johan

Sub test()
'

Sheets("charts").Select
ActiveSheet.ChartObjects("15HP").Activate
Selection.Copy
Sheets("15HP").Select
Range("H10:K33").Select
ActiveSheet.Paste
ActiveSheet.Pictures.Paste.Select
Range("A1").Select


Sheets("charts").Select
ActiveSheet.ChartObjects("03AE").Activate
Selection.Copy
Sheets("03AE").Select
Range("H10:K33").Select
ActiveSheet.Paste
ActiveSheet.Pictures.Paste.Select
Range("A1").Select

and so on....
.....


End Sub

 

[Domenic]

The following macro will loop through each chartobject within the worksheet called "charts", and copy/paste each chart into their respective worksheets. Note that it assumes that all of the relevant worksheets are contained in the workbook running the code. If you want the macro to run based on the active workbook instead, replace...

Set sourceWorkbook = ThisWorkbook

with

Set sourceWorkbook = ActiveWorkbook

Here's the macro...

Sub test()

    Dim sourceWorkbook As Workbook
    Set sourceWorkbook = ThisWorkbook

    Dim sourceWorksheet As Worksheet
    Set sourceWorksheet = sourceWorkbook.Worksheets("charts")
    
    Dim currentChartObject As ChartObject
    For Each currentChartObject In sourceWorksheet.ChartObjects
        currentChartObject.Copy
        With sourceWorkbook.Worksheets(currentChartObject.Name)
            .Paste .Range("H10")
        End With
    Next currentChartObject

End Sub

Hope this helps!

 

[Johan W]

Thank you Domenic. Perfect!

Johan