Voucher data flow through VBA

[Zubair]

Hi experts,

Please help to fix the following.

1. If Tab "Voucher" Cell H4 = "Payment" then D11 = O2
2. If Tab "Voucher" Cell H4 = "Credit" then D11 = O3, and "Database" Column H figure required with a negative sign (example -5,240)
3. O2 & O3 is showing the starting document number, which required plus 1 on each new entry
4. It is required to check "Database" column A also to avoid duplication & maintain sequence without skipping serial like incase of re-set.

Voucher

Voucher.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1														Credit voucher	CV
2				Voucher Entry Form										PV	70000
3														CV	90000
4							Nature
5
6					Customer Name					Voucher No.
7
8					Customer No					Voucher Date
9
10				S. No.	Against Invoice No.	Invoice Date			Cash		Amount
11
12
13
14
15
16
17
18
19
20
21
22				Total							-
23
24
25
26
27
28
Voucher

Cell Formulas
Range	Formula
N1	N1	=IF(H4="Payment","Payment voucher","Credit voucher")
O1	O1	=IF(H4="Payment","PV","CV")
E6	E6	=IF($H$4="Payment","Supplier Name","Customer Name")
E8	E8	=IF($H$4="Payment","Supplier No","Customer No")
G10	G10	=IF(I10="Cheque No.","Bank Name","")
H10	H10	=IF(I10="Cheque No.","Branch Name","")
J10	J10	=IF(I10="Cheque No.","Cheque date","")
K22	K22	=SUM(K11:K21)

Cells with Data Validation
Cell	Allow	Criteria
H4	List	Payment, Credit
I10	List	Cheque No., Cash

Database

Voucher.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1	Document No.	Document type	Transaction	Supplier/Customer No.	Supplier/Customer Name	Voucher No.	Voucher Date	Amount	Transaction Date	Nature	Against Invoice No.	Invoice Date	Bank Name	Branch Name	Cheque No.	Cheque Date
2	70000	PV	Payment voucher	100	ABC	101	1/1/2022	56,852	12/5/2022 22:54	Payment	JK-9855	1/1/2022	Bank of A	Teflon	2562521	2/2/2022
3	70000	PV	Payment voucher	100	ABC	101	1/1/2022	58,000	12/5/2022 22:54	Payment	JK-9859	2/1/2022	Bank of C	Main Road Branch	5658545	3/2/2022
4	90000	CV	Credit voucher	1001	SSS	501	9/5/2022	3,520	12/5/2022 22:45	Credit	2004	8/5/2022	Bank No.2	Branch No.4	9000424	9/7/2022
5	90001	CV	Credit voucher	1000	AAA	500	1/1/2022	56,200	12/5/2022 22:51	Credit	2005	1/1/2022	Bank No.4	Branch No.5	9000420	9/7/2022
6	90001	CV	Credit voucher	1000	AAA	500	1/1/2022	56,890	12/5/2022 22:51	Credit	2006	2/1/2022	Bank No.5	Branch No.2	9000455	10/7/2022
7	70001	PV	Payment voucher	501	DEF	105	3/3/2000	4,000	12/5/2022 22:56	Payment	JK-9000	2/2/2000			Cash
8	90002	CV	Credit voucher	1010	CCC	510	5/6/2022	5,240	13-05-2022 09:46:29	Credit	2010	7/9/2022			Cash
9	90003	CV	Credit voucher	1012	UUU	520	19/05/2022	650	13-05-2022 12:02:59	Credit	213	8/9/2022			Cash
10
11
Database

VBA
Sub SaveNewDataVoucher()
Application.ScreenUpdating = False
Dim LastRow As Long, vouWS As Worksheet, desWS As Worksheet, brand As Range
Set vouWS = Sheets("Voucher")
Set desWS = Sheets("Database")

With vouWS

For Each brand In .Range("E11", .Range("E" & .Rows.Count).End(xlUp))
LastRow = desWS.Range("E" & .Rows.Count).End(xlUp).Row + 1

desWS.Range("A" & LastRow).Resize(, 1).Value = Array(.Range("D11"))
desWS.Range("B" & LastRow).Resize(, 2).Value = Array(.Range("O1"), .Range("N1"))
desWS.Range("D" & LastRow).Resize(, 4).Value = Array(.Range("F8"), .Range("F6"), .Range("K6"), .Range("K8"))
desWS.Range("K" & LastRow).Resize(, 6).Value = .Range("E" & brand.Row).Resize(, 6).Value
desWS.Range("H" & LastRow).Value = .Range("K" & brand.Row)
desWS.Range("I" & LastRow).Resize(, 1).Value = Array([Text(Now(), "DD-MM-YYYY HH:MM:SS")])
desWS.Range("J" & LastRow).Resize(, 1).Value = Array(.Range("H4"))


Next brand
End With
Call ResetVoucher
Application.ScreenUpdating = True
End Sub

Sub ResetVoucher()
Application.ScreenUpdating = False
Dim vouWS As Worksheet, desWS As Worksheet
Set vouWS = Sheets("Voucher")
Set desWS = Sheets("Database")
With vouWS
.Range("H4,F6,F8,K6,K8").Interior.Color = xlNone
.Range("H4,F6,F8,K6,K8").Value = ""
.Range("E11:K21").Interior.Color = xlNone
.Range("E11:K21").Value = ""
.Range("E11,F21").Interior.Color = xlNone
.Range("D11,F21").Value = ""
End With
Application.ScreenUpdating = True
End Sub

 

[Zubair]

Any help !!