Paste as values

[Flaviolib]

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

 

[L. Howard]

Hi Flaviolib, welcome to the forum.

Try this.

Regards,
Howard

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]

Hi Howard,

Im getting Compile error: Syntax error

 

[Flaviolib]

My bad, It's working thanks a Lot

 

[L. Howard]

You're welcome, glad to hear.

Regards,
Howard

 

[Flaviolib]

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]

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]

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]

Try the add-in between the ****'s

Howard

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]

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