SAP Journal Entries_VBA to create multiple entries

[Sttil]

Hi eveyone,

So, I need to migrate a large number of documents from legacy system into SAP by using a template for creating documents. The idea is:
1. I have a source file, in which every row corresponds to one document. There are many rows.
2. Every document consists of 11 rows in the template. The next document will be entered in rows below with same general layout.
3. Some of the cells in each document have predefined values
4. Some of the cells in each document are based on the values from the source file

This is what the source file would look like:

	A	B	C	D	E	F	G	H	I	J
1​	Company Code	Journal Entry Date	Journal entry date	Posting Date	G/L Account	Document header text	Doc Currency	Amount doc currency	Amount local currency	Fiscal Period
2​	1000​	2020-01-01​	2020-01-01​	2020-02-02	1100000​	Posting 1	EUR	100​	90​
3​	1000​	2020-01-01​	2020-01-01​	2020-02-02	2200000​	Posting 2	USD	500​	400​

I would like for the VBA to transform these two rows from source file above (2 and 3) into two document postings, consisting of 11 rows each, like below:

	A	B	C	D	E	F	G	H
1​	1	Header
2​		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS
3​		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)
4​		1000​	SA	2020-01-01​	2020-02-02​		Posting 1	EUR
5​		Line Items
6​					Transaction Currency
7​		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2
8​		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)
9​		1000​	1100000​		100​		90​
10​		1000​	9900000​			100​	90​
11​
12​	2	Header
13​		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS
14​		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)
15​		1000​	SA	2020-01-01​	2020-02-02​		Posting 2	USD
16​		Line Items
17​					Transaction Currency
18​		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2
19​		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)
20​		1000​	2200000​		500​		400​
21​		1000​	9900000​			500​	400​
22​

For the first document (rows 1-11), below is a description of how they should be constructed:
1A - running number per document, starts with 1, then 2 for next doc, etc. No data from source file
1B - predefined as "Header"
2:2 all entries predefined, nothing from source file
3:3 all entries predefined, nothing from source file
4B: pickup from source file A2
4C: predefined
4D: pickup from source file C2
4E, pickup from source file D2
4F: pickup from source file J2 (empty in this case)
4G: pickup from source file F2
4H: pickup from source file G2
5:8: all fields predefined
9:10: same logic as for line 4 entries; some predefined and some picked up from entries in row 2 of source file
11:11 blank line as delimiter to next document

Then start again with same logic on rows 12-22 for next document (line 3 in source file).

Not sure if this is too much to handle but would appreciate any help a lot!

Thanks in advance!

 

[J.Ty.]

Hi,

I have made a simple partial solution. I hope this is what you need, and that you can complete it in the same manner.
In order to make it complete you need to select first 11 rows and fill them down as a whole.

Book1
	A	B	C	D	E	F	G	H
1	1	Header
2		BUKRS	BLART	BLDAT	BUDAT	MONAT	BKTXT	WAERS
3		*Company Code (4)	*Journal Entry Type (2)	*Journal Entry Date	*Posting Date	Fiscal period (2)	Document Header Text (25)	*Transaction Currency (5)
4		1000	SA	2020-01-01	2020-02-02		Posting 1	EUR
5		Line Items
6					Transaction Currency
7		BUKRS	HKONT	SGTXT	WRSOL	WRHAB	DMBTR	DMBE2
8		Company Code (4)	G/L Account (10)	Item Text (50)	Debit	Credit	Amount in Company Code Currency	Amount in second local currency (LC2)
9		1000	1100000		100		90
10		1000	9900000			100	90
11
New system

Cell Formulas
Range	Formula
A1	A1	=1+QUOTIENT(ROW(),11)
B4	B4	=IF(ISBLANK(INDEX(Legacy!A:A,1+A1)),"",INDEX(Legacy!A:A,1+A1))
G4	G4	=IF(ISBLANK(INDEX(Legacy!F:F,1+A1)),"",INDEX(Legacy!F:F,1+A1))
C9	C9	=IF(ISBLANK(INDEX(Legacy!E:E,1+A1)),"",INDEX(Legacy!E:E,1+A1))

 

[Sttil]

Thanks a lot for your help, I will give it a try!