Copying from one Workbook to Another #REF

[kpierce]

Hello -
I have gotten a nice script going thanks to all the help on this forum. My scropt opens all the workbooks in a specific directory and copies over information into a consolidated sheet in the MASTER workbook. Like a dashboard.

This all worked with when I just had cell colors and values, but now I have added several nested IF statements and conditional formatting.

So when I run the script now, it tries to copy over all the IF statements and it replaces the cells, etc. with #REF.

What I want to do is just 'snag' the color and value from the cell..
For example a cell I want to copy contains:
=IF(O35<=-0.002,"R",IF(O35<=-0.001,"Y","G")), and the Cell is GREEN in the sundry file that is being read.

Now when I read that cell and place it into my MASTER I get: #REF
=IF(#REF!<=-0.002,"R",IF(#REF!<=-0.001,"Y","G"))

the code that is copying over this section is:
ws.Range("C2").Copy _
wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1)

These cells can be: R(Red), Y(Yellow), G(Green), C(Complete), Blank. I can deal with the conditional formatting, but how do I just pick the value from the cell and not the formula?

Hopefully there is a way to get this to work. Otherwise, I am really 'hosed'.

thanks

 

[GlennUK]

Don't copy .... just get the value, like:

wsDest.Range("A" & Rows.Count).End(xlUp).Offset(1).Value = ws.Range("C2").Value

 

[kpierce]

much appreciated....

 

[GlennUK]

My pleasure.