Need hep with Macro

kelly1

Well-known Member
Joined
May 11, 2003
Messages
525
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
 

Some videos you may like

Excel Facts

Excel Joke
Why can't spreadsheets drive cars? They crash too often!

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
Kelly

Try

Code:
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
 

Watch MrExcel Video

Forum statistics

Threads
1,111,493
Messages
5,541,041
Members
410,543
Latest member
ExcelGlenn
Top