Act. per 8</SPAN> | Plan. per 8</SPAN> | Var. per 8</SPAN> | Cost centers/Cost elements</SPAN> | Act per 01 - 8</SPAN> | Plan version 3</SPAN> |
1769</SPAN> | 41666,66</SPAN> | -39897,66</SPAN> | 591202 Customer magazines, production</SPAN> | 453677</SPAN> | 500000</SPAN> |
-140</SPAN> | 0</SPAN> | -140</SPAN> | 591816 Customer magazines</SPAN> | -2520</SPAN> | 0</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 625001 Postal expenses</SPAN> | -1499990,5</SPAN> | 0</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 655901 Other consultant fees</SPAN> | 217271</SPAN> | 0</SPAN> |
148</SPAN> | 0</SPAN> | 148</SPAN> | 659202 Hired Services Elanders (Publ Store)</SPAN> | 752</SPAN> | 0</SPAN> |
1777</SPAN> | 41666,66</SPAN> | -39889,66</SPAN> | * 19065 Customer magazines</SPAN> | -830810,5</SPAN> | 500000</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 564901 Demo/test vehicle miscellaneous</SPAN> | 107138</SPAN> | 0</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | * 19066 VT Press Test</SPAN> | 107138</SPAN> | 0</SPAN> |
0</SPAN> | 4166,66</SPAN> | -4166,66</SPAN> | 591101 Daily press media space</SPAN> | 0</SPAN> | 50000</SPAN> |
0</SPAN> | 41666,66</SPAN> | -41666,66</SPAN> | 591201 Magazine media space</SPAN> | 329504,25</SPAN> | 500000</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 591802 Advertising</SPAN> | 10000</SPAN> | 0</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 595002 POS production</SPAN> | 13158</SPAN> | 0</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 597102 Photos production</SPAN> | 1500</SPAN> | 0</SPAN> |
0</SPAN> | 4583,34</SPAN> | -4583,34</SPAN> | 597401 Internet</SPAN> | 0</SPAN> | 55000</SPAN> |
0</SPAN> | 8333,34</SPAN> | -8333,34</SPAN> | 599701 Other advertising costs</SPAN> | 136700</SPAN> | 100000</SPAN> |
0</SPAN> | 8333,34</SPAN> | -8333,34</SPAN> | 615001 Printed matter</SPAN> | 11406</SPAN> | 100000</SPAN> |
0</SPAN> | 67083,34</SPAN> | -67083,34</SPAN> | * 19067 VT Advertising</SPAN> | 502268,25</SPAN> | 805000</SPAN> |
0</SPAN> | 2083,34</SPAN> | -2083,34</SPAN> | 564901 Demo/test vehicle miscellaneous</SPAN> | 25120</SPAN> | 25000</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 581003 Tickets other countries</SPAN> | 0</SPAN> | 0</SPAN> |
14290</SPAN> | 1250</SPAN> | 13040</SPAN> | 597102 Photos production</SPAN> | 14290</SPAN> | 15000</SPAN> |
0</SPAN> | 28750</SPAN> | -28750</SPAN> | 598401 Sponsorship</SPAN> | 326058,2</SPAN> | 345000</SPAN> |
0</SPAN> | 0</SPAN> | 0</SPAN> | 609801 Other sales costs</SPAN> | 173,41</SPAN> | 0</SPAN> |
25119,13</SPAN> | 0</SPAN> | 25119,13</SPAN> | 783501 Depreciation of cars</SPAN> | 25119,13</SPAN> | 0</SPAN> |
39409,13</SPAN> | 32083,34</SPAN> | 7325,79</SPAN> | * 19069 VT PR/Media</SPAN> | 390760,74</SPAN> | 385000</SPAN> |
Do the headings actually repeat like in your first example?
Sub Test()
Dim ShNew As Worksheet
Dim LastRow As Long
Dim First As Long
Dim r As Long
Dim i As Long
Set ShNew = Worksheets.Add
ShNew.Range("A1:I1").Value = Array("Act. per 8", "Plan. per 8", "Var. per 8", "Cost center", "Cost center text", "Cost element", "Cost element text", "Act per 01 - 8", "Plan version 3")
First = 2
r = 2
With Worksheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Left(.Range("D" & i).Value, 1) <> "*" Then
.Range("A" & i).Resize(, 3).Copy ShNew.Range("A" & r)
ShNew.Range("F" & r).Value = Left(.Range("D" & i).Value, 6)
ShNew.Range("G" & r).Value = Mid(.Range("D" & i).Value, 8)
.Range("E" & i).Resize(, 2).Copy ShNew.Range("H" & r)
r = r + 1
Else
ShNew.Range("D" & First & ":D" & r - 1).Value = Mid(Trim(.Range("D" & i).Value), 3, 5)
ShNew.Range("E" & First & ":E" & r - 1).Value = Mid(Trim(.Range("D" & i).Value), 9)
First = r
End If
Next i
End With
End Sub
Act. per 8</SPAN> | Plan. per 8</SPAN> | Var. per 8</SPAN> | Cost center</SPAN> | Cost center text</SPAN> | Cost element</SPAN> | Cost element text</SPAN> | Act per 01 - 8</SPAN> | Plan version 3</SPAN> |
4 166,66</SPAN> | 4 166,66-</SPAN> | 1906</SPAN> | VT Advertising</SPAN> | 591</SPAN> | 01 Daily press media space</SPAN> | 50 000,00</SPAN> | ||
41 666,66</SPAN> | 41 666,66-</SPAN> | 1906</SPAN> | VT Advertising</SPAN> | 591</SPAN> | 01 Magazine media space</SPAN> | 329 504,25</SPAN> | 500 000,00</SPAN> | |
1906</SPAN> | VT Advertising</SPAN> | 591</SPAN> | 02 Advertising</SPAN> | 10 000,00</SPAN> | ||||
1906</SPAN> | VT Advertising</SPAN> | 595</SPAN> | 02 POS production</SPAN> | 13 158,00</SPAN> | ||||
1906</SPAN> | VT Advertising</SPAN> | 597</SPAN> | 02 Photos production</SPAN> | 1 500,00</SPAN> | ||||
4 583,34</SPAN> | 4 583,34-</SPAN> | 1906</SPAN> | VT Advertising</SPAN> | 597</SPAN> | 01 Internet</SPAN> | 55 000,00</SPAN> | ||
8 333,34</SPAN> | 8 333,34-</SPAN> | 1906</SPAN> | VT Advertising</SPAN> | 599</SPAN> | 01 Other advertising costs</SPAN> | 136 700,00</SPAN> | 100 000,00</SPAN> | |
8 333,34</SPAN> | 8 333,34-</SPAN> | 1906</SPAN> | VT Advertising</SPAN> | 615</SPAN> | 01 Printed matter</SPAN> | 11 406,00</SPAN> | 100 000,00</SPAN> | |
2 083,34</SPAN> | 2 083,34-</SPAN> | 1906</SPAN> | VT PR/Media</SPAN> | 564</SPAN> | 01 Demo/test vehicle miscellaneous</SPAN> | 25 120,00</SPAN> | 25 000,00</SPAN> | |
1906</SPAN> | VT PR/Media</SPAN> | 581</SPAN> | 03 Tickets other countries</SPAN> | |||||
14 290,00</SPAN> | 1 250,00</SPAN> | 13 040,00</SPAN> | 1906</SPAN> | VT PR/Media</SPAN> | 597</SPAN> | 02 Photos production</SPAN> | 14 290,00</SPAN> | 15 000,00</SPAN> |
28 750,00</SPAN> | 28 750,00-</SPAN> | 1906</SPAN> | VT PR/Media</SPAN> | 598</SPAN> | 01 Sponsorship</SPAN> | 326 058,20</SPAN> | 345 000,00</SPAN> | |
1906</SPAN> | VT PR/Media</SPAN> | 609</SPAN> | 01 Other sales costs</SPAN> | 173,41</SPAN> | ||||
25 119,13</SPAN> | 25 119,13</SPAN> | 1906</SPAN> | VT PR/Media</SPAN> | 783</SPAN> | 01 Depreciation of cars</SPAN> | 25 119,13</SPAN> |
Excel 2010 | |||||||||||
---|---|---|---|---|---|---|---|---|---|---|---|
A | B | C | D | E | F | G | H | I | |||
1 | Act. per 8 | Plan. per 8 | Var. per 8 | Cost center | Cost center text | Cost element | Cost element text | Act per 01 - 8 | Plan version 3 | ||
2 | 1769 | 41666.66 | -39897.66 | 19065 | Customer magazines | 591202 | Customer magazines. production | 453677 | 500000 | ||
3 | -140 | 0 | -140 | 19065 | Customer magazines | 591816 | Customer magazines | -2520 | 0 | ||
4 | 0 | 0 | 0 | 19065 | Customer magazines | 625001 | Postal expenses | -1499990.5 | 0 | ||
5 | 0 | 0 | 0 | 19065 | Customer magazines | 655901 | Other consultant fees | 217271 | 0 | ||
6 | 148 | 0 | 148 | 19065 | Customer magazines | 659202 | Hired Services Elanders (Publ Store) | 752 | 0 | ||
7 | 0 | 0 | 0 | 19066 | VT Press Test | 564901 | Demo/test vehicle miscellaneous | 107138 | 0 | ||
8 | 0 | 4166.66 | -4166.66 | 19067 | VT Advertising | 591101 | Daily press media space | 0 | 50000 | ||
9 | 0 | 41666.66 | -41666.66 | 19067 | VT Advertising | 591201 | Magazine media space | 329504.25 | 500000 | ||
10 | 0 | 0 | 0 | 19067 | VT Advertising | 591802 | Advertising | 10000 | 0 | ||
11 | 0 | 0 | 0 | 19067 | VT Advertising | 595002 | POS production | 13158 | 0 | ||
12 | 0 | 0 | 0 | 19067 | VT Advertising | 597102 | Photos production | 1500 | 0 | ||
13 | 0 | 4583.34 | -4583.34 | 19067 | VT Advertising | 597401 | Internet | 0 | 55000 | ||
14 | 0 | 8333.34 | -8333.34 | 19067 | VT Advertising | 599701 | Other advertising costs | 136700 | 100000 | ||
15 | 0 | 8333.34 | -8333.34 | 19067 | VT Advertising | 615001 | Printed matter | 11406 | 100000 | ||
16 | 0 | 2083.34 | -2083.34 | 19069 | VT PR/Media | 564901 | Demo/test vehicle miscellaneous | 25120 | 25000 | ||
17 | 0 | 0 | 0 | 19069 | VT PR/Media | 581003 | Tickets other countries | 0 | 0 | ||
18 | 14290 | 1250 | 13040 | 19069 | VT PR/Media | 597102 | Photos production | 14290 | 15000 | ||
19 | 0 | 28750 | -28750 | 19069 | VT PR/Media | 598401 | Sponsorship | 326058.2 | 345000 | ||
20 | 0 | 0 | 0 | 19069 | VT PR/Media | 609801 | Other sales costs | 173.41 | 0 | ||
21 | 25119.13 | 0 | 25119.13 | 19069 | VT PR/Media | 783501 | Depreciation of cars | 25119.13 | 0 | ||
Sheet4 |
Sub Test()
Dim ShNew As Worksheet
Dim LastRow As Long
Dim First As Long
Dim r As Long
Dim i As Long
Set ShNew = Worksheets.Add
ShNew.Range("A1:I1").Value = Array("Act. per 8", "Plan. per 8", "Var. per 8", "Cost center", "Cost center text", "Cost element", "Cost element text", "Act per 01 - 8", "Plan version 3")
First = 2
r = 2
With Worksheets("Sheet1")
LastRow = .Range("A" & .Rows.Count).End(xlUp).Row
For i = 2 To LastRow
If Left(.Range("D" & i).Value, 1) <> "*" Then
.Range("A" & i).Resize(, 3).Copy ShNew.Range("A" & r)
ShNew.Range("F" & r).Value = Left(WorksheetFunction.Trim(.Range("D" & i).Value), 6)
ShNew.Range("G" & r).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 8)
.Range("E" & i).Resize(, 2).Copy ShNew.Range("H" & r)
r = r + 1
Else
ShNew.Range("D" & First & ":D" & r - 1).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 3, 5)
ShNew.Range("E" & First & ":E" & r - 1).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 9)
First = r
End If
Next i
End With
End Sub