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]

Hmmm, I'm getting lost on what should be doing what and where.

Maybe like this, where it refers to sheet "Hist Prods" column A range.

   Dim aRng As Range, c As Range

  Set aRng = Sheets("Hist Prods").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    For Each c In aRng
        If IsDate(c) Then c.ClearContents
    Next c

Howard

 

[Flaviolib]

Trying to clarify,

Before we do the loop through the sheets A, B, C copying to "Hist Prods" I want to clear some rows on "hist prods":

What I want to remove from "hist prod" is all rows where in cells from columns A I have a date = "refdate"

* column A is a date column
* "refdate" is a named range with a date that would be checked to delete rows on "hist prods"

hope it's good now

 

[L. Howard]

So then we should run the date clear stuff for sheet "Hist Prods" first and follow with the for each sheet in array.

Does that do it the way you need?

Howard

Sub SaveFinalPrices()

'/*****
With Sheets("Hist Prods")
  Dim aRng As Range, c As Range

  Set aRng = Sheets("Hist Prods").Range("A1:A" & Cells(Rows.Count, "A").End(xlUp).Row)
    For Each c In aRng
        If IsDate(c) Then c.ClearContents
    Next c
End With
'/*****


 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]

where I should include "refdate" ?

* all my column A cels have dates. just what to delete row when this date = "refdate"

 

[L. Howard]

I'm guessing here, but try this on a test sheet. Using refdate instead of aRng.

Howard

With Sheets("Hist Prods")
  Dim refdate As Range, c As Range

    For Each c In refdate        
         If IsDate(c) Then c.ClearContents
    Next c
End With

 

[Flaviolib]

Hi mate,

Getting error: refdate=Nothing

Your for each should not loop on column A?
just to illustrate, I have a cell where I have my reference date that I call it in named range as "refdate".
I want to seek in below table on column A for any date equal to my "refdate"
So, for example below I'll delete Row 3 only, because cell A3 = "refdate".



[TABLE="width: 505"]
<colgroup><col width="101" span="5" style="width:76pt"> </colgroup><tbody>[TR]
[TD="width: 101"][/TD]
[TD="class: xl69, width: 101"]Refdat:[/TD]
[TD="class: xl70, width: 101"]2-Sep[/TD]
[TD="width: 101"][/TD]
[TD="width: 101"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="class: xl67"] [/TD]
[TD="class: xl69"]Column A[/TD]
[TD="class: xl69"]Column B[/TD]
[TD="class: xl69"]Column C[/TD]
[TD="class: xl69"]Column D[/TD]
[/TR]
[TR]
[TD="class: xl69"]Row 1[/TD]
[TD="class: xl68"]28-Aug[/TD]
[TD="class: xl69"]Mike[/TD]
[TD="class: xl69"]30[/TD]
[TD="class: xl69"]100[/TD]
[/TR]
[TR]
[TD="class: xl69"]Row 2[/TD]
[TD="class: xl68"]1-Sep[/TD]
[TD="class: xl69"]Jay[/TD]
[TD="class: xl69"]31[/TD]
[TD="class: xl69"]150[/TD]
[/TR]
[TR]
[TD="class: xl69"]Row 3[/TD]
[TD="class: xl68"]2-Sep[/TD]
[TD="class: xl69"]Ed[/TD]
[TD="class: xl69"]32[/TD]
[TD="class: xl69"]200[/TD]
[/TR]
</tbody>[/TABLE]

 

[L. Howard]

Can you post a link to an example workbook and re state what all you want to happen.

I think I have an idea of what you want, but an example workbook and all instructions would be very helpful.

I use Drop Box, but there are others.

Howard

 

[Flaviolib]

Hi

I think below will solve.
I'm just having problem with this line:
.AutoFilter 1, "=" & .Range(RngRefdate).Value

I think because this range is in other worksheet, not at "hist prods"

this range was created on workbook scope, there is a way to reference to it without specify the worksheet where it is?

Sub testDel()

Const RngRefdate = "refdate"


With Worksheets("Hist Prods")
.AutoFilterMode = False
With Range("A2", Range("A" & Rows.Count).End(xlUp))
.AutoFilter 1, "=" & .Range(RngRefdate).Value
On Error Resume Next
.Offset(1).SpecialCells(12).EntireRow.Delete
End With
.AutoFilterMode = False
End With
End Sub

 

[L. Howard]

I am lost as to how to handle the Refdate. Sorry.

Regards,
Howard