Pivot Table Macro

[jrambo]

Hello.

I am trying to find out how to copy the description to each row of my pivot table. For example, my pivot table currently looks like:

Red 4 800
5 700
Blue 8 400

I would like it to look like:

Red 4 800
Red 5 700
Blue 8 400

I need the description on each line so I can sort it more like I need it.

Anyone know how to do this?

Thanks in advance.

 

[xlbo]

Can't do this in a pivot table
You would need to copy>Paste special your data and then run a macro to insert the missing details. Post back if this would be a suitable workaround and I'll provide some code Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[jrambo]

That will be fine.

Thanks!

 

[xlbo]

This should do the trick
Just copy>Paste special Values your pivot table onto a new sheet
Then select the 1st cell in the column you want to fill in and play the macro
Sub fillindata()
Dim ucol As Integer, sRow As Long, lRow As Long
ucol = ActiveCell.Column
sRow = ActiveCell.Row
lRow = Cells(65536, ucol).End(xlUp).Row
For i = sRow To lRow
If Cells(i, ucol).Text = "Grand Total" Then
Exit Sub
Else
tempRow = Cells(i, ucol).End(xlDown).Row - 1
Range(Cells(i + 1, ucol), Cells(tempRow, ucol)).Value = Cells(i, ucol).Text
i = tempRow
End If
Next i
End Sub
Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[jrambo]

I am getting a syntax error when I try to run it. It is pointing to

Range(Cells(i + 1, ucol), Cells(tempRow, ucol)).Value = Cells(i, ucol).Text

Thanks for your help.

 

[xlbo]

Well there's nothing syntactically wrong with that line and I tried running the code on a test set of data and it worked fine

Slight change to test for Grand Total (ie end of selection)
Sub fillindata()
Dim ucol As Integer, sRow As Long, lRow As Long
ucol = ActiveCell.Column
sRow = ActiveCell.Row
lRow = Cells(65536, ucol).End(xlUp).Row
For i = sRow To lRow
If UCase(Cells(i, ucol).Text) = "GRAND TOTAL" Or i = lRow Then
Exit Sub
Else
tempRow = Cells(i, ucol).End(xlDown).Row - 1
Range(Cells(i + 1, ucol), Cells(tempRow, ucol)).Value = Cells(i, ucol).Text
i = tempRow
End If
Next i
End Sub

I may have been slightly misleading in where you need to have selected. Select the cell that is the 1st entry in the column you wish to fill

Other than that, it works fine

If you are still having problems, post your email address. I will email you and you can then send me a copy of the workbook by reply (change any data if it is sensitive) Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde