if product exists then copy to that row

[Wagstaff]

Hi,

I have posted this to another forum but have not received a reply so

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 exists 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,same as before.

Any ideas

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