I need to modify some code and hopefully someone experienced could help me.
The code below transfers data to another workbook (63.xls), this work fine.
In cell I7 there is a reference no. I need it to check it that already exist in 63.xls, if it does then overwrite that same row, if it don't then create a new entry as it's already doing.
Thanks for any help
Regards
Kelly
Sub Xfer2()
Dim path As String, LastRow, V1, V2, V3, V4, V5, V6
V1 = Sheets("CSF 63").Range("I7").Value
V2 = Sheets("CSF 63").Range("I6").Value
V3 = Sheets("CSF 63").Range("B9").Value
V4 = Sheets("CSF 63").Range("C9").Value
V5 = Sheets("CSF 63").Range("J9").Value
V6 = Sheets("CSF 63").Range("K9").Value
path = ThisWorkbook.path ' assumes CIS.XLS is in the same folder
Workbooks.Open (path & "\63.XLS")
LastRow = Sheets("Record").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Record").Cells(LastRow + 1, 1).Value = V1
Sheets("Record").Cells(LastRow + 1, 2).Value = V2
Sheets("Record").Cells(LastRow + 1, 3).Value = V3
Sheets("Record").Cells(LastRow + 1, 4).Value = V4
Sheets("Record").Cells(LastRow + 1, 5).Value = V5
Sheets("Record").Cells(LastRow + 1, 6).Value = V6
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub
The code below transfers data to another workbook (63.xls), this work fine.
In cell I7 there is a reference no. I need it to check it that already exist in 63.xls, if it does then overwrite that same row, if it don't then create a new entry as it's already doing.
Thanks for any help
Regards
Kelly
Sub Xfer2()
Dim path As String, LastRow, V1, V2, V3, V4, V5, V6
V1 = Sheets("CSF 63").Range("I7").Value
V2 = Sheets("CSF 63").Range("I6").Value
V3 = Sheets("CSF 63").Range("B9").Value
V4 = Sheets("CSF 63").Range("C9").Value
V5 = Sheets("CSF 63").Range("J9").Value
V6 = Sheets("CSF 63").Range("K9").Value
path = ThisWorkbook.path ' assumes CIS.XLS is in the same folder
Workbooks.Open (path & "\63.XLS")
LastRow = Sheets("Record").Cells(Rows.Count, 1).End(xlUp).Row
Sheets("Record").Cells(LastRow + 1, 1).Value = V1
Sheets("Record").Cells(LastRow + 1, 2).Value = V2
Sheets("Record").Cells(LastRow + 1, 3).Value = V3
Sheets("Record").Cells(LastRow + 1, 4).Value = V4
Sheets("Record").Cells(LastRow + 1, 5).Value = V5
Sheets("Record").Cells(LastRow + 1, 6).Value = V6
ActiveWorkbook.Save
ActiveWorkbook.Close
End Sub