Copy & paste sheets to other workbook issues

[copperwasher]

Hi there Excel Guru's

I have more than 50 workbooks which all have a summary sheet (As sheet 1).
The workbooks contain sheets of data (Which is stored in the same format / layout)

Summary sheet (Sheet1)

1​	​		2
Value	No. Trades	P/L [R]	Setting	Value	No. Trades	P/L [R]	Setting
=AUDCAD!$B1​	=AUDCAD!$C1​	=AUDCAD!$D1​	=AUDCAD!$P1​	=AUDCHF!$B1​	=AUDCHF!$C1​	=AUDCHF!$D1​	=AUDCHF!$P1​
=AUDCAD!$B2​	=AUDCAD!$C2​	=AUDCAD!$D2​	=AUDCAD!$P2​	=AUDCHF!$B2​	=AUDCHF!$C2​	=AUDCHF!$D2​	=AUDCHF!$P2​
=AUDCAD!$B3​	=AUDCAD!$C3​	=AUDCAD!$D3​	=AUDCAD!$P3​	=AUDCHF!$B3​	=AUDCHF!$C3​	=AUDCHF!$D3​	=AUDCHF!$P3​
=AUDCAD!$B4​	=AUDCAD!$C4​	=AUDCAD!$D4​	=AUDCAD!$P4​	=AUDCHF!$B4​	=AUDCHF!$C4​	=AUDCHF!$D4​	=AUDCHF!$P4​
=AUDCAD!$B5​	=AUDCAD!$C5​	=AUDCAD!$D5​	=AUDCAD!$P5​	=AUDCHF!$B5​	=AUDCHF!$C5​	=AUDCHF!$D5​	=AUDCHF!$P5​
=AUDCAD!$B6​	=AUDCAD!$C6​	=AUDCAD!$D6​	=AUDCAD!$P6​	=AUDCHF!$B6​	=AUDCHF!$C6​	=AUDCHF!$D6​	=AUDCHF!$P6​
=AUDCAD!$B7​	=AUDCAD!$C7​	=AUDCAD!$D7​	=AUDCAD!$P7​	=AUDCHF!$B7​	=AUDCHF!$C7​	=AUDCHF!$D7​	=AUDCHF!$P7​
=AUDCAD!$B8​	=AUDCAD!$C8​	=AUDCAD!$D8​	=AUDCAD!$P8​	=AUDCHF!$B8​	=AUDCHF!$C8​	=AUDCHF!$D8​	=AUDCHF!$P8​
=AUDCAD!$B9​	=AUDCAD!$C9​	=AUDCAD!$D9​	=AUDCAD!$P9​	=AUDCHF!$B9​	=AUDCHF!$C9​	=AUDCHF!$D9​	=AUDCHF!$P9​
=AUDCAD!$B10​	=AUDCAD!$C10​	=AUDCAD!$D10​	=AUDCAD!$P10​	=AUDCHF!$B10​	=AUDCHF!$C10​	=AUDCHF!$D10​	=AUDCHF!$P10​
=AUDCAD!$B11​	=AUDCAD!$C11​	=AUDCAD!$D11​	=AUDCAD!$P11​	=AUDCHF!$B11​	=AUDCHF!$C11​	=AUDCHF!$D11​	=AUDCHF!$P11​
=AUDCAD!$B12​	=AUDCAD!$C12​	=AUDCAD!$D12​	=AUDCAD!$P12​	=AUDCHF!$B12​	=AUDCHF!$C12​	=AUDCHF!$D12​	=AUDCHF!$P12​

As I progress, I need to amend the summary sheet of all the 50 workbooks,
But when I copy the latest amended summary sheet to another workbook, it retains the link to the previous workbook.

1​
Value	No. Trades	P/L [R]	Setting
='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$B1​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$C1​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$D1​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$P1​
='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$B2​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$C2​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$D2​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$P2​
='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$B3​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$C3​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$D3​	='[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]AUDCAD'!$P3​

This is not ideal.

I need to paste the amended copy into another workbooks so it copies the data from its sheets and not from the other workbook sheets.

I have looked at paste link, copy & paste formats etc... still I am without a method...

Any guidance is much appreciated.
My thanks
Copperwasher

 

[jkpieterse]

Some options:
- After the paste, do a search and replace, replace "'[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]" with nothing

or

- save the file, then open the Data, Edit Links dialog, click the link in question and click Change Source, point to the file you just saved.

 

[copperwasher]

Some options:
- After the paste, do a search and replace, replace "'[10. ADM DI^MDI- Batch1 1.1.1.100TradesAll OptiResult v5.1.xlsm]" with nothing

or

- save the file, then open the Data, Edit Links dialog, click the link in question and click Change Source, point to the file you just saved.

Hi there jkpieterse

Thanks for the input...

I sure hope there is an easier way of doing this... excel wise. The number of sheets is going to increase

(In the meantime I have set the JitBit command tool to do the copy and paste option).

My regards
Copperwasher