Macro to rearrange structure of report.

[Carl_H]

Hello,

Is it possible to change the structure of this report:

To look like this:

[/IMG]

if anyone can help me I would be very grateful.

My report has a lot more rows than above but the columns are the same.

 

[Carl_H]

Hello again,
The macro has worked perfectly for my Swedish data. however when using it for some other countries the first letter of the cost center text is not included after running the macro. Could you please adjust the macro to correct this. thank you very much.

Rawdata:
[TABLE="width: 724"]
<TBODY>[TR]
[TD]Act. per 8</SPAN>[/TD]
[TD]Plan. per 8</SPAN>[/TD]
[TD]Var. per 8</SPAN>[/TD]
[TD]Cost centers/Cost elements</SPAN>[/TD]
[TD]Act per 01 - 8</SPAN>[/TD]
[TD]Plan version 0</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 501102 Husleje udgift</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 561601 Værkstedsvogne , forsikring</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 564902 Demo/test - diverse (Vogn på serviceskol</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 591807 Training material</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 594006 Customer events</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 615002 Tryksager</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 659103 Costs of training GC - Serviceskolen</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 659214 Costs of training NGC - Serviceskolen KB</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 659901 Recharge hired services</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 659904 Recharge techn. training to dealers - Se</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 701111 Timeløn faste medarb Timeløn / gager</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 732102 Kørepenge</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]* 7024 CD, Technical Training</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 659103 Costs of training GC - Serviceskolen</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 659901 Recharge hired services</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 659908 Recharge commercial training - Service S</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]* 7025 CD, COMMERCIAL TRAINING</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD] 609901 Support - VLBD</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]* 7030 Human Resources, regional</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]


Output from macro

[TABLE="width: 993"]
<TBODY>[TR]
[TD]Act. per X</SPAN>[/TD]
[TD]Plan. per X</SPAN>[/TD]
[TD]Var. per X</SPAN>[/TD]
[TD]Cost center</SPAN>[/TD]
[TD]Cost center text</SPAN>[/TD]
[TD]Cost element</SPAN>[/TD]
[TD]Cost element text</SPAN>[/TD]
[TD]Act per X - X</SPAN>[/TD]
[TD]Plan version X</SPAN>[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]501102</SPAN>[/TD]
[TD]Husleje udgift</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]561601</SPAN>[/TD]
[TD]Værkstedsvogne , forsikring</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]564902</SPAN>[/TD]
[TD]Demo/test - diverse (Vogn på serviceskol</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]591807</SPAN>[/TD]
[TD]Training material</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]594006</SPAN>[/TD]
[TD]Customer events</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]615002</SPAN>[/TD]
[TD]Tryksager</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]659103</SPAN>[/TD]
[TD]Costs of training GC - Serviceskolen</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]659214</SPAN>[/TD]
[TD]Costs of training NGC - Serviceskolen KB</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]659901</SPAN>[/TD]
[TD]Recharge hired services</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]659904</SPAN>[/TD]
[TD]Recharge techn. training to dealers - Se</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]701111</SPAN>[/TD]
[TD]Timeløn faste medarb Timeløn / gager</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7024</SPAN>[/TD]
[TD]D, Technical Training</SPAN>[/TD]
[TD="align: right"]732102</SPAN>[/TD]
[TD]Kørepenge</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7025</SPAN>[/TD]
[TD]D, COMMERCIAL TRAINING</SPAN>[/TD]
[TD="align: right"]659103</SPAN>[/TD]
[TD]Costs of training GC - Serviceskolen</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7025</SPAN>[/TD]
[TD]D, COMMERCIAL TRAINING</SPAN>[/TD]
[TD="align: right"]659901</SPAN>[/TD]
[TD]Recharge hired services</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7025</SPAN>[/TD]
[TD]D, COMMERCIAL TRAINING</SPAN>[/TD]
[TD="align: right"]659908</SPAN>[/TD]
[TD]Recharge commercial training - Service S</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]7030</SPAN>[/TD]
[TD]uman Resources, regional</SPAN>[/TD]
[TD="align: right"]609901</SPAN>[/TD]
[TD]Support - VLBD</SPAN>[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</TBODY><COLGROUP><COL><COL><COL><COL><COL><COL><COL><COL><COL></COLGROUP>[/TABLE]

 

[Andrew Poulsom]

A slight amendment:

ShNew.Range("D" & First & ":D" & r - 1).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 3, 4)
ShNew.Range("E" & First & ":E" & r - 1).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 8)

 

[Carl_H]

A slight amendment:

ShNew.Range("D" & First & ":D" & r - 1).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 3, 4)
ShNew.Range("E" & First & ":E" & r - 1).Value = Mid(WorksheetFunction.Trim(.Range("D" & i).Value), 8)

Perfect. Thanks again!