Previous Topic Next Topic

Automatically transfering rows from one sheet to another

Solved/Closed
Report
Posts
2
Registration date
Tuesday September 16, 2014
Status
Member
Last seen
September 26, 2014
 -
Posts
2
Registration date
Tuesday September 16, 2014
Status
Member
Last seen
September 26, 2014
 -
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
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
 490
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
Thank you
Thank you

A few words of thanks would be greatly appreciated. Add comment

CCM 2821 users have said thank you to us this month

Answer 2 / 3

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
Report
Posts
2829
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
November 22, 2021
 490
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
Answer 3 / 3
Posts
2
Registration date
Tuesday September 16, 2014
Status
Member
Last seen
September 26, 2014
Thank you TrowaD - that worked perfectly!!!
Ask a question