# Paste as values

#### Flaviolib

##### New Member
Hi,

Below formula works but it paste as formula. How can I change to paste as value?

Sub SaveFinalPrices()
Dim bottomD As Integer
Dim ws As Worksheet
For Each ws In Sheets(Array("A", "B", "C"))
ws.Activate
bottomD = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:M" & bottomD).Copy Sheets("Hist Prods").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
Next ws
End Sub

Thanks

### Excel Facts

How to calculate loan payments in Excel?
Use the PMT function: =PMT(5%/12,60,-25000) is for a \$25,000 loan, 5% annual interest, 60 month loan.

#### L. Howard

##### Well-known Member
Hi Flaviolib, welcome to the forum.

Try this.

Regards,
Howard

Code:
``````Sub SaveFinalPrices()
Dim bottomD As Integer
Dim ws As Worksheet

For Each ws In Sheets(Array("A", "B", "C"))

ws.Activate
bottomD = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:M" & bottomD).Copy
Sheets("Hist Prods").Cells(Rows.Count, "A").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Next ws

End Sub``````

#### Flaviolib

##### New Member
Hi Howard,

Im getting Compile error: Syntax error

#### Flaviolib

##### New Member
My bad, It's working thanks a Lot

#### L. Howard

##### Well-known Member
You're welcome, glad to hear.

Regards,
Howard

#### Flaviolib

##### New Member
Let me take the opportunity for one more thing on this same SUB.

My sheets A B C have on their column A dates.
so before paste its content I would like to delete from destination sheet all lines where I have same dates on column A.

#### L. Howard

##### Well-known Member
You want to clear only the rows on "Hist Prods" column A that have dates in them, and then paste values?

Or will just clear column A on "Hist Prods" sheet and then paste?

Howard

#### Flaviolib

##### New Member
Remove all rows from "Hist Prods" where in column A we have a date = "refdate"

"Refdate"is a name range with a date value.

It should be done all time before we do the preview copy/paste.
thanks

#### L. Howard

##### Well-known Member
Try the add-in between the ****'s

Howard

Code:
``````Sub SaveFinalPrices()
Dim bottomD As Integer
Dim ws As Worksheet

For Each ws In Sheets(Array("A", "B", "C"))

ws.Activate
'/*****
Dim aRng As Range, c As Range

Set aRng = Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
For Each c In aRng
If IsDate(c) Then c.ClearContents
Next c
'/*****
bottomD = Range("A" & Rows.Count).End(xlUp).Row
Range("A2:M" & bottomD).Copy
Sheets("Hist Prods").Cells(Rows.Count, "A").End(xlUp)(2).PasteSpecial Paste:=xlPasteValues

Next ws

End Sub``````

#### Flaviolib

##### New Member
If I'll delete only "hist prods" rows, it's correct to be part of the "for each"?

Replies
3
Views
1K
Replies
4
Views
755
Replies
0
Views
205
Replies
0
Views
235
Replies
3
Views
635

1,190,896
Messages
5,983,441
Members
439,843
Latest member
PlanetFitness

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

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