Correct Syntax for Copy Worksheet & Pasting Vaules

YQue

New Member
Joined
Oct 7, 2002
Messages
9
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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

YQue

New Member
Joined
Oct 7, 2002
Messages
9
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...
 

Forum statistics

Threads
1,143,704
Messages
5,720,378
Members
422,281
Latest member
jantoooh

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top