Pivot Table (Blank)

[remeng]

Hi all,

I have constructed a macro to generate a pivot table. The macro works fine and is generating the table properly, but sometimes the macro adds a line with (blank) as its X and Y Lable. Does anyone have any ideas as to why this might be?

Thanks.

 

[SkipVought]

Hi,

Because there is no value in that column in your source data?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Hey Skip,

The area selected to create the pivot table does not have any blanks. Additionally the X and Y headers are also blank. These added (blank) column and row do not always apear and there is nothing different with how the data is generated between the pivot table with (blank) and without.

Any ideas on how to ensure that there aren't any columns or rows with the (blank) header?

Thanks in advance

 

[SkipVought]

Run this and then see if it persists...

Sub CleanMyPivots()
'SkipVought/2007 Apr 30/
'--------------------------------------------------
'cleans up pivot tables having OLD/UNUSED data hanging around.
'--------------------------------------------------
    Dim pc As PivotCache
    For Each pc In ActiveWorkbook.PivotCaches
       pc.MissingItemsLimit = xlMissingItemsNone
       pc.Refresh
    Next
End Sub

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[xlbo]

You might also want to
a: post VBA based code in forum707
b: post your code

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[remeng]

Skip,

I attempted the code that you provided. The code did not have any affect between the two different files. I think that there might be something other than data in the cache that might be the cause.

When I use the Pivot Table Wizard on the data, the (blank) does not appear. I have recorded the macro and compaired it to my created macro.

my code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'X Offset'!R1C1:R6066C3").CreatePivotTable TableDestination:="", TableName _
        :="X Offset PT", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("X Offset PT")
        .ColumnGrand = False
        .RowGrand = False
    End With
ActiveSheet.PivotTables("X Offset PT").AddFields RowFields:="Y", _
        ColumnFields:="X"                                                       
ActiveSheet.PivotTables("X Offset PT").PivotFields("X Offset").Orientation = _
        xlDataField

The Auto macro code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'X Offset'!R1C1:R5458C3").CreatePivotTable TableDestination:="", TableName _
        :="PivotTable1", DefaultVersion:=xlPivotTableVersion10
    ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
    ActiveSheet.Cells(3, 1).Select
    With ActiveSheet.PivotTables("PivotTable1")
        .ColumnGrand = False
        .RowGrand = False
    End With
    ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Y", _
        ColumnFields:="X"
    ActiveSheet.PivotTables("PivotTable1").PivotFields("X Offset").Orientation = _
        xlDataField

 

[SkipVought]

"When I use the Pivot Table Wizard on the data, the (blank) does not appear"

So what is the reason for using VBA?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

Basically it comes down to automation of data analysis on very large files. Manually this will take 30 minutes plus per file where as the macro can do the same task in only a few seconds. There are also several pivot tables per file. Unfortunately it will be to time consuming to perform the PT manually.

 

[xlbo]

Your code: SourceData:= "'X Offset'!R1C1:R6066C3"

Recorded code: SourceData:= "'X Offset'!R1C1:R5458C3"

You are picking up more lines in the non recorded version - do they contain data or are they (blank) ?

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[remeng]

xlbo:

Great Catch!

I'll have to go back and change that to a dynamic setting. I'll update if the change works.

Thanks!

 

[SkipVought]

You know that you can copy a pivot table and then change the source data reference. That takes all of about 10 seconds.

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[remeng]

xlbo:

Thanks! It solved the problem.

dumy = "'X Offset'!R1C1:R" & Ccolumn & "C3"

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        dumy).CreatePivotTable TableDestination:="", TableName _
        :="X Offset PT", DefaultVersion:=xlPivotTableVersion10

'ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'X Offset'!R1C1:R6066C3").CreatePivotTable TableDestination:="", TableName _
        :="X Offset PT", DefaultVersion:=xlPivotTableVersion10

by adding the dynamic value the selection will change acordingly

 

[xlbo]

<Burns>eeeeeehxellent</Burns>

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question