I have the following table :

Month
Code 1 2 3 4
5110 xxx
5111
5112
5113
5114

I would like to rearrange the table as

Code Month Amount
5110 1 xxx
5110 2 xxx
5110 3
5110 4
5111 1
5111 2
5111 3
5111 4
5112 1

Can I use the pivot table for this task ? Also it is possible to remove the
Pivot Table Structure after rearranging ? I need to input the data in
large batches to another business system.

For the information, the Paste Special Transpose Data function does not
rearrange the data in the manner I wanted.


Thanks

Low





--
A36B58K641

Pivot table will not work for this. Yes, you can remove the structure
after rearranging, but it won't do what you want.

I do however have some code that will help. I adapted it to solve
your problem.


Public Sub splitByMonth()
'************************************************* **********
'****************** constant declarations ******************
'************************************************* **********
'column on this worksheet containing code
Const iSourceGLcol As Long = 1

'column on this worksheet containing date
Const iSourceDateRow As Long = 1

'row on this worksheet containing the first code
Const iSourceFirstRow As Long = 2

'destination worksheet name
Const strWshName As String = "Transpose"

'column on destination worksheet to contain code
Const iDestGLcol As Long = 1

'column on destination worksheet to contain date
Const iDestDateCol As Long = 2

'column on destination worksheet to contain amount
Const iDestAmountCol As Long = 3

'************************************************* **********
'****************** variable declarations ******************
'************************************************* **********

'row and column on source worksheet
Dim iCol As Long
Dim iRow As Long

'destination row on import data worksheet
Dim iRowDest As Long

'import data worksheet
Dim wsh As Excel.Worksheet

'************************************************* **********
'******************** execution section ********************
'************************************************* **********

Application.ScreenUpdating = False

Set wsh = ThisWorkbook.Worksheets.Add

' check for worksheet from previous runs
' if rename operation fails, it's because
' a worksheet by that name already exists
On Error Resume Next
wsh.Name = strWshName
On Error GoTo 0

'confirm deletion
If wsh.Name < strWshName Then
If VBA.MsgBox("Import data worksheet exists. Delete?", _
vbYesNo + vbInformation) = vbNo Then
Application.DisplayAlerts = False
wsh.Delete
Application.DisplayAlerts = True
Exit Sub
End If
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(strWshName).Delete
Application.DisplayAlerts = True
wsh.Name = strWshName
End If

iRowDest = 1

For iRow = iSourceFirstRow To Me.UsedRange.Rows.Count
' process each month's amount
' assuming there are 12 months, and
' first month follows code column immediately
For iCol = iSourceGLcol + 1 To iSourceGLcol + 12
wsh.Cells(iRowDest, iDestGLcol).Value = _
Me.Cells(iRow, iSourceGLcol).Value
wsh.Cells(iRowDest, iDestDateCol).Value = _
Me.Cells(iSourceDateRow, iCol).Value
wsh.Cells(iRowDest, iDestAmountCol).Value = _
Me.Cells(iRow, iCol).Value
iRowDest = iRowDest + 1
Next iCol
Next iRow

Application.ScreenUpdating = True
End Sub


Let me know if you have problems with it.

On Oct 12, 6:27 am, Mr. Low wrote:
I have the following table :

Month
Code 1 2 3 4
5110 xxx
5111
5112
5113
5114

I would like to rearrange the table as

Code Month Amount
5110 1 xxx
5110 2 xxx
5110 3
5110 4
5111 1
5111 2
5111 3
5111 4
5112 1

Can I use the pivot table for this task ? Also it is possible to remove the
Pivot Table Structure after rearranging ? I need to input the data in
large batches to another business system.

For the information, the Paste Special Transpose Data function does not
rearrange the data in the manner I wanted.

Thanks

Low

--
A36B58K641

Helo Iliace,

Thanks for the code.


Low
--
A36B58K641


"iliace" wrote:

Pivot table will not work for this. Yes, you can remove the structure
after rearranging, but it won't do what you want.

I do however have some code that will help. I adapted it to solve
your problem.


Public Sub splitByMonth()
'************************************************* **********
'****************** constant declarations ******************
'************************************************* **********
'column on this worksheet containing code
Const iSourceGLcol As Long = 1

'column on this worksheet containing date
Const iSourceDateRow As Long = 1

'row on this worksheet containing the first code
Const iSourceFirstRow As Long = 2

'destination worksheet name
Const strWshName As String = "Transpose"

'column on destination worksheet to contain code
Const iDestGLcol As Long = 1

'column on destination worksheet to contain date
Const iDestDateCol As Long = 2

'column on destination worksheet to contain amount
Const iDestAmountCol As Long = 3

'************************************************* **********
'****************** variable declarations ******************
'************************************************* **********

'row and column on source worksheet
Dim iCol As Long
Dim iRow As Long

'destination row on import data worksheet
Dim iRowDest As Long

'import data worksheet
Dim wsh As Excel.Worksheet

'************************************************* **********
'******************** execution section ********************
'************************************************* **********

Application.ScreenUpdating = False

Set wsh = ThisWorkbook.Worksheets.Add

' check for worksheet from previous runs
' if rename operation fails, it's because
' a worksheet by that name already exists
On Error Resume Next
wsh.Name = strWshName
On Error GoTo 0

'confirm deletion
If wsh.Name < strWshName Then
If VBA.MsgBox("Import data worksheet exists. Delete?", _
vbYesNo + vbInformation) = vbNo Then
Application.DisplayAlerts = False
wsh.Delete
Application.DisplayAlerts = True
Exit Sub
End If
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(strWshName).Delete
Application.DisplayAlerts = True
wsh.Name = strWshName
End If

iRowDest = 1

For iRow = iSourceFirstRow To Me.UsedRange.Rows.Count
' process each month's amount
' assuming there are 12 months, and
' first month follows code column immediately
For iCol = iSourceGLcol + 1 To iSourceGLcol + 12
wsh.Cells(iRowDest, iDestGLcol).Value = _
Me.Cells(iRow, iSourceGLcol).Value
wsh.Cells(iRowDest, iDestDateCol).Value = _
Me.Cells(iSourceDateRow, iCol).Value
wsh.Cells(iRowDest, iDestAmountCol).Value = _
Me.Cells(iRow, iCol).Value
iRowDest = iRowDest + 1
Next iCol
Next iRow

Application.ScreenUpdating = True
End Sub


Let me know if you have problems with it.

On Oct 12, 6:27 am, Mr. Low wrote:
I have the following table :

Month
Code 1 2 3 4
5110 xxx
5111
5112
5113
5114

I would like to rearrange the table as

Code Month Amount
5110 1 xxx
5110 2 xxx
5110 3
5110 4
5111 1
5111 2
5111 3
5111 4
5112 1

Can I use the pivot table for this task ? Also it is possible to remove the
Pivot Table Structure after rearranging ? I need to input the data in
large batches to another business system.

For the information, the Paste Special Transpose Data function does not
rearrange the data in the manner I wanted.

Thanks

Low

--
A36B58K641

Sorry forgot to add - put this code in the module of the worksheet
that has the source data, not a standard module.


On Oct 12, 12:13 pm, iliace wrote:
Pivot table will not work for this. Yes, you can remove the structure
after rearranging, but it won't do what you want.

I do however have some code that will help. I adapted it to solve
your problem.

Public Sub splitByMonth()
'************************************************* **********
'****************** constant declarations ******************
'************************************************* **********
'column on this worksheet containing code
Const iSourceGLcol As Long = 1

'column on this worksheet containing date
Const iSourceDateRow As Long = 1

'row on this worksheet containing the first code
Const iSourceFirstRow As Long = 2

'destination worksheet name
Const strWshName As String = "Transpose"

'column on destination worksheet to contain code
Const iDestGLcol As Long = 1

'column on destination worksheet to contain date
Const iDestDateCol As Long = 2

'column on destination worksheet to contain amount
Const iDestAmountCol As Long = 3

'************************************************* **********
'****************** variable declarations ******************
'************************************************* **********

'row and column on source worksheet
Dim iCol As Long
Dim iRow As Long

'destination row on import data worksheet
Dim iRowDest As Long

'import data worksheet
Dim wsh As Excel.Worksheet

'************************************************* **********
'******************** execution section ********************
'************************************************* **********

Application.ScreenUpdating = False

Set wsh = ThisWorkbook.Worksheets.Add

' check for worksheet from previous runs
' if rename operation fails, it's because
' a worksheet by that name already exists
On Error Resume Next
wsh.Name = strWshName
On Error GoTo 0

'confirm deletion
If wsh.Name < strWshName Then
If VBA.MsgBox("Import data worksheet exists. Delete?", _
vbYesNo + vbInformation) = vbNo Then
Application.DisplayAlerts = False
wsh.Delete
Application.DisplayAlerts = True
Exit Sub
End If
Application.DisplayAlerts = False
ThisWorkbook.Worksheets(strWshName).Delete
Application.DisplayAlerts = True
wsh.Name = strWshName
End If

iRowDest = 1

For iRow = iSourceFirstRow To Me.UsedRange.Rows.Count
' process each month's amount
' assuming there are 12 months, and
' first month follows code column immediately
For iCol = iSourceGLcol + 1 To iSourceGLcol + 12
wsh.Cells(iRowDest, iDestGLcol).Value = _
Me.Cells(iRow, iSourceGLcol).Value
wsh.Cells(iRowDest, iDestDateCol).Value = _
Me.Cells(iSourceDateRow, iCol).Value
wsh.Cells(iRowDest, iDestAmountCol).Value = _
Me.Cells(iRow, iCol).Value
iRowDest = iRowDest + 1
Next iCol
Next iRow

Application.ScreenUpdating = True
End Sub

Let me know if you have problems with it.

On Oct 12, 6:27 am, Mr. Low wrote:



I have the following table :

Month
Code 1 2 3 4
5110 xxx
5111
5112
5113
5114

I would like to rearrange the table as

Code Month Amount
5110 1 xxx
5110 2 xxx
5110 3
5110 4
5111 1
5111 2
5111 3
5111 4
5112 1

Can I use the pivot table for this task ? Also it is possible to remove the
Pivot Table Structure after rearranging ? I need to input the data in
large batches to another business system.

For the information, the Paste Special Transpose Data function does not
rearrange the data in the manner I wanted.

Thanks

Low

--
A36B58K641- Hide quoted text -

- Show quoted text -

Hi Low

You can do this utilising a method described by John Walkenbach
http://www.j-walk.com/ss/excel/usertips/tip068.htm

--
Regards
Roger Govier



"Mr. Low" wrote in message
...
I have the following table :

Month
Code 1 2 3 4
5110 xxx
5111
5112
5113
5114

I would like to rearrange the table as

Code Month Amount
5110 1 xxx
5110 2 xxx
5110 3
5110 4
5111 1
5111 2
5111 3
5111 4
5112 1

Can I use the pivot table for this task ? Also it is possible to remove
the
Pivot Table Structure after rearranging ? I need to input the data in
large batches to another business system.

For the information, the Paste Special Transpose Data function does not
rearrange the data in the manner I wanted.


Thanks

Low





--
A36B58K641

OH! So you could do it with a pivot table. Nice to know.

My particular problem for which I used the code above also had
additional columns, such as annual total and code description values,
which were used in the For loop for some calculations.

On Oct 12, 1:05 pm, "Roger Govier" <rogerattechnology4NOSPAMu.co.uk
wrote:
Hi Low

You can do this utilising a method described by John Walkenbachhttp://www.j-walk.com/ss/excel/usertips/tip068.htm

--
Regards
Roger Govier

"Mr. Low" wrote in message

...



I have the following table :

Month
Code 1 2 3 4
5110 xxx
5111
5112
5113
5114

I would like to rearrange the table as

Code Month Amount
5110 1 xxx
5110 2 xxx
5110 3
5110 4
5111 1
5111 2
5111 3
5111 4
5112 1

Can I use the pivot table for this task ? Also it is possible to remove
the
Pivot Table Structure after rearranging ? I need to input the data in
large batches to another business system.

For the information, the Paste Special Transpose Data function does not
rearrange the data in the manner I wanted.

Thanks

Low

--
A36B58K641- Hide quoted text -

- Show quoted text -

Hello Roger,

Thanks for the tip and many other helpful resources at the website.

Regards,

Low


--
A36B58K641


"Roger Govier" wrote:

Hi Low

You can do this utilising a method described by John Walkenbach
http://www.j-walk.com/ss/excel/usertips/tip068.htm

--
Regards
Roger Govier



"Mr. Low" wrote in message
...
I have the following table :

Month
Code 1 2 3 4
5110 xxx
5111
5112
5113
5114

I would like to rearrange the table as

Code Month Amount
5110 1 xxx
5110 2 xxx
5110 3
5110 4
5111 1
5111 2
5111 3
5111 4
5112 1

Can I use the pivot table for this task ? Also it is possible to remove
the
Pivot Table Structure after rearranging ? I need to input the data in
large batches to another business system.

For the information, the Paste Special Transpose Data function does not
rearrange the data in the manner I wanted.


Thanks

Low





--
A36B58K641