Correct Syntax for Copy Worksheet & Pasting Vaules

[YQue]

All,

I have a macro to copy worksheets and consolidate them into one. However I need to either convert the formulas in the source worksheet to values before I copy them or paste values only when I paste them. Anyone?

Below is what I have as the code...
The top just let's you know what is the destination of the copy. Below that is the paste. (1) is original code (2) is how I tried to use paste special an it bombed.

'Set the destination cells

Set destCell = _
.Cells(.Cells.SpecialCells _
(xlCellTypeLastCell).Row + 1, "A")



'MANIPULATE STARTING ROW TO CONSOLIDATE

'(1)
.Range(.Cells(HeaderRows.Rows.Count, 1), _
.Cells.SpecialCells(xlCellTypeLastCell)).Copy _
Destination:=destCell


'(2)
.Range(.Cells(HeaderRows.Rows.Count, 1), _
.Cells.SpecialCells(xlCellTypeLastCell)).Copy _
PasteSpecial.Paste:=xlPasteValues, _
Destination:=destCell

 

[YQue]

I posed the question and then found the syntax in an old VB book...

Instead of pasting the values I will convert formuals...

After making the correct worksheet active. I did this...

ActiveSheet.UsedRange.SpecialCells(xlFormulas)

.Value = .Value

Thanks Anyway...