RAECH
03-22-2021, 01:01 AM
Hi Everyone
I need to split a large range of data from approx. 950 different cost centres into a text-file for each cost centre. I need to extract the data from each account and posting period on each of our cost centre into - so that it can be uploaded into our ERP.
My data looks like this:
Posting period
Oct
Nov
Dec
Jan
ActualsYTDZ42020
ActualsYTDZ42020
ActualsYTDZ42020
ActualsYTDZ42020
Cost center
0
Account No.
* 1.000
* 1.000
* 1.000
* 1.000
10000
General Group HQ
5500
Cons, Packaging
0
0
1,37573
0
10000
General Group HQ
6000
Salary
436,8968
424,9079
402,8012
453,2055
10000
General Group HQ
6003
Pension
34,95174
33,99263
32,2241
37,14348
10001
The house of Hennevej
6527
Electrical items
0
0
1,94516
2,81275
10001
The house of Hennevej
6528
Lubric oil & grease
0,3079
0,37291
0
0
10003
Group HR
6018
Ref wage/unempl. all
0
0
0
0
10005
Visitors Horsens
6570
Roadtax
0
0
0
2,19
I have various amounts of account numbers for each cost center. The text file I need to generate start with something like this:
Where for each generated text file, it writes the cost center in the "header". The account numbers goes in "Cost Element" and each posting period corresponding to period 1-12 in below set-up.
Version
Z87
Fiscal Year
2021
Cost Center
10000
1
2
3
4
5
6
7
8
9
10
11
12
Cost element
Period 1
Period 2
Period 3
Period 4
Period 5
Period 6
Period 7
Period 8
Period 9
Period 10
Period 11
Period 12
I am aware that is probably is a big tasks.. And something that takes a long time to run. In total I have 10190 account lines that I need to split up on cost centres.
I have tried a few different things - for example codes similar to below.. Which I to some extent can make work - but probably is not the best way to do this..
a = 0For a = 0 To 100
If ActiveCell.Value = "" Then Exit For
ActiveCell.Offset(0, 2).Resize(1, 14).Copy
ActiveCell.Offset(0, 20).PasteSpecial xlPasteValues
Application.CutCopyMode = False
ActiveCell.Offset(1, -20).Activate
But in this task from my employer I really am out of my league.. So I hope that someone can put me in the right direction - as I would also like to learn how a proper set up for something like this could be.
Best regards
Rasmus
I need to split a large range of data from approx. 950 different cost centres into a text-file for each cost centre. I need to extract the data from each account and posting period on each of our cost centre into - so that it can be uploaded into our ERP.
My data looks like this:
Posting period
Oct
Nov
Dec
Jan
ActualsYTDZ42020
ActualsYTDZ42020
ActualsYTDZ42020
ActualsYTDZ42020
Cost center
0
Account No.
* 1.000
* 1.000
* 1.000
* 1.000
10000
General Group HQ
5500
Cons, Packaging
0
0
1,37573
0
10000
General Group HQ
6000
Salary
436,8968
424,9079
402,8012
453,2055
10000
General Group HQ
6003
Pension
34,95174
33,99263
32,2241
37,14348
10001
The house of Hennevej
6527
Electrical items
0
0
1,94516
2,81275
10001
The house of Hennevej
6528
Lubric oil & grease
0,3079
0,37291
0
0
10003
Group HR
6018
Ref wage/unempl. all
0
0
0
0
10005
Visitors Horsens
6570
Roadtax
0
0
0
2,19
I have various amounts of account numbers for each cost center. The text file I need to generate start with something like this:
Where for each generated text file, it writes the cost center in the "header". The account numbers goes in "Cost Element" and each posting period corresponding to period 1-12 in below set-up.
Version
Z87
Fiscal Year
2021
Cost Center
10000
1
2
3
4
5
6
7
8
9
10
11
12
Cost element
Period 1
Period 2
Period 3
Period 4
Period 5
Period 6
Period 7
Period 8
Period 9
Period 10
Period 11
Period 12
I am aware that is probably is a big tasks.. And something that takes a long time to run. In total I have 10190 account lines that I need to split up on cost centres.
I have tried a few different things - for example codes similar to below.. Which I to some extent can make work - but probably is not the best way to do this..
a = 0For a = 0 To 100
If ActiveCell.Value = "" Then Exit For
ActiveCell.Offset(0, 2).Resize(1, 14).Copy
ActiveCell.Offset(0, 20).PasteSpecial xlPasteValues
Application.CutCopyMode = False
ActiveCell.Offset(1, -20).Activate
But in this task from my employer I really am out of my league.. So I hope that someone can put me in the right direction - as I would also like to learn how a proper set up for something like this could be.
Best regards
Rasmus