Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross referencing two workbooks?
Hi Everyone,
Is anyone out there that can help me. I have two workbooks. The first workbook is open and the second workbook is close. The first workbook contains my product code and the cost. the second workbook contains my product code with the MOST up to date cost. I manage to create a macro which returns the MOST up to date cost in my first workbook. But my code is opening the second workbook. I wonder if there is a way that i dont have to open the second workbook but still manage to do the cross referencing? Please advise. Below is the code. thanks much. Sub CostUpdate() ' ' CostUpdate Macro ' Macro recorded 13/01/2009 by jeffreyst ' Application.ScreenUpdating = False Dim activeWorkbookName As String activeWorkbookName = ActiveWorkbook.Name Workbooks.Open Filename:="G:\Commercial Auckland\Estimating Dept\Bpcs Cost\z UpdatedCost.xls" Dim active As Workbook Dim sourceRow As Long Dim destination As Long Dim code As Long Dim cost As Double sourceRow = Sheets("Sheet1").Range("a65536").End(xlUp).Row For i = 2 To sourceRow Windows("z UpdatedCost.xls").Activate code = Range("a" & i).Value cost = Range("c" & i).Value Windows(activeWorkbookName).Activate destination = Sheets("MaterialDatabase").Range("b65536").End (xlUp).Row Dim cont As Boolean Dim count As Integer cont = True count = 3 Do While cont Dim matchCode As Long matchCode = Sheets("MaterialDatabase").Range("B" & count).Value If matchCode = code Then Sheets("MaterialDatabase").Range("C" & count).Value = cost cont = False End If count = count + 1 If count destination Then cont = False End If Loop Next i Windows("z UpdatedCost.xls").Activate 'Application.CutCopyMode = False ActiveWorkbook.Close End Sub |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross referencing two workbooks?
Have a look at Ron de Bruin's code to retrieve data from a closed workbook.
Adapt to suit. http://www.rondebruin.nl/copy7.htm On Sun, 18 Jan 2009 10:30:12 -0800 (PST), Jeffrey wrote: Hi Everyone, Is anyone out there that can help me. I have two workbooks. The first workbook is open and the second workbook is close. The first workbook contains my product code and the cost. the second workbook contains my product code with the MOST up to date cost. I manage to create a macro which returns the MOST up to date cost in my first workbook. But my code is opening the second workbook. I wonder if there is a way that i dont have to open the second workbook but still manage to do the cross referencing? Please advise. Below is the code. thanks much. Sub CostUpdate() ' ' CostUpdate Macro ' Macro recorded 13/01/2009 by jeffreyst ' Application.ScreenUpdating = False Dim activeWorkbookName As String activeWorkbookName = ActiveWorkbook.Name Workbooks.Open Filename:="G:\Commercial Auckland\Estimating Dept\Bpcs Cost\z UpdatedCost.xls" Dim active As Workbook Dim sourceRow As Long Dim destination As Long Dim code As Long Dim cost As Double sourceRow = Sheets("Sheet1").Range("a65536").End(xlUp).Row For i = 2 To sourceRow Windows("z UpdatedCost.xls").Activate code = Range("a" & i).Value cost = Range("c" & i).Value Windows(activeWorkbookName).Activate destination = Sheets("MaterialDatabase").Range("b65536").End (xlUp).Row Dim cont As Boolean Dim count As Integer cont = True count = 3 Do While cont Dim matchCode As Long matchCode = Sheets("MaterialDatabase").Range("B" & count).Value If matchCode = code Then Sheets("MaterialDatabase").Range("C" & count).Value = cost cont = False End If count = count + 1 If count destination Then cont = False End If Loop Next i Windows("z UpdatedCost.xls").Activate 'Application.CutCopyMode = False ActiveWorkbook.Close End Sub |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross referencing two workbooks?
Gord,
Would the code in that link work if the source file was open on a different PC on the same network? "Gord Dibben" wrote: Have a look at Ron de Bruin's code to retrieve data from a closed workbook. Adapt to suit. http://www.rondebruin.nl/copy7.htm On Sun, 18 Jan 2009 10:30:12 -0800 (PST), Jeffrey wrote: Hi Everyone, Is anyone out there that can help me. I have two workbooks. The first workbook is open and the second workbook is close. The first workbook contains my product code and the cost. the second workbook contains my product code with the MOST up to date cost. I manage to create a macro which returns the MOST up to date cost in my first workbook. But my code is opening the second workbook. I wonder if there is a way that i dont have to open the second workbook but still manage to do the cross referencing? Please advise. Below is the code. thanks much. Sub CostUpdate() ' ' CostUpdate Macro ' Macro recorded 13/01/2009 by jeffreyst ' Application.ScreenUpdating = False Dim activeWorkbookName As String activeWorkbookName = ActiveWorkbook.Name Workbooks.Open Filename:="G:\Commercial Auckland\Estimating Dept\Bpcs Cost\z UpdatedCost.xls" Dim active As Workbook Dim sourceRow As Long Dim destination As Long Dim code As Long Dim cost As Double sourceRow = Sheets("Sheet1").Range("a65536").End(xlUp).Row For i = 2 To sourceRow Windows("z UpdatedCost.xls").Activate code = Range("a" & i).Value cost = Range("c" & i).Value Windows(activeWorkbookName).Activate destination = Sheets("MaterialDatabase").Range("b65536").End (xlUp).Row Dim cont As Boolean Dim count As Integer cont = True count = 3 Do While cont Dim matchCode As Long matchCode = Sheets("MaterialDatabase").Range("B" & count).Value If matchCode = code Then Sheets("MaterialDatabase").Range("C" & count).Value = cost cont = False End If count = count + 1 If count destination Then cont = False End If Loop Next i Windows("z UpdatedCost.xls").Activate 'Application.CutCopyMode = False ActiveWorkbook.Close End Sub |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Cross referencing two workbooks?
Beyond my skills to answer that.
Others can answer. Maybe someone will? Gord On Sun, 18 Jan 2009 14:42:21 -0800, h2fcell wrote: Gord, Would the code in that link work if the source file was open on a different PC on the same network? "Gord Dibben" wrote: Have a look at Ron de Bruin's code to retrieve data from a closed workbook. Adapt to suit. http://www.rondebruin.nl/copy7.htm On Sun, 18 Jan 2009 10:30:12 -0800 (PST), Jeffrey wrote: Hi Everyone, Is anyone out there that can help me. I have two workbooks. The first workbook is open and the second workbook is close. The first workbook contains my product code and the cost. the second workbook contains my product code with the MOST up to date cost. I manage to create a macro which returns the MOST up to date cost in my first workbook. But my code is opening the second workbook. I wonder if there is a way that i dont have to open the second workbook but still manage to do the cross referencing? Please advise. Below is the code. thanks much. Sub CostUpdate() ' ' CostUpdate Macro ' Macro recorded 13/01/2009 by jeffreyst ' Application.ScreenUpdating = False Dim activeWorkbookName As String activeWorkbookName = ActiveWorkbook.Name Workbooks.Open Filename:="G:\Commercial Auckland\Estimating Dept\Bpcs Cost\z UpdatedCost.xls" Dim active As Workbook Dim sourceRow As Long Dim destination As Long Dim code As Long Dim cost As Double sourceRow = Sheets("Sheet1").Range("a65536").End(xlUp).Row For i = 2 To sourceRow Windows("z UpdatedCost.xls").Activate code = Range("a" & i).Value cost = Range("c" & i).Value Windows(activeWorkbookName).Activate destination = Sheets("MaterialDatabase").Range("b65536").End (xlUp).Row Dim cont As Boolean Dim count As Integer cont = True count = 3 Do While cont Dim matchCode As Long matchCode = Sheets("MaterialDatabase").Range("B" & count).Value If matchCode = code Then Sheets("MaterialDatabase").Range("C" & count).Value = cost cont = False End If count = count + 1 If count destination Then cont = False End If Loop Next i Windows("z UpdatedCost.xls").Activate 'Application.CutCopyMode = False ActiveWorkbook.Close End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
cross referencing 2 workbooks at same time | Excel Worksheet Functions | |||
Cross Referencing | Excel Discussion (Misc queries) | |||
Cross referencing | Excel Worksheet Functions | |||
Cross referencing data across different workbooks | Excel Discussion (Misc queries) | |||
cross referencing | Excel Worksheet Functions |