Hi,

Bit shamed that I have to keep comming back day after day but I have hit
another problem.

The workbook I have created will allow user to set up records for customer
pricing.
The code below is the module that will check if the file already exist and
if so it will add the new record to that file and If it does not exist it
will go to a nother module to create a new workbook.

Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
Dim wsNew As Worksheet
Dim bk As Workbook
Dim bSave As Boolean
Dim myFile As String


With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Customer = Worksheets("CurrentRecord").Range("F2").Value
With Worksheets("CustomerLogSheet").Columns("A")
Set C = .Columns("A").Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)

End With

If C Is Nothing Then
Application.Run "Copy_To_Workbooks4"
Else
myFile = C.Offset(0, 1).Value
Set DestWB = Workbooks.Open(myFile)
Sheets(1).Unprotect Password:="mypsswrd"


'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("CurrentRecord").Range("A2:G2" )
'Change the sheet name of the database workbook
Set DestSh = DestWB.Sheets(1)


Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

Sheets(1).Protect Password:="mypsswrd"
DestWB.Close savechanges:=True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Sheets("CurrentRecord").Select
Sheets("CurrentRecord").Unprotect Password:="mypsswrd"
Range("A2:G2").Clear
Sheets("CurrentRecord").Protect Password:="mypsswrd"

End If
End Sub


The problem I am having is that if the product already eixts for that
customer I want it to overwrite that row with the new data. I have tried the
code below but, yes you have guessed it doesn't work. Just copies to the last
row.

Sub Copy_To_Another_Workbook()
Dim SourceRange As Range
Dim DestRange As Range
Dim DestWB As Workbook
Dim DestSh As Worksheet
Dim Lr As Long
Dim wsNew As Worksheet
Dim bk As Workbook
Dim bSave As Boolean
Dim myFile As String


With Application
.ScreenUpdating = False
.EnableEvents = False
End With

Customer = Worksheets("CurrentRecord").Range("F2").Value
With Worksheets("CustomerLogSheet").Columns("A")
Set C = .Columns("A").Find(what:=Customer, _
LookIn:=xlValues, lookat:=xlWhole)

End With

If C Is Nothing Then
Application.Run "Copy_To_Workbooks4"
Else
Product = Worksheets("CurrentRecord").Range("E2").Value
myFile = C.Offset(0, 1).Value
Set DestWB = Workbooks.Open(myFile)
Sheets(1).Unprotect Password:="mypsswrd"

'Change the Source Sheet and range
Set SourceRange = ThisWorkbook.Sheets("CurrentRecord").Range("A2:G2" )
'Change the sheet name of the database workbook
Set DestSh = DestWB.Sheets(1)


With DestSh.Columns("E")

Set C = .Columns("E").Find(what:=Product, _
LookIn:=xlValues, lookat:=xlWhole)
If C Is Nothing Then


Lr = LastRow(DestSh)
Set DestRange = DestSh.Range("A" & Lr + 1)

'We make DestRange the same size as SourceRange and use the Value
'property to give DestRange the same values
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value

Sheets(1).Protect Password:="danrob1968"
DestWB.Close savechanges:=True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Sheets("CurrentRecord").Select
Sheets("CurrentRecord").Unprotect Password:="danrob1968"
Range("A2:G2").Clear
Sheets("CurrentRecord").Protect Password:="danrob1968"

Else
If C Is Found Then
Set firstAddress = C.Address
C.Row = C.Address
Set DestRange = DestSh.Range("A" & C.Row)
With SourceRange
Set DestRange = DestRange.Resize(.Rows.Count, .Columns.Count)
End With
DestRange.Value = SourceRange.Value
Sheets(1).Protect Password:="mypsswrd"
DestWB.Close savechanges:=True

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

Sheets("CurrentRecord").Select
Sheets("CurrentRecord").Unprotect Password:="mypsswrd"
Range("A2:G2").Clear
Sheets("CurrentRecord").Protect Password:="mypsswrd"
End If
End With
End If
End Sub


can you help me AGAIN.

Thanks
Winnie