Locate specific record
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 |
Locate specific record
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 |
All times are GMT +1. The time now is 04:21 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com