Copy cells from a row to a new table based on cell value

[AccessP]

Hi there,

I am new to the boards and need some coding help.

I am compiling a spreadsheet that consists of 2 work sheets, "Quoted works" and "Current Works".

I have done some very simple conditional formatting for colour schemes.

When jobs on the "Quoted Works" sheet the "Status" column becomes "Approved" I would like specific data to be copied into a new job in the "Current Works" sheet.

The cell data that would need to move over to the "Current Works" sheet would be:

- Status (Approved)
- Job#
- Logged By
- Log Date
- Responsible
- Description

The above column headers are replicated in both sheets.

The rest of the sheet would remain blank for the user to manually enter.

We would also need to manually enter jobs (rows) as not all jobs go through the quoted works process.

I have attached a sample worksheet as an example.

Can anyone help me with this or give me some pointers to start me off as it is way beyond my skillset.

FYI I am using Office 2003 but it also needs to be compatible with Office 2010.

Thanks!!

Michael

[patel45]

When jobs on the "Quoted Works" sheet the "Status" column becomes "Approved" I would like specific data to be copied into a new job in the "Current Works" sheet.

copied or moved ?

[AccessP]

Copied.

Thanks for your reply patel45.

[AB33]

Try the attached.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[AccessP]

Thanks for all the replies.

JOHN H. DAVIS your code has worked a charm. Thanks heaps!!

[AccessP]

Sorry guys I have one more issue.

I have utilised the code from JOHN H. DAVIS but when my new entry goes to the new worksheet, I also need to be able to change the first cell of the row from "Approved" to "In Progress" or any of my other options.

The code that I am using does not allow me to make changes to the first cell "Approved".

Any help would be appreciated.

I am using the following code as posted by JOHN H. DAVIS:

Private Sub CommandButton1_Click()

End Sub

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Range("A5:A" & ActiveSheet.UsedRange.Rows.Count)) Is Nothing Then
If Target.Value = "Approved" Then
Target.Copy Sheets("Current Works").Range("A" & Rows.Count).End(3)(2)
Target.Offset(, 6).Copy Sheets("Current Works").Range("B" & Rows.Count).End(3)(2)
Target.Offset(, 2).Copy Sheets("Current Works").Range("C" & Rows.Count).End(3)(2)
Target.Offset(, 3).Copy Sheets("Current Works").Range("D" & Rows.Count).End(3)(2)
Target.Offset(, 4).Copy Sheets("Current Works").Range("E" & Rows.Count).End(3)(2)
Target.Offset(, 8).Copy Sheets("Current Works").Range("G" & Rows.Count).End(3)(2)
End If
End If
End Sub

Thanks.

[JOHN H. DAVIS]

Maybe:

Please Login or Register  to view this content.

[JOHN H. DAVIS]

You're welcome. Glad to help out and thanks for the feedback.

[AB33]

John,
I thought the OP wants to include the test from rows 1 and below.

Please Login or Register  to view this content.

is probably The A5 needs to be changed in to A1.

[JOHN H. DAVIS]

John,
I thought the OP wants to include the test from rows 1 and below.

Please Login or Register  to view this content.

is probably The A5 needs to be changed in to A1.

I don't see where he requested that? My understanding from Post #7 is he wanted to change the status from "Approved" to "In Progress" on the destination sheet.

[AB33]

Okay!
I may have misread it!

[AccessP]

Sorry but my explanation probably wasn't clear.

What I need is the new entry on the destination sheet (Current Works) to have "Approved" in the first column as it is indicated in the entry sheet (Quoted Works), but then have the user have the ability to change "Approved" to one of my other options (ie "In Progress", "To be invoiced", "Invoiced") on the destination sheet.

With the current code in place we receive an error message whenever we try to change the status of the job (row) from "Approved" to a new status in the destination sheet (Current Works): "The value you entered is not valid. A user has restricted values that can be entered into this cell."

Basically this is so that when a job is under-way we are able to track and update the status of the job.

[JOHN H. DAVIS]

Sorry for the late reply, is this what you need?

Please Login or Register  to view this content.

[AccessP]

Sorry I have been away from my computer for a few days.

Thanks for the modification JOHN H. DAVIS. Your solution has worked a charm!!

[JOHN H. DAVIS]

You're welcome. Glad to hear you're problem is solved.