REF Cell Issue

[leviathan86]

I have a macro that copies a sheet and saves values (Sheet Baseline)


What I have is a formula that call on values from cells in sheet baseline.

"=IF($Y$3<Config!$B$2,BaseLine!O5,BaseLine!N5)" The formula being if Date is less then Date(Config), then o5 else n5 in sheet baseline.


This works fine, but when I run the macro that copies and pastes values into baseline, the formula switches to =IF($Y$3<Config!$B$2,#REF!O5,#REF!N5)

[leviathan86]

One option I am thinking of is, use record macro to paste the formula then drag down. Any better way?

[Steve0492]

How do you have it copying and pasting? Perhaps try the Cells(0,0)=Cells(0,0) method or something similar.

For instance if you want the forumula to copy from A1 to A10.

Dim i as integer
For i = 1 to 9
Cells(i, 1) = Cells(1, 1)
Next i

Let me know how this works for you.

Steve

[leviathan86]

What is happening at the moment is my macro deletes the sheet, creates a new one and pastes the values in. Reason I did this is because there are also column groups that I wanted to eliminate and couldnt think of a better way to do this.

I think it would be easier if I was able to clear all contents along with column groups, is there a way to do this via macro?

[leviathan86]

Bump nothing seems to work. I did the macro but no luck.

I would just like the value to be pinned to a certain cell regardless of if the sheet exists or not.

=BaseLine!AA5
becomes
=#REF!AA5

[lmvk99]

Hi leviatan86. The problem is that when you delete your sheet you kill all of your references. Then you create a new sheet of the same name but it's too late, your references are dead. If you are deleting your sheet to get rid of grouping, instead why don't you just try this:

ActiveSheet.Columns.ClearOutline

Good Luck!

[lmvk99]

A not so good way (ie. it uses lots of processing power) is to replace all of your references with =INDIRECT("Baseline!"&CELL("address")) but its really not a good solution and will slow up your working a fair bit.

[Steve0492]

leviathan can you post your workbook? I am not an expert and seeing your work would help me help you.

Steve