copying from one sheet to another

[Chrissirhc]

Hi,

I want to copy formulae from one sheet to another, but I it is including links to another sheet. How do I stop that? I.e. I just want the same formala without the links...

Thanks,

Chris

 

[xlbo]

Edit Paste Special>Formulas ???

Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Chrissirhc]

Unforuntately that includes the links to the other sheet. Well basically its because the formulae contain references to ranges which don't exist in both sheets... Any other possibilities without writing a macro? I basically need the exact text in each cell to populate the cell I'm copying to (no extra cleverness). Thanks, Chris.

 

[xlbo]

Think you're a bit stuck without code then I am afraid.


Rgds, Geoff

Three things are certain. Death, taxes and lost data. DPlank is to blame

Please read FAQ222-2244 before you ask a question

 

[Chrissirhc]

Well I decided to write a quick Macro which partially works, BUT everytime I run it comes up with a "File not found" dialogue, before actually pasting the contents I want. I guess the true value of the cell also includes the workbook name? Or something else?

Here is the code. Any ideas?

Sub copySelectionExactly()

Dim rangeSelection As Range
Dim rows As Integer
Dim columns As Integer

Dim destinationCells As Range

Set rangeSelection = Selection

rows = rangeSelection.rows.Count
columns = rangeSelection.columns.Count


Workbooks("Book3").Activate
Range("A1").Select
Set destinationCells = Range("A1")


' variables for loop
Dim i As Integer
Dim j As Integer

For j = 1 To rows
For i = 1 To columns
'MsgBox rangeSelection.Cells(j, i).Formula
Range("A1").Offset(j, i).value = rangeSelection.Cells(j, i).Formula
Next i
Next j

End Sub

Thanks in advance!

Chris

 

[Chrissirhc]

It is because the reference don't exist in the destination sheet. Thanks.

 

[xlbo]

Pardon me if I am missing something but if the reference does not exist, how does the formula work ??

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[Chrissirhc]

It doesn't calculate. I have to make sure the references are there before I run the macro.