Archive of Mr Excel Message Board


Back to Pivot Tables in Excel archive index
Back to archive home

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?

Re: Blank spaces representing repeat info on pivot table

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


Oops, Ignore That Code.

Posted by Dave Hawley on April 20, 2001 2:19 PM
...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


Re: Oops, Ignore That Code.

Posted by A Hensil on April 20, 2001 5:14 PM
Thank you very much for your help, Dave!

Amy


This archive is from the original message board at www.MrExcel.com.
All contents © 1998-2004 MrExcel.com.
Visit our online store to buy searchable CD's with thousands of VBA and Excel answers.
Microsoft Excel is a registered trademark of the Microsoft Corporation.
MrExcel is a registered trademark of Tickling Keys, Inc.