Blank spaces representing repeat info on pivot table
Posted by A Hensil on April 20, 2001 12:49 PM
A common question in my office is how to have Excel fill in applicable information on every line of a pivot table. As is, it automatically keeps a cell blank if it is repeat information from the cell above. Then for reports we have to copy and paste to get the same on the other applicable lines. Is there a way Excel can fill in those blanks automatically?
Posted by Dave Hawley on April 20, 2001 1:57 PM
Hi
If i have understood you correctly the answer is no! But it can be done with a macro. the one below will copy PivotTable1 on the Activesheet and paste it starting from cell G1 as raw data (not a Pivot Table) it will then fill in all blank cell with the cell above. Change the Pivot Table name and the Range G1 if needed.
Sub CopyPtAndFillInBlanks()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim RPt As Range
Dim Cell As Range
Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
Selection.Copy
Range("G1").PasteSpecial xlPasteValues
Range("G1").PasteSpecial xlPasteFormats
On Error Resume Next
Set RPt = Selection.SpecialCells(xlCellTypeBlanks)
RPt.Value = RPt.Offset(-1, 0).Value
Selection.Columns.AutoFit
Set RPt = Nothing
Application.CutCopyMode = False
End Sub
To use this, push Alt+F11 then go to Insert>Module and paste it in. Push Alt+Q, then Push Alt+F8, click "CopyPtAndFillInBlanks" then Options and assign a shortcut key.
Dave
OzGrid Business Applications
Posted by Dave Hawley on April 20, 2001 2:19 PM
Oops, Ignore That Code.
...Use this one instead.
Sub CopyPtAndFillInBlanks()
'Written by OzGrid Business Applications
'www.ozgrid.com
Dim RPt As Range
Dim MyCell As Range
Application.ScreenUpdating = False
ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
Selection.Copy
Range("G1").PasteSpecial xlPasteValues
Range("G1").PasteSpecial xlPasteFormats
On Error Resume Next
Set RPt = Selection.SpecialCells(xlCellTypeBlanks)
For Each MyCell In RPt.Cells
MyCell = MyCell.Offset(-1, 0)
Next
Selection.Columns.AutoFit
Set RPt = Nothing
Application.CutCopyMode = False
End Sub
Dave
OzGrid Business Applications
Posted by A Hensil on April 20, 2001 5:14 PM
Re: Oops, Ignore That Code.
Thank you very much for your help, Dave!
Amy
