Wild_Vivek
New Member
- Joined
- Sep 16, 2014
- Messages
- 4
Below is the header that i receive from the extract.
Header1 Header2 Header3 Header4 Header5 Header6(Blank) Header7
Header8 header9(Blank)Header10 Header11 Header12 Header13 Header14
Header15 Header16 Header17 Header18 Header19 Header20 Header21
header22 header 23 header24 header25 header26 header27 header28
12345 S XXXXX XXX ODB $1,234 (This data is for the first 7 Header)2222222 J xxxxx xxx $12,345.00 $12,345 10%(This data is for the Second 7 Header)
2014-09-08 022-1234-0000 xxx(123456) Y $0 $1,234 (This data is for the Third 7 Header)
2014-06-17 Complete MP $12,344.00 $100 2%(This data is for the fourth 7 Header)
12345 S XXXXX XXX ODB $1,234 (This data is for the first 7 Header)2222222 J xxxxx xxx $12,345.00 $12,345 10%(This data is for the Second 7 Header)
2014-09-08 022-1234-0000 xxx(123456) Y $0 $1,234 (This data is for the Third 7 Header)
2014-06-17 Complete MP $12,344.00 $100 2%(This data is for the fourth 7 Header)
...
....
....
...
...
This is how my data comes in. I got the header aligned using the below macro, but the data keeps getting deleted..
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Sub AlignData() Dim i As Integer, j As Integer Dim rowCounter As Long, colCounter As Integer Dim lastRow As Long ' You can change the sheet number to the appropriate number starting from 1, ' or you can use the sheet's name in quotes e.g., Sheets("My_Sheet") ThisWorkbook.Sheets(1).Activate lastRow = ActiveSheet.UsedRange.Rows.Count ' This speeds things up but can it screw things up if the code glitches Application.ScreenUpdating = False Sheet1.EnableCalculation = False rowCounter = 1 colCounter = 1On Error GoTo Abort While lastRow > rowCounter For i = 1 To 5 If colCounter > 28 Then colCounter = 1 For j = 1 To 7 Cells(WorksheetFunction.Floor((rowCounter / 5) + 1, 1), colCounter) = Cells(rowCounter, j) colCounter = colCounter + 1 Next j rowCounter = rowCounter + 1 Next i WendAbort: Sheet1.EnableCalculation = True Application.ScreenUpdating = TrueEnd Sub</code></pre>
Please let me know if i am doing something wrong?
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Header1 Header2 Header3 Header4 Header5 Header6(Blank) Header7
Header8 header9(Blank)Header10 Header11 Header12 Header13 Header14
Header15 Header16 Header17 Header18 Header19 Header20 Header21
header22 header 23 header24 header25 header26 header27 header28
12345 S XXXXX XXX ODB $1,234 (This data is for the first 7 Header)2222222 J xxxxx xxx $12,345.00 $12,345 10%(This data is for the Second 7 Header)
2014-09-08 022-1234-0000 xxx(123456) Y $0 $1,234 (This data is for the Third 7 Header)
2014-06-17 Complete MP $12,344.00 $100 2%(This data is for the fourth 7 Header)
12345 S XXXXX XXX ODB $1,234 (This data is for the first 7 Header)2222222 J xxxxx xxx $12,345.00 $12,345 10%(This data is for the Second 7 Header)
2014-09-08 022-1234-0000 xxx(123456) Y $0 $1,234 (This data is for the Third 7 Header)
2014-06-17 Complete MP $12,344.00 $100 2%(This data is for the fourth 7 Header)
...
....
....
...
...
This is how my data comes in. I got the header aligned using the below macro, but the data keeps getting deleted..
<code style="margin: 0px; padding: 0px; border: 0px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif; white-space: inherit; background-image: initial; background-attachment: initial; background-size: initial; background-origin: initial; background-clip: initial; background-position: initial; background-repeat: initial;">Sub AlignData() Dim i As Integer, j As Integer Dim rowCounter As Long, colCounter As Integer Dim lastRow As Long ' You can change the sheet number to the appropriate number starting from 1, ' or you can use the sheet's name in quotes e.g., Sheets("My_Sheet") ThisWorkbook.Sheets(1).Activate lastRow = ActiveSheet.UsedRange.Rows.Count ' This speeds things up but can it screw things up if the code glitches Application.ScreenUpdating = False Sheet1.EnableCalculation = False rowCounter = 1 colCounter = 1On Error GoTo Abort While lastRow > rowCounter For i = 1 To 5 If colCounter > 28 Then colCounter = 1 For j = 1 To 7 Cells(WorksheetFunction.Floor((rowCounter / 5) + 1, 1), colCounter) = Cells(rowCounter, j) colCounter = colCounter + 1 Next j rowCounter = rowCounter + 1 Next i WendAbort: Sheet1.EnableCalculation = True Application.ScreenUpdating = TrueEnd Sub</code></pre>
Please let me know if i am doing something wrong?
<colgroup><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>