Copying all rows that meet a criteria to another worksheet

[nzkazza]

I am trying to copy entire rows that have a value in column A from an entry worksheet - starts at A10 (Sheet1) to the next empty line in a sales record worksheet(Sales), then clear the entry worksheet. I am very new to VBA coding. I also have a hidden column in the entry worksheet with contains a TODAY() Forumla the actual result needs to also be copied - in fact there are a number or formula in the entry worksheet. Both appeared to be working this morning however as I have renamed sheets - and modified the names in the formula while working on other things today I seem to have lost all but the first row when I run the marco. Can any one point out where I am going wrong please: Code as follows:

Sub CopyRowC()

    Dim ws As Worksheet
    Dim r As Range

    Set ws = Worksheets("Sales")
    Set r = Range("A10", Range("A65536").End(xlUp))

    For Each c In r.Cells
        If c <> "" Then
            c.EntireRow.Copy Destination:=ws.Range("A65536").End(xlUp).Offset(1, 0)
      Cells.Select
Selection.SpecialCells(xlCellTypeConstants, 1).Select
Selection.ClearContents
    
   End If
     Next
    MsgBox "Thankyou the sales records have been updated"
    
    
End Sub

Sorry if I have posted this wrong - not sure how to load the code differently than this - Like I say I am new at this I bit off a little more than I can chew but need to get the workbook working. Yes I did save three different copies but the error has occured prior to the first save.

I am sure the answer is staring me in the face -
Thanks
Kazza

[nzkazza]

I Have found where the problem lies - it is clearing the contents of the other rows prior to copying - if I delete the selection.special cells etc from the code it pastes each relevant row. Can someone please point me to the coding I should use in the same macro to clear the rows after all have been copied or can i lead one marco into another? Like I say I have little knowledge.

[arlu1201]

Try this code

Sub CopyRowC()

    Dim ws As Worksheet
    Dim r As Range
    Dim lrow As Long
    Dim c As Range
    
    Set ws = Worksheets("Sales")
    lrow = Worksheets("Entry").Range("A" & Rows.Count).End(xlUp).Row
    Set r = Worksheets("Entry").Range("A10:A" & lrow)

    For Each c In r.Cells
        If c <> "" Then
            c.EntireRow.Copy Destination:=ws.Range("A65536").End(xlUp).Offset(1, 0)
            c.EntireRow.ClearContents
        End If
    Next c
    
    MsgBox "Thankyou the sales records have been updated"
        
End Sub

[arlu1201]

Welcome to the forum.

I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. If you need more information on how to use them, check my signature.

[nzkazza]

Will this not clear all my forumlas to? I need thm to stay there.

[arlu1201]

So which are the cells / columns you want to be cleared?

[nzkazza]

Thanks Arlette
I have solved the problem - added Next, repeated the if statment and then the Special Cell selection and it works. Problem I have now though that I did not realise is that the col with the TODAY() formula is copying the formula and not the result. Thus it updates to today for every record in the Sales Records worksheet. I "think" I need to remove the col/forumla off the sales input sheet and instead add something to the code to get it to insert the Value of Today() to each line just pasted.

[arlu1201]

You can try this - change the column as per your sheet -

ws.Range("P" & rows.count).end(xlup).offset(1,0).value = date

[nzkazza]

Thank you so much
That works perfectly - can I confirm that this is a date stamp and tommorrow the date on today's entries won't change? But tommorrows will de displayed for tommorrows entries?

Kazza

[arlu1201]

Yes, the date will be input as a value and not as a formula, so it wont change.