"Ghost" values when pasting via VBA

[JungleJme]

Hi,

I'm writing a macro that does a copy and paste from a table to another workbook - this workbook is then used for a mail merge.

Part of the macro also then saves what has been copied back to a history table. Which can then be used to track when/which batch records were last mailed.

My problem. When do the paste back to the history table, then some kind of ghost value is coming across in blank cells in a column. Any formula that references this column immediately goes to a #Value error.

If i manually select blank cells and hit DELETE - then the formulas right themselves. I've tried the following code. Including skipblanks set to true. As soon as this paste completes, my formulas go to #Value, how can i stop these "ghost" values (whatever they are) messing up my formulas?!

Please Login or Register  to view this content.

' activate the workbook and copy the range

Please Login or Register  to view this content.

' paste the range to the bottom the history table

Please Login or Register  to view this content.

[JungleJme]

In case anyone else comes across this thread - i evaluated the contents of the cells, right before the first copy by using the SpecialCells (xlCellTypeConstants) property and could see that excel was treating the blanks as being a constant - i don't know the reason why.

Anyway, i re-jigged my formulas so i didn't have to paste these columns. But still don't know why excel pasted empty cells out of a table and thought they had some kind of value in.

Thanks for looking anyway.