Auto generation, copy & paste date

[KeithHiggins]

Hi Guys.
I am a newbie to this so please be gentle. Here is my situation and what I would like to do if possible. I have a worksheet named "quote". a1 is date, b1 is quote#, c1 is client, d1 is address, e1 is value (in dollars) and i1 is yes/no.

What I would like to do is when the value of i1=yes I want to auto copy a1:e1 to another worksheet named "job". Where this all starts to fall apart is that when the auto copy action is carried out I would like to auto generate a job number and the date it gets copied to the "job" worksheet.

When this happens I would like to have the new information being generated to be added to the bottom of the list in the next "available" row.

Am I asking too much of the program, is it possible to do and lastly can anybody help me with this.

Thanks Guys
Keith

 

[texasalynn]

Welcome to MrExcel board...


Ok so try this - this code needs to go into the "Quote" worksheet code. If you right click on that sheet then click view code

Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if the Target is in
'Column 9 = yes
If Target.Column = 9 Then
If Target.Value = "Yes" Then
'Store Target Address
MyAddress = Target.Address
'Find Next Empty Cell In Sheet2 Column A
NextRow = Sheets("Job").Range("A" & Rows.Count).End(xlUp).Row + 1
'Cut and Paste Target Row to Sheet2
Target.EntireRow.Cut Destination:=Sheets("Job").Range("A" & NextRow)
Sheets("Job").Range("H" & NextRow) = Date
Sheets("Job").Range("G" & NextRow) = "Job" & WorksheetFunction.Text(NextRow, "000")
'Delete Target Row in Quote Sheet
Range(MyAddress).EntireRow.Delete
End If
End If
End Sub