In the following code I'm copying data from a worksheet (QuoteForm) to
another workbook called Quote Log Temp, worksheet TempQuoteLog. The
code works great, no issues at all. But I'd like to change it slightly,
but I'm struggling with how to do it.

Here's what I'd like to do, the critical variable is the Quote_Number
in the source worksheet, lets say that the quote number already exists
in the Quote Log Temp workbook (destination), how would I copy the data
from the source worksheet and copy over the existing data in the
destination workbook. I'm thinking you would first have to locate the
record in the destination worksheet, then copy the data over using
similar code to below, how would you code the locate portion?


Sub Rectangle24_Click()

Dim Src As Workbook
Dim Dest As Workbook
Dim iRow As Long
Dim ws As Worksheet

Set Src = ThisWorkbook 'Source Workbook
Set Dest = Workbooks.Open("C:\Temp\Quote Models\Quote Log Temp.xls")
Set ws = Dest.Worksheets("TempQuoteLog")

'find first empty row in database
iRow = ws.Range("A65536").End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value =
Src.Worksheets("QuoteForm").Range("Quote_Number")
ws.Cells(iRow, 2).Value =
Src.Worksheets("QuoteForm").Range("ProductCode")
ws.Cells(iRow, 3).Value = Src.Worksheets("QuoteForm").Range("Customer")

End Sub

Thanks
burl_rfc

Sub Rectangle24_Click()

Dim Src As Workbook
Dim Dest As Workbook
Dim iRow As Long
Dim ws As Worksheet
Dim rng as Range
Dim rng1 as Range

Set Src = ThisWorkbook 'Source Workbook
Set Dest = Workbooks.Open("C:\Temp\Quote Models\Quote Log Temp.xls")
Set ws = Dest.Worksheets("TempQuoteLog")
Set rng = Src.Worksheets("QuoteForm").Range("Quote_Number")

'find first empty row in database
set rng1 = ws.Columns(1).Find( _
What:= rng.Value, _
After:=ws.Cells(rows.count,1), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
if not rng1 is nothing then
iRow = rng1.row
Else
iRow= ws.Range("A65536").End(xlUp).Offset(1, 0).Row
End if

'copy the data to the database
ws.Cells(iRow, 1).Value = rng.Value
ws.Cells(iRow, 2).Value =
Src.Worksheets("QuoteForm").Range("ProductCode")
ws.Cells(iRow, 3).Value = Src.Worksheets("QuoteForm").Range("Customer")

End Sub

--
Regards,
Tom Ogilvy


"burl_rfc" wrote:

In the following code I'm copying data from a worksheet (QuoteForm) to
another workbook called Quote Log Temp, worksheet TempQuoteLog. The
code works great, no issues at all. But I'd like to change it slightly,
but I'm struggling with how to do it.

Here's what I'd like to do, the critical variable is the Quote_Number
in the source worksheet, lets say that the quote number already exists
in the Quote Log Temp workbook (destination), how would I copy the data
from the source worksheet and copy over the existing data in the
destination workbook. I'm thinking you would first have to locate the
record in the destination worksheet, then copy the data over using
similar code to below, how would you code the locate portion?


Sub Rectangle24_Click()

Dim Src As Workbook
Dim Dest As Workbook
Dim iRow As Long
Dim ws As Worksheet

Set Src = ThisWorkbook 'Source Workbook
Set Dest = Workbooks.Open("C:\Temp\Quote Models\Quote Log Temp.xls")
Set ws = Dest.Worksheets("TempQuoteLog")

'find first empty row in database
iRow = ws.Range("A65536").End(xlUp).Offset(1, 0).Row

'copy the data to the database
ws.Cells(iRow, 1).Value =
Src.Worksheets("QuoteForm").Range("Quote_Number")
ws.Cells(iRow, 2).Value =
Src.Worksheets("QuoteForm").Range("ProductCode")
ws.Cells(iRow, 3).Value = Src.Worksheets("QuoteForm").Range("Customer")

End Sub

Thanks
burl_rfc