I want to copy a range from a worksheet in one workbook to a worksheet in another workbook.

[Silaws]

I want to copy a range from a worksheet in one workbook to a worksheet in another workbook.


But my problem is that the copied formulas/references become relative to the old workbook instead absolute in the new workbook

I have a workbook with a sheet "Get Data " , and then I have a lot of sheets which retrieves data from the "Get Data " but my "Get Data " sheet does not work anymore, I have now created a new "Get Data " sheets in a new workbook and will now have all the sheets into the worbook and retrieve data from the new sheet without making any references again .

 

[MarcelBeug]

After you created the new "Get Data" sheet (and saved the workbook), and moved the other sheets to the new workbook, you can change the link from the old file to the new file: goto DATA, Edit Links, Change Source, navigate to and select your new file.

 

[Mackers]

Hi

One thing you might want to try is pressing Control+` (button next to 1 on a UK keyboard) to show all of the formulas in your worksheet, then copy the formulas into notepad++ (or notepad or word or outlook or anything), then copy it again and paste it into your new workbook. Just ensure that your formulas do not include the filename in them, so if you were referencing cell A1 it should just say =A1 rather than =filelocation'[filename.xlsx]Sheet1'A1 (or whatever the format is).

Hope that helps

Mackers