Paste as values

Flaviolib

New Member
Joined
Sep 1, 2014
Messages
21
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

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,416
Messages
6,178,504
Members
452,853
Latest member
philipnjk64

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