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

[GijoeBlack]

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 in Worksheet (1) of Workbook(A) in Worksheet (2) of Workbook (B). If cell value 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.

 

[Zot]

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]

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]

Dont wont to be a pest but can anyone shed some light. Thank you in advance.

 

[Zot]

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]

Hey @Zot

Surething. I will try to upload a sample sheet.

 

[GijoeBlack]

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
	A	B	C	D	E	F	G	H	I
1
2	Invoice
3
4								Invoice Number:	54330
5								Invoice Date:
6								Customer ID:	109
7								Agreement #:
8								Purchase Order #:
9								DUE DATE:
10	To							Payment Method:
11								Sales/Agent:
12								TOTAL:	$ 100.00
13
14
15	Email
16
17	Item ID	Description			Qty	UOM		Rate	Amount
18	0001	Test Item			1	EACH		$ 100.00	$ 100.00
19
20
21
22
23
24
25
26	Notes:							Sub Total:	$ 100.00
27
28								Grand Total:	$ 100.00
29		Thank you for your Business!
Invoice

Cell Formulas
Range	Formula
I4	I4	='INV Summary'!C11
I12	I12	=I28
I26	I26	=SUM(I18:I25)
I28	I28	=I26

Cells with Data Validation
Cell	Allow	Criteria
F18:F25	List	EACH|MIN

Target Worksheet/Workbook:

Test.xlsx
	A	B	C	D	E	F
1	CustID	Date	Invoice #	Cust Details	Description	Amount
2	100	2021-09-27	54321
3	101	2021-09-28	54322
4	102	2021-09-29	54323
5	103	2021-09-30	54324
6	104	2021-10-01	54325
7	105	2021-10-02	54326
8	106	2021-10-03	54327
9	107	2021-10-04	54328
10	108	2021-10-05	54329
11	109	2021-10-06	54330			$ 100.00
INV Summary

Cell Formulas
Range	Formula
B2	B2	=TODAY()
B3:B11	B3	=B2+1
F11	F11	=Invoice!I28

 

[Zot]

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

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]

@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]

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.