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
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