Automatic data transfer

Closed
sam - Jul 15, 2014 at 03:42 AM
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 - Jul 15, 2014 at 10:24 AM
Hello,

I have two tabs in a work book, Materials and Hire. In materials I have column A - PO Number Column B - Job Number and Column C - Date I am wanting to transfer the data in these columns based on if Column F - Hire / Materials is a H (if H pull po number, job number and date into Hire Tab if not its to stay in materials Tab) The Columns in the Hire tab are set out in the same way Column A - PO Number Column B - Job Number Column C Date

Please can anyone advise a macro or formula to do this automatically


1 reply

TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen January 16, 2023 544
Jul 15, 2014 at 10:24 AM
Hi Sam,

Right-click on the Materials sheet tab and click on "View code". Paste the following code in the big white field:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("F:F")) Is Nothing Then Exit Sub

If Target.Value = "H" Then Range(Cells(Target.Row, "A"), Cells(Target.Row, "C")).Copy _
Sheets("Hire").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)

End Sub

Once that is done, whenever you enter an "H" in column F of the Materials sheet, column A to C of that row will be copied to the first available row of the Hire sheet.

Best regards,
Trowa
0