Need hep with Macro

[kelly1]

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

 

[acw]

Kelly

Try

Sub Xfer2()

Dim path As String, OutRow, V1, V2, V3, V4, V5, V6, FindRef
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")
Set FindRef = Range("A:A").Find(what:=V1, lookat:=xlWhole)
If Not FindRef Is Nothing Then
  OutRow = FindRef.Row
Else
  OutRow = Sheets("Record").Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row
End If
Sheets("Record").Cells(OutRow, 1).Value = V1
Sheets("Record").Cells(OutRow, 2).Value = V2
Sheets("Record").Cells(OutRow, 3).Value = V3
Sheets("Record").Cells(OutRow, 4).Value = V4
Sheets("Record").Cells(OutRow, 5).Value = V5
Sheets("Record").Cells(OutRow, 6).Value = V6

ActiveWorkbook.Save

ActiveWorkbook.Close
End Sub

Tony

 

[kelly1]

Works great

Thank you

Regards
Kelly