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 replies

Answer 1 / 3
TrowaD
Posts
2888
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 16, 2022
 515
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
2888
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
August 16, 2022
 515
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