Inventory transfer through VBA

[Zubair]

Mr. Excel.xlsx
	A	B	C	D	E	F	G	H	I	J
1	Inventory Data Form
2	Transaction	Inventory Transfer
3	Document No.	5001
4	Date	22/02/2022
5
6	Location From	Factory 1
7	Location To	Factory 2
8
9	Brand	Size	Quantity	Rate	Amount
10	ABC	21	25	60	1,500
11	CDE	29	12	55	660
12
13
14	Data base
15	Transaction	Document No.	Date	Location From	Location To	Brand	Size	Quantity	Rate	Amount
16	Opening stock				Factory 1	ABC	21	100	60	6,000
17	Opening stock				Factory 1	CDE	29	50	55	2,750
18
19	Inventory Transfer	5001	22/02/2022	Factory 1		ABC	21	-25	60	(1,500)
20	Inventory Transfer	5001	22/02/2022		Factory 2	ABC	21	25	60	1,500
21	Inventory Transfer	5001	22/02/2022	Factory 1		CDE	29	-12	55	(660)
22	Inventory Transfer	5001	22/02/2022		Factory 2	CDE	29	12	55	660
23
24	Closing stock				Factory 1	ABC	21	75	60	4,500
25	Closing stock				Factory 2	ABC	21	25	60	1,500
26	Closing stock				Factory 1	CDE	29	38	55	2,090
27	Closing stock				Factory 2	CDE	29	12	55	660
Inventory transfer

Cell Formulas
Range	Formula
J24:J27,J19:J22,J16:J17,E10:E11	E10	=+C10*D10
D19	D19	=+B6
E20	E20	=+B7
D21	D21	=+B6
E22	E22	=+B7
H24	H24	=+H16+H19
H25,H27	H25	=+H20
H26	H26	=+H17+H21

Hi,

I need VBA to record transfer stock from Factory 1 to Factory 2 in tab "database" through Inventory Data Form.
please help

 

[sijpie]

Hi Zubair,

please explain more clearly. I see in the rows 19 to 22 there are items to be transferred from factory1 to factory2. What do you need in the tab 'database ' to happen?

• Do these lines need to be added to the bottom? or something else?
• Do lines 19-22 then need to be cleared from the first sheet?
• Other actions?

 

[Zubair]

Hi sijpie,

Yes lines to be added at the bottom
Yes after transfer lines Inventory Data Form to be cleared.
Please keep in mind positive and negative transactions as shown in lines 19-22 as an example, I have put manually to show how I want.

Thanks in Advance.

 

[Zubair]

Experts any idea !

 

[sijpie]

Maybe I can do this coming week, else it will be the week after

 

[Zubair]

Thanks in Advance

 

[sijpie]

Hi Zubair, a few questions:
1. is your database on the same sheet as the form?
if so, would you consider moving it to a separate sheet? That is far safer for your data.
else, what is the sheet name for the data base?

2. I am assuming that the number of items to be moved can be more than two, so line 10-11 could extend downwards

3. Here the transaction is a inventory transfer. Are there any other transaction types possible?

4. In the database you have opening stock, closing stock and in between inventory transfer. The next inventory transfer (or any transaction) is to come below the last transaction, above the first closing stock line. Correct?

 

[Zubair]

Hi Zubair, a few questions:
1. is your database on the same sheet as the form?
if so, would you consider moving it to a separate sheet? That is far safer for your data.
else, what is the sheet name for the data base?

2. I am assuming that the number of items to be moved can be more than two, so line 10-11 could extend downwards

3. Here the transaction is a inventory transfer. Are there any other transaction types possible?

4. In the database you have opening stock, closing stock and in between inventory transfer. The next inventory transfer (or any transaction) is to come below the last transaction, above the first closing stock line. Correct?

1. Separate tab with same name "Database"
2. Yes, I have given example i.e. in Inventory data form line 10 is appearing in database lines 19 & 20 (minus Location from and plus in Location to) similarly line 11 appears in 21 & 22.
3. No in this program we are only maintaining movement of stock between 4 locations Factory 1, Factory 2, Warehouse 1 & Warehouse 2.
4. Yes Opening & closing stock mentioned only for your understanding purpose, the basic requirement is to move positive & negative figures for a single transaction.

 

[sijpie]

Ok, will continue with it later in the week or next week

 

[Zubair]

Ok, will continue with it later in the week or next week

ok thanks