Thread: VBA Pivot selecting only data

Hi,

I am coding a macro, and I would like the macro to create a pivot from Data sheet "All Dated". however when the macro creates the pivot, I want the pivot table to select all cells with data in them, ignoring all blank/empty cells.

I don't want fixed cells selected for the creation of the pivot because the Data is variable.

I have this code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'All Dated'!R4C1:R3622C28").CreatePivotTable TableDestination:= _
"'[Data Dump.xls]Dated Pivot'!R3C1", TableName:="PivotTable15", _
DefaultVersion:=xlPivotTableVersion10

For reference purposes: "All Dated" is where the data is coming from
"Data Dump/ Dated Pivot" is the destination

Please Advise.


Thanks,

A

FYI. Please wrap all your code with code tags.

You could try using the UsedRange property. Go here for details: http://www.databison.com/index.php/h...ble-using-vba/

Hi,

Thanks for your response. Apologies for not wrapping the code, as you can see I am a newcomer to the forum.

With re to the "usedrange" code, I was wondering if you could help me implement it into my current code. I am having issues because I the data is being imported from an external workbook.

My current code:

HTML Code: 

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'Discretionary Perpetual'!usedrange").CreatePivotTable TableDestination:= _
        "'[Data Dump.xls]Dated Pivot'!R3C1", TableName:="PivotTable15", _
        DefaultVersion:=xlPivotTableVersion10

Usedrange code:

HTML Code: 

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=ActiveSheet.UsedRange).CreatePivotTable TableDestination:="", TableName:="PivotTable101", DefaultVersion:=xlPivotTableVersion10

ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(1, 1)

I'm assuming I just replace the "!R3C1" with "Usedrange"?

Please advise.


Thanks,

A

Hi AmateurXL,

"!R3C1" refers to where in the intended destination you want to paste the pivot table (Row3,Column1, or cell A3). You couldn't use "UsedRange" in the destination unless your new pivot is going to have the same dimensions as the existing data in your destination sheet.

I don't know how to create a pivot in workbook B from source data in workbook A (sorry, I'm still a learner, too), but I do know how to create a pivot in workbook B from source data in workbook B, and copy the pivot to Workbook A. Let me know if you want to do that, or maybe someone will pop in with a better solution.

It might help to post your full code so we can see what's going on around the pivot table creation, as well as a sample of your workbooks.

Thanks,
John

Hi John,

I may have to do that, I think copying and pasting the pivot tabel would be a better idea. However, if they are in two different workbooks, can the data be refereshed, subsequently refreshing any charts and/ or results derived from the table?

If so, I would be greatful if you could provide me with the code for my table.

Once I do clean up my VBA codes (making them more legible), I will upload a sample and the entire code.


Thanks,

A.

The code needed depends on what you'd like to do. For instance, we can create the pivot on, say, Sheet1, then copy it and paste it to another workbook as a pivot table, which would mean you'd still be able to drill down to the details, and we could refresh the table, or we could paste as values and formats so it'll look like the pivot table but won't have drill down capabilities. Or, we can move sheet1 to the new/different workbook, where again you could refresh the table.

So let me know what you want to do. Moving the sheet is probably the easist, but none of these are very hard as long as you're clear on what you want to do.

Excellent!-

In that case, perhaps the best option would be to create the Pivot Table in Workbook "A", on "sheet 1", then move the entire sheet to workbook "B".
I think it would be most viable for the pivot table to have the drill down capabilities seeing as the data will varify as time goes on, and the sheets will be updated.


Thanks,
A

Sorry it's taken me so long to get back. I've been busy with my real job.

0EGO,
That's a nice piece of coding! I searched myself and never found a way to make a pivot in a remote workbook, but your code works! I'll add that to my bag of tricks.

Amateur,
I've taken OEG0's code and played a bit with it. I've attached two workbooks so you can see how it works. Not having access to your data I used some of mine. The code (as shown below) resides in the Source workbook, and you have to be in the source workbook for the code to work, but not necessarily on the source page [0EGO, question for you; how would we replace "ThisWorkbook.Name" with the actual name of the workbook, in case we wanted to run this from a third workbook?]

I've added just a few of my pivot tricks in the hopes it can help you flesh out your code. I couldn't figure out how to add the PT fields without activating and selecting on the Data Dump book, but maybe OEG0 can point us in the right direction there.

Let us know what else you need on this.

Sub RemotePiv()
'---------------------------------------------------------------------------------------
' Procedure : RemotePiv
' Author    : OnErrorGoTo0 and Jomili
' Date      : 1/26/2012
' Purpose   : Creates pivot in another workbook from the active workbook
'---------------------------------------------------------------------------------------
'Data Dump is target workbook, "Dated Pivot" is target worksheet
'Active (source) workbook can have any name
'Source worksheet is "Discretionary Perpetual"
'Don't have to be on source worksheet, but DO have to be in source workbook

Dim PT As PivotTable

Workbooks("Data Dump.xls").PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'[" & ThisWorkbook.Name & "]Discretionary Perpetual'!" & Sheets("Discretionary Perpetual").UsedRange.Address(ReferenceStyle:=r1c1)).CreatePivotTable TableDestination:= _
        Workbooks("Data Dump.xls").Sheets("Dated Pivot").Range("A3"), TableName:="PivotTable15", _
        DefaultVersion:=xlPivotTableVersion10
        
        Workbooks("Data Dump.xls").Sheets("Dated Pivot").Activate
        ActiveSheet.Range("A3").Select
        Set PT = ActiveSheet.PivotTables("PivotTable15")
                 
        'Don't let the pivot table update while we're working on it
        PT.ManualUpdate = True  'Speeds up code dramatically
        
        'Add our fields
        PT.AddFields RowFields:=Array("Unit", "Posn Func", "Data")
        
        'change the name of a field
        With PT.PivotFields("FTE")
            .Orientation = xlDataField
            .Caption = "Number of FTEs"
            .Function = xlCount
        End With
        'Turn off the blanks
        With PT.PivotFields("Unit")
            .PivotItems("(blank)").Visible = False
        End With
        
        'We're done.  Allow the pt to update
        PT.ManualUpdate = False
    
        
End Sub

Realized after I posted that this line is unneeded and can be deleted from the macro:

ActiveSheet.Range("A3").Select

So, if I understand correctly, you are saying that these lines:

Workbooks("Data Dump.xls").Sheets("Dated Pivot").Activate
        ActiveSheet.Range("A3").Select
        Set PT = ActiveSheet.PivotTables("PivotTable15")

...could be replaced with something like:

Set PT = Workbooks("Data Dump.xls").Sheets("Dated Pivot").PivotTables("PivotTable15")

You can use a literal string in place of Thisworkbook.name if you know the source file name at design time.

I tried using "Data Source.xls" in place of "Thisworkbook.name" but couldn't get it to work. Can you provide example code?

It certainly does, at least for me. I really like code I can work with.

Amateur, based on this latest info from OEG0, I've updated the macro to make it a little more flexible. Let us know if that does the trick for you.

Sub RemotePiv()
'---------------------------------------------------------------------------------------
' Procedure : RemotePiv
' Author    : OnErrorGoTo0 and Jomili
' Date      : 1/27/2012
' Purpose   : Creates pivot in another workbook from the active workbook
'---------------------------------------------------------------------------------------
'Data Dump is target workbook, "Dated Pivot" is target worksheet, Source worksheet is "Discretionary Perpetual"
'Don't have to be on source worksheet, but DO have to be in source workbook

Dim PT As PivotTable

' You can use this line to allow Active (source) workbook to have any name
'SourceData:= "'[" & ThisWorkbook.Name & "]Discretionary Perpetual'!" & Sheets("Discretionary Perpetual")
        
   Set PT = Workbooks("Data Dump.xls").PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
        "'[Data source.xls]Discretionary Perpetual'!" & Sheets("Discretionary Perpetual"). _
        UsedRange.Address(ReferenceStyle:=r1c1)).CreatePivotTable(TableDestination:= _
        Workbooks("Data Dump.xls").Sheets("Dated Pivot").Range("A3"), TableName:="PivotTable15", _
        DefaultVersion:=xlPivotTableVersion10)
        
        'Don't let the pivot table update while we're working on it
        PT.ManualUpdate = True  'Speeds up code dramatically
        
        'Add our fields
        PT.AddFields RowFields:=Array("Unit", "Posn Func", "Data")
        
        'change the name of a field
        With PT.PivotFields("FTE")
            .Orientation = xlDataField
            .Caption = "Number of FTEs"
            .Function = xlCount
        End With

        'Turn off the blanks
        On Error Resume Next
        With PT.PivotFields("Unit")
            .PivotItems("(blank)").Visible = False
        End With
        On Error GoTo 0
        
        'We're done.  Allow the pt to update
        PT.ManualUpdate = False
        
End Sub