copying rows, while keeping formulas on orginal

Hi all and thanks in advance to all

I am slowing making headway. But I am stuck. I am trying to copy all of a row, to another sheet, which is working fine.

But, I want to preserve the formulas from the original sheet after I clear that data out. I am using a copy command, and it works well, b ut leave the original data behind.

So I tried cut-- obviously, it took everything out.

I have been experimenting with the clear command, and using this line of code

Sheets("Sheet2").Range("2:2").Clear

After I have copied the data, I am clearing the row. Is there another comman d, ,or an add on that will leave the formulas behind and clear out teh row?

Thanks again..

John.

Thanks. 2 follow up questions:

1) The code leaves the formulas, but removes the drop down list from column B. Anyway to preserve that?

2) Is there a way that this will work for all rows of data? It is variable depending on the day as to how many rows I will have. I have attempted to put in m y counter variable where the "2:2" are to go through all rows, but it only executed on a row when I specify an constant number (ie rown 2, or 3, or 4....)

Thanks again

John

Thanks so much for you assistance. The code you gave me I pasted into each case section. I realize they all say "2:2". I may have 10 rows on monday, 3 rows tuesday, 25 rows wednesday of data that I will input, and subsequently copy to the other sheets.

Here is my code:

Sub Macro2()
Dim lr As Long, lr2 As Long, lr3 As Long, lr4 As Long, r As Long

lr = Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Row
lr2 = Sheets("RV2032").Cells(Rows.Count, "A").End(xlUp).Row
lr3 = Sheets("0042298-02").Cells(Rows.Count, "A").End(xlUp).Row
lr4 = Sheets("41352").Cells(Rows.Count, "A").End(xlUp).Row
For r = 2 To lr
Select Case Range("B" & r).Value
Case Is = "RV2032"
Range(Cells(r, 1), Cells(r, 10)).Copy Destination:=Sheets("RV2032").Range("A" & lr2 + 1)
lr2 = Sheets("RV2032").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Range("2:2").SpecialCells(xlCellTypeConstants).ClearContents
Case Is = "0042298-02"
Range(Cells(r, 1), Cells(r, 10)).Copy Destination:=Sheets("0042298-02").Range("A" & lr3 + 1)
lr3 = Sheets("0042298-02").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Range("2:2").SpecialCells(xlCellTypeConstants).ClearContents
Case Is = "41352"
Range(Cells(r, 1), Cells(r, 10)).Copy Destination:=Sheets("41352").Range("A" & lr4 + 1)
lr4 = Sheets("41352").Cells(Rows.Count, "A").End(xlUp).Row
Sheets("Sheet2").Range("2:2").SpecialCells(xlCellTypeConstants).ClearContents
End Select

Next r
End Sub

My data looks something like this:

a b c d e
date part number part name customer quantity


Thanks again

Scott

Try now. Each time I have been running it and trying new code, it takes the drop list out.

The source is column A off sheet1. I just looked on the copy I sent you, and its down column B. Dose it not transfer when uploading?

John

thank you so much!!

I am using 3 part numbers to figure out what I need to do for our company. We currently have 32 part numbers that we use, and we will be growing to over 100 by mid 2016. In my mind, I wanted to understand and get 3 working, then expand it to the full lilst of part numbers.

You have been such a great help.

Thanks again

Scott