I have two similar workbooks, 'A' and 'B'. The summary sheet in 'A' has formulas that reference back to the data sheet in 'A'. When I copy the summary sheet from 'A', into 'B', I would like the formulas to remain the same, but so they reference to the data sheet in 'B' insteadof 'A'. However, excel automatically keeps the same reference cells, so my new summary sheet in 'B' is still looking at the data sheet in 'A'.

Formula in 'A' summary; =data!A1
Formula in 'B' summary (actual); =[workbookA]data!A1
Formula in 'B' summary (desired); =data!A1


Is there anyway I can copy my sheet between workbooks and get my desired results without writing a macro or having to use the replace function each time?