Copy from WBK and paste to other open WBK?

[NeverHappyMike]

Hi

I've recorded myself copying a legend from my correct workbook (Mike1) into another one (OtherWorkbook) that I have opened. I'll be doing this process quite a few times so wanted to make a short macro to help me do this.

Unfortunately, the 'OtherWorkbook' name is very specific, and I can't figure out how to make it refer to any workbook that I have opened. There will only ever be two workbooks opened at a time.

Windows("Mike1.xlsx").Activate

    ActiveSheet.Shapes.Range(Array("xlamLegendGroup")).Select
    Selection.Copy
    Windows("OtherWorkbook.xlsx").Activate
    ActiveSheet.Shapes.Range(Array("xlamLegendGroup")).Select
    ActiveSheet.Paste

Any help greatly appreciated.

Michael.

 

[ThauThème]

Hi NeverHappyMike,

Try this code :

Sub Macro1()
Dim WO As Workbook 'Workbook Original
Dim SO As Worksheet 'Sheet Original
Dim WD As Workbook 'Workbook Destination
Dim SD As Worksheet 'Sheet Destination
Dim WB As Workbook

Set WO = ThisWorkbook
Set SO = WO.Sheets("Sheet1") 'adapt to tour case

For Each WB In Workbooks
    If WB.Name <> WO.Name Then Set WD = WB
Next WB
Set SD = WD.Sheets("Sheet1") 'adapt to tour case
SO.Activate
SO.Shapes.Range(Array("xlamLegendGroup")).Select
Selection.Copy
WD.Activate
SD.Shapes.Range(Array("xlamLegendGroup")).Select
SD.Paste
End Sub

 

[NeverHappyMike]

Hi

I tried it, and exchanged the sheet1 to simply (1), as that's all my workbook/s have. But it gave me an error message on the part:

SO.Shapes.Range(Array("xlamLegendGroup")).Select

Saying the item with the specified name wasn't found

I'm wondering if just from looking at it whether it's not trying to copy the legend from the destination workbook first.

Anyway, thanks for you help. I'll keep trying, but I think I may just end up copying and pasting manually.

Mike.

 

[ThauThème]

Hi NeverHappyMike,

Sorry for my poor english... When I read your code I found a little bit strange this part... What is xlamLegendGroup ? Why did you need Array ?

 

[NeverHappyMike]

Hi

I recorded my actions using the macro recorder, and that was the code it gave me as an output.

I'm copying a legend, which is actually just an image, from one set of panel charts that share this legend, to another workbook with the same panel chart layout and series but different data. The legend is the same but the data changes. Excel doesn't manage panels and legends very well, so I made a work around by creating an image as a legend.

Thanks for your help anyway. I understand a bit more about needing to inform Excel that there are two workbooks and how to go between them.

Mike.