separating linked wrkbooks

[robzrob]

Workbook 2 picks up data from Workbook 1. I type data into 1. I want
to be able to save 2 with all its data from 1, then go back to 1,
clear it, but without my saved 2 being cleared, then enter more data
into 1 and save it on a new 2.

 

[MJJ]

If you don't want to add to workbook 2 later, then once you have the data you
require in workbook 2, highlight the sheet (or the area of data containing
the formulae), copy it and then 'paste special' 'values'. This will remove
the links and just give you numbers in the cells. Save it with a new name
(January or whatever), dont save workbook 2 and you should still have
workbook 2 with the original formulae for next time you run it.

 

[robzrob]

If you don't want to add to workbook 2 later, then once you have the data you
require in workbook 2, highlight the sheet (or the area of data containing
the formulae), copy it and then 'paste special' 'values'.  This will remove
the links and just give you numbers in the cells.  Save it with a new name
(January or whatever), dont save workbook 2 and you should still have
workbook 2 with the original formulae for next time you run it.  





- Show quoted text -

Thanks. Why didn't I think of that?

 

[robzrob]

Thanks.  Why didn't I think of that?  - Hide quoted text -

- Show quoted text -

Actually I spoke a bit too soon. It won't paste formats if you pick
paste values. To get over that, I've set up a sheet-to-be-pasted-to
with identical formats to the one I'm copying from, but now it won't
paste values into that at all, it gives:'This operation requires the
merged cells to be identically sized'. But they are. I'm confused.

 

[MJJ]

Rob
Now I think its beyond me too I have trouble with the identiclly sized
merged cells thing - I tried - someone else needs to take this one on now.
Sorry and good luck.
Margaret

 

[JLatham]

One other option: In workbook2 choose Edit | Links and then choose the
[Break Links] option. That should convert all links to their current value,
much the same as Edit / Paste Special w/Values. But affecting only linked
values.

But from reading some of your exchange with MJJ I'm thinking that maybe
you're still wanting to retain a sheet with the links, while keeping the
'carved in stone' version of the data on another sheet. So, think about this:
Start by making a copy of the worksheet with the linked values on it in
Workbook2.
Choose that copy of the sheet and run the macro that I provide below. It
will take all formulas, including those involving links and convert them to
their resulting values on the sheet selected when you run the macro.

To put the code into your Workbook2: open that file, press [Alt]+[F11] to
open the VB Editor. Choose Insert | Module and copy and paste the code below
into the module that's presented to you. Close the VB Editor. To use it,
just use Tools | Macro | Macros and select the CarveInStone macro frm the
list to [Run].

Sub CarveInStone()
Dim myUsedRange As Range

Set myUsedRange = ActiveSheet.UsedRange
myUsedRange.Formula = myUsedRange.Value
End Sub