PDA

View Full Version : Pivot Table Fill Blank Cells Add-in



mgomez
02-16-2006, 09:20 PM
Hello everybody! I?m new to this forum, but I would like to ask for your help, I?ve been looking for a solution to auto fill the empty cells created in a pivot table, and recently I found a code in a forum by a guy called Ozgrid that does that.

The code does exactly what its required to do, but every time I want to use the code, I have to create a macro and paste it for each workbook where I want to use it, and manually the pivot?s name (if I have more than one in the same workbook), as well as where I want the macro to start pasting as values (currently set in cell ?j1?). I was wondering if there is a way, to convert this code into something like a plug-in that would automatically open with excel and add on the toolbar, that would ask you from which cell it should fill the table and paste the info as values for the active/selected pivot table or active worksheet (maybe a popup window could ask you the cell where you want it to start pasting values, just by typing the cell number or by clicking on the desired cell)?



Here is the code: and the credits for its creator:


Sub CopyPtAndFillInBlanks()
'Written by OzGrid Business Applications
'www.ozgrid.com
'http://www.mrexcel.com/archive/Pivot/15261.html
Dim RPt As Range
Dim MyCell As Range

Application.ScreenUpdating = False

ActiveSheet.PivotTables("PivotTable1").PivotSelect "", xlDataAndLabel
Selection.Copy

Range("j1").PasteSpecial xlPasteValues
Range("j1").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

Application.ScreenUpdating = True



End Sub





Thanks!!!