Previous Topic Next Topic

Automatically transfering rows from one sheet to another

Solved/Closed
madi9749 Posts 2 Registration date Tuesday September 16, 2014 Status Member Last seen September 26, 2014 - Sep 16, 2014 at 02:11 AM
madi9749 Posts 2 Registration date Tuesday September 16, 2014 Status Member Last seen September 26, 2014 - Sep 26, 2014 at 12:37 AM
Hi,

I was wondering if anyone could help. I've created a spreadsheet to track all contracts complied within our department. I have a "Contract Status" column which I have used data validation to create a dropdown with the selection of "Live" and "Cancelled". I now need to have all the jobs when changed to "Cancelled" to copy the entire row of information for that contract into the next worksheet so that I can track how many jobs cancel.

Can anyone help? I'm not very farmiliar with macros - so please go easy on me!

Cheers.

3 responses

Answer 1 / 3
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 16, 2014 at 11:05 AM
Hi Madi,

A bit more info would be convenient.

Which column contains the DDL's (drop down lists)?

Provide sheet names, so you don't have to alter them.

Do you know how to implement codes?

Best regards,
Trowa
3
Answer 2 / 3
Madi
Sep 19, 2014 at 12:43 AM
Hi Trowa,

Colum H contains the DDL...
Sheet names are "'Estate Name'" and "'Estate Name' Cancellations".

I want to duplicate these sheets for different Estate Names as we will be dealing with more than 1 Estate Name at a time.

I do know how to impletment codes so if you could help that would be fantastic. Then I can manipulate the codes depending on the estate I'm dealing with.

Thank you so much - you are saving me a whole lot of "trial-and-errors"!!

Cheers
1
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 552
Sep 23, 2014 at 10:35 AM
Ok Madi,

Here you go: 
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("H:H")) Is Nothing Then Exit Sub
If Target = "Cancelled" Then Target.EntireRow.Copy Sheets("Estate Name Cancellations").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
End Sub


Best regards,
Trowa
0
Answer 3 / 3
madi9749 Posts 2 Registration date Tuesday September 16, 2014 Status Member Last seen September 26, 2014
Sep 26, 2014 at 12:37 AM
Thank you TrowaD - that worked perfectly!!!
0