Copy a cell value (x) from one Workbook (A) to Workbook (B) if finds a lookup cell value (y) from Workbook (A)

GijoeBlack

New Member
Joined
Sep 22, 2021
Messages
18
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Apologies if this has been already discussed and resolved. Unfortunately, I could not find anything close to what am trying to achieve. I have attempted several ways and have not been successful. It should be noted, am a complete novice in VBA. Here is the use case, am looking to resolve in form of a VBA code.

I have Workbook (A) [Source] and Workbook (B) [Destination]. I would like to setup a macro within Workbook (A) to open Workbook (B) and copy a cell value (x) within Worksheet (1) of a Workbook (A) but look up/Find a Cell value (y) in Worksheet (1) of Workbook(A) in Worksheet (2) of Workbook (B). If cell value (y) is found, would like to Paste cell value (x) to the same row but offset by (4) columns, close Workbook (B). I may later copy more than one cell value from source to destination. But I believe if I can get this to work, I will be able to figure out the rest.

I thank you all in advance.

Joe.
 

Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,760
Office Version
  1. 2016
Platform
  1. Windows
Here is my understanding
1) Open wbA and run macro
2) Select wbB when prompted
3) Find a value in wbB
4) If found, paste cell value(x) from wbA into wbB same row as found value but offset by 4 column

Question:
1) What determine cell value(x) in wbA
2) where the value to be found in wbB comes from?
 

GijoeBlack

New Member
Joined
Sep 22, 2021
Messages
18
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Hello @Zot

Thank you for looking into this. Listed understanding excluding item (2) is true. For Item (2), instead of prompting user, the file path will be hard coded into the macro. (a path to sharepoint site)

For your queries:

1. Cell value ( x ) essentially is a 'Total Inv Amt' in current use case. Once the Invoice is Billed, the "Total Inv Amount" needs to be copied/pasted in Workbook (B) which maintains list of Inv #s
2. The lookup cell value ( y ) is the "Inv #" present in Workbook (A) [pulled from list of inv# listed in workbook (B)] where corresponding 'Total Inv Amt' will be pasted by offsetting (4) columns. For clarity, Invoice# is present in Column(3) while Total Inv Amt is Column (7) in Workbook (B).

I hope I was able to answer your question. Thank you again.
 

GijoeBlack

New Member
Joined
Sep 22, 2021
Messages
18
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Dont wont to be a pest but can anyone shed some light. Thank you in advance.
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,760
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Dont wont to be a pest but can anyone shed some light. Thank you in advance.
I think I need to see sample sheets here. Need to know how you structure your data.

If possible, can you install the XL2BB Add-in (the right most icon in reply form here. Use it to select range you want to show and capture it by clicking XL2BB tab created in your Excel and click MiniSheet. Then Paste in reply . You will see rubbish code and to actually see the actual output, click Preview. Click Preview tab again to return to writing mode.
 

GijoeBlack

New Member
Joined
Sep 22, 2021
Messages
18
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Hey @Zot

Surething. I will try to upload a sample sheet.
 

GijoeBlack

New Member
Joined
Sep 22, 2021
Messages
18
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS

ADVERTISEMENT

Here are (2) minisheets as requested. Although, I have added samples as (2) worksheet within same workbook, in reality I have them as (2) separate workbooks. If within the same workbook am able to achieve what am trying to do using "danteamor"'s [VBA Help - Index match, then copy/paste) code. As soon as this is replaced with external workbook, it fails to do the "find" from the target workbook (b). Let me know if you need anything else.

Source worksheet/workbook:
Test.xlsx
ABCDEFGHI
1
2Invoice
3
4Invoice Number:54330
5Invoice Date:
6Customer ID:109
7Agreement #:
8Purchase Order #:
9DUE DATE:
10ToPayment Method:
11Sales/Agent:
12TOTAL:$ 100.00
13
14
15Email
16
17Item IDDescriptionQtyUOMRateAmount
180001Test Item1EACH$ 100.00$ 100.00
19
20
21
22
23
24
25
26Notes:Sub Total:$ 100.00
27
28Grand Total:$ 100.00
29Thank you for your Business!
Invoice
Cell Formulas
RangeFormula
I4I4='INV Summary'!C11
I12I12=I28
I26I26=SUM(I18:I25)
I28I28=I26
Cells with Data Validation
CellAllowCriteria
F18:F25ListEACH|MIN


Target Worksheet/Workbook:
Test.xlsx
ABCDEF
1CustIDDateInvoice #Cust DetailsDescriptionAmount
21002021-09-2754321
31012021-09-2854322
41022021-09-2954323
51032021-09-3054324
61042021-10-0154325
71052021-10-0254326
81062021-10-0354327
91072021-10-0454328
101082021-10-0554329
111092021-10-0654330$ 100.00
INV Summary
Cell Formulas
RangeFormula
B2B2=TODAY()
B3:B11B3=B2+1
F11F11=Invoice!I28
 

Zot

Well-known Member
Joined
Nov 26, 2020
Messages
1,760
Office Version
  1. 2016
Platform
  1. Windows
Sorry. I was busy today and able to do at the end of the day now.

For the sheet name I just follow your example: Invoice for the Book Source, INV Summary for the Book Target.

I created 2 workbooks, one for wbSource and one for wbTarget. The code is in wbSource as required.

Run macro and it will ask for wbTarget. Then all done. Right now I hard coded the address for Invoice Number and the Amount to copy. You can see in the code. The addresses follow your sample. I have no idea if you want to search for it automatically or not since you need to search for keyword.

Here's the code
VBA Code:
Sub WriteInvAmt()

Dim NoInv As Long
Dim rngInv As Range, rngFound As Range
Dim wsSource As Worksheet, wsTarget As Worksheet
Dim wbSource As Workbook, wbTarget As Workbook

Set wbSource = ActiveWorkbook
Set wsSource = wbSource.Sheets("Invoice")

Application.ScreenUpdating = False

' Select workbook Target
Fname = Application.GetOpenFilename(FileFilter:="Excel Files (*.xls; *.xlsx; *.xlsm; *.xlsb), *.xls; *.xlsx; *.xlsm; *.xlsb", Title:="Select a File")
If Fname = False Then Exit Sub                         'CANCEL is clicked

Set wbTarget = Workbooks.Open(Filename:=Fname, UpdateLinks:=False, IgnoreReadOnlyRecommended:=True)
Set wsTarget = wbTarget.Sheets("INV Summary")

' Set range of Invoice numbers in wsTarget
Set rngInv = wsTarget.Range("C2", wsTarget.Cells(Rows.Count, "C").End(xlUp))

' Get Invoice number in wsSource
NoInv = wsSource.Range("I4")

' Find matching invoice number in wsTarget
Set rngFound = rngInv.Find(What:=NoInv, LookAt:=xlWhole)
If Not rngFound Is Nothing Then
    rngFound.Offset(0, 3) = wsSource.Range("I12")
End If
' Save and Close wbTarget
wbTarget.Close True

End Sub
 

GijoeBlack

New Member
Joined
Sep 22, 2021
Messages
18
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
@Zot
Thank you so much for your help on this. I would like to request one change though, I don't want user to be prompted to Select target workbook file. Like to be hardcoded. The only thing user needs to know if the cell was copied else prompt with the reason/error when macro is run. Is this doable?

Thanks again in advance. In the meantime, I will dry run the above code and make necessary changes to reflect actual prod workbooks.
 

GijoeBlack

New Member
Joined
Sep 22, 2021
Messages
18
Office Version
  1. 365
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Another point I missed in my previous response, (which I can no longer edit) is that as indicated in my original request, Inv value (I4 in my example) needs to be searched in workbook (b), once found, the pasting value for Inv Total will need to be offsetted to be pasted in Amt column for that Inv. Let me know if that makes sense. Thank you again.
 

Forum statistics

Threads
1,143,906
Messages
5,721,430
Members
422,361
Latest member
Kelvin Kiplangat

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top