Populate data based on matching last part with sheet name across sheets

[Ali M]

Hi
in DAILY sheet I have items contains sheet name in last part example (FROM ALI , FROM OMAR ,....)
and I have sheets names ALI and OMAR so I want matching in last PART in the item in column B with sheets names based on DATE & AMOUNT together even duplicates the same amount and date for the same name should brings . if I have the items in DAILY sheet for CUSTOMER NAME doesn't exist in CUSTOMER sheet based on matching date, amount and last part the item with sheet name then should copy to sheet is relation of it as in rows (2,3,5,78,9) in DAILY sheet (those are not existed in ALI, OMAR sheet ) so should add to them and delete FROM ALI, FROM OMAR) , I don't need it to show in customers sheets .
if I have the items in CUSTOMERS sheets and don't existed in DAILY sheet as in row3 for OMAR sheet and rows (4,5,6) in ALI sheet, then should copy to DAILY sheet and add in last part of the ITEM "FROM ALI, FROM OMAR based on matching date, amount and last part the item with sheet name .
any item doesn't contain the sheet name in last part in DAILY sheet should ignore it and any word "OPENING BALANCE" also should ignores from customers sheets when matching.
every time I add new sheets and change data in all of sheets so should update every time run the macro.

copy (3) (1).xlsm
	A	B	C	D	E
2	20/06/2023	PURCHASE FROM OMAR	15,000.00		15,000.00
3	21/06/2023	CASH PR FROM ALI	15,000.00		30,000.00
4	21/06/2023	STOCK1	100,000.00		130,000.00
5	22/06/2023	CASH DM FROM ALI		37,000.00	93,000.00
6	22/06/2023	EXPENSE ADMIN		65,000.00	28,000.00
7	22/06/2023	PURCHASE RETURNS FROM OMAR		14,000.00	14,000.00
8	22/06/2023	PURCHASE RETURNS FROM ALI		3,200.00	10,800.00
9	22/06/2023	PURCHASE RETURNS FROM ALI		3,200.00	7,600.00
10	22/06/2023	PURCHASE LOW	6,200.00		13,800.00
11	22/06/2023	EXPENSE PR	37,000.00		50,800.00
12	22/06/2023	SALES		201,000.00	-150,200.00
13	22/06/2023	SALES RETURNS	3,500.00		-146,700.00
14	23/06/2023	SELLING LOW		3,201.00	-149,901.00
15	24/06/2023	SALES FROM OMAR		3,202.00	-153,103.00
DAILY

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E3:E15	E3	=E2+C3-D3

copy (3) (1).xlsm
	A	B	C	D	E
1	ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE SALES	15,000.00		15,000.00
3	22/06/2023	PURCHASE RETURNS		4,000.00	11,000.00
4	24/06/2023	SALES		3,202.00	7,798.00
5		TOTAL	15,000.00	7,202.00	7,798.00
OMAR

Cell Formulas
Range	Formula
E2,E5	E2	=C2-D2
E3:E4	E3	=E2+C3-D3
C5:D5	C5	=SUM(C2:C4)

copy (3) (1).xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE PURCHASE	200,000.00		200,000.00
3	21/06/2023	CASH PR	15,000.00		215,000.00
4	22/06/2023	CASH DM		30,000.00	185,000.00
5	23/06/2023	CASH DM		1,200.00	183,800.00
6	23/06/2023	CASH DM		1,200.00	182,600.00
7		TOTAL	215,000.00	32,400.00	182,600.00
ALI

Cell Formulas
Range	Formula
E2,E7	E2	=C2-D2
E3:E6	E3	=E2+C3-D3
C7:D7	C7	=SUM(C2:C6)

result in daily sheet as show in rows (6,7,8,10) brought from customers sheets.

copy (3) (1).xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	21/06/2023	CASH PR FROM ALI	15000		15000
3	21/06/2023	CASH PR FROM ALI	15000		30000
4	22/06/2023	CASH DM FROM ALI		37000	-7000
5	22/06/2023	EXPENSE ADMIN		65000	-72000
6	22/06/2023	CASH DM FROM ALI		30000	-102000
7	23/06/2023	CASH DM FROM ALI		1200	-103200
8	23/06/2023	CASH DM FROM ALI		1200	-104400
9	20/06/2023	PURCHASE FROM OMAR	15000		-89400
10	22/06/2023	PURCHASE RETURNS FROM OMAR		4000	-93400
11	21/06/2023	STOCK1	100000		10600
12	22/06/2023	PURCHASE RETURNS FROM OMAR		14000	-3400
13	22/06/2023	PURCHASE LOW	6200		2800
14	22/06/2023	EXPENSE PR	37000		39800
15	22/06/2023	SALES		201000	-161200
16	22/06/2023	SALES RETURNS	3500		-157700
17	22/06/2023	PURCHASE RETURNS FROM ALI		3200	-160900
18	22/06/2023	PURCHASE RETURNS FROM ALI		3200	-164100
19	23/06/2023	SELLING LOW		3201	-167301
20	24/06/2023	SALES FROM OMAR		3202	-170503
DAILY

Cell Formulas
Range	Formula
E2	E2	=C2-D2
E12:E20,E3:E10	E3	=E2+C3-D3
E11	E11	=E9+C11-D11

result in OMAR sheet as shows in rows 3,4 brought from DAILY sheet.

copy (3) (1).xlsm
	A	B	C	D	E
1	ITEM	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE SALES	15,000.00		15,000.00
3	20/06/2023	PURCHASE	15,000.00		30,000.00
4	22/06/2023	PURCHASE RETURNS		14,000.00	16,000.00
5	22/06/2023	PURCHASE RETURNS		4,000.00	12,000.00
6	24/06/2023	SALES		3,202.00	8,798.00
7		TOTAL	30,000.00	21,202.00	8,798.00
OMAR

Cell Formulas
Range	Formula
E2,E7	E2	=C2-D2
E3:E6	E3	=E2+C3-D3
C7:D7	C7	=SUM(C2:C6)

result in ALI sheet as in rows (4,5,6,7) brought from DAILY sheet

copy (3) (1).xlsm
	A	B	C	D	E
1	DATE	ACCOUNT NAME	DEBIT	CREDIT	BALANCE
2	20/06/2023	OPENING BALANCE PURCHASE	200,000.00		200,000.00
3	21/06/2023	CASH PR	15,000.00		215,000.00
4	21/06/2023	CASH PR	15,000.00		230,000.00
5	22/06/2023	CASH DM		37,000.00	193,000.00
6	22/06/2023	PURCHASE RETURNS		3,200.00	189,800.00
7	22/06/2023	PURCHASE RETURNS		3,200.00	186,600.00
8	22/06/2023	CASH DM		30,000.00	156,600.00
9	23/06/2023	CASH DM		1,200.00	155,400.00
10	23/06/2023	CASH DM		1,200.00	154,200.00
11		TOTAL	230,000.00	75,800.00	154,200.00
ALI

Cell Formulas
Range	Formula
E2,E11	E2	=C2-D2
E3:E10	E3	=E2+C3-D3
C11:D11	C11	=SUM(C2:C10)

thanks

 

[sijpie]

The macro can run through column B and check for "FROM" in the cell content using for instance :

    for lR = 1 to lLastRow
        If Sheets("DAILY").Cells(2,lR) Like "*FROM*" Then
            sName = trim(Right(Sheets("DAILY").Cells(2,lR), RevInstr(Sheets("DAILY").Cells(2,lR)," ",99))
'Then find the sheet with name sName and add the data to the bottom

 

[Ali M]

is it macro or what?!