VBA Pivot selecting only data

[AmateurXL]

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

[jomili]

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/

[AmateurXL]

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:

Usedrange code:

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

Please advise.


Thanks,

A

[jomili]

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

[AmateurXL]

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.

[jomili]

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.

[OnErrorGoto0]

Assuming the code is in the workbook where the source data is, then the syntax would be something akin to

Please Login or Register  to view this content.

[AmateurXL]

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

[OnErrorGoto0]

I am unsure whom you are addressing. If you use the syntax I posted there is no need to copy any sheets - you simply create the pivotcache and pivot table in the target workbook.

[jomili]

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.

Please Login or Register  to view this content.

[jomili]

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

Please Login or Register  to view this content.

[OnErrorGoto0]

CreatePivotTable returns a pivottable object which you can assign to PT directly. No need to select anything.

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

[jomili]

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

Please Login or Register  to view this content.

...could be replaced with something like:

Please Login or Register  to view this content.

Please Login or Register  to view this content.

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

[OnErrorGoto0]

Please Login or Register  to view this content.

should be the answer to both, if that helps?

[jomili]

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.

Please Login or Register  to view this content.

[AmateurXL]

John and 0EGO,

Apologies for the belated response, I was a little occupied in implementing your suggestions and neatening my macro. I can safely say that the formula does work!- Many thanks to both of you, it has been causing me a pain for a while.

Just to add to the problem, I do have a couple of other issues I was hoping the two of you (and any other who read this) would help me resolve.
Firstly: having created the pivot tables, I was hoping to rearrange them in descending order. However the issue I am having is the code isn't working. There are, as far as I know, two codes I can try;

Please Login or Register  to view this content.

The code is giving me an error. The other code is one where I just recorded a macro to establish what the code would be, the issue with this is that it is for a fixed range, therefore it won't cater to a change in the data.

Please Login or Register  to view this content.

I have attached a dummy workbook which attempts the former code. Please Advise.

The second issues I have is as follows;

I have a Pivot Table, within the pivot item list ("Lowest Ratings") I have options which range from 0 - 21.
I would like to create a code whereby the instructions are; to deselect any items from the pivot item list which are less than 11.

Please Advise!



Many Thanks,

A.

[OnErrorGoto0]

It should be PivotFields and not PivotField.

[jomili]

For your second problem, see if this code helps you get started. It's in the attached workbook as well.

Please Login or Register  to view this content.

[AmateurXL]

Hi Rory,

Thanks for your response, I have tried running the code with the suggested correction, I am still receiving error messages.

I have attached the workbook with the correction.

Please advise.



Thanks,

Axl

[AmateurXL]

Hi Jomilli,

Thanks for your response, the code works well. The only issue I have is that I have various pivot tables on the same worksheet. Not all of them have the pivotfields("Region"). therefore I would like the code to specidically operate on the pivottable("AllPivot4").

I made various attemps to do this, but all were unsuccessfull.

Please advise.

Thanks,

Axl

[OnErrorGoto0]

I get no errors with the code in that workbook, it just doesn't do anything, because you need to specify the field caption as it is in the table

Please Login or Register  to view this content.

[jomili]

Hi AMXl,

You said

Please Login or Register  to view this content.

I think you mean "in addition to the PT we just created", right? If so, to work on different PivotTables you just need to define them. See below for untested sample code:

Please Login or Register  to view this content.

[AmateurXL]

Hi Jomilli,

I have attached a dummy sheet, the headings for the sheet is exactly as the ones I am using for my actual workbook, but the data is irrelevant.
In the workbook you will find that when you run the macro, there are four pivots being created. I want to use your code "'Change the pivot table to only give us the info we need" specifically for the last two pivots, however when I insert the code it doesn't do that.

Therefore I was wondering if, when inputting the code, we could specify which pivot table to apply it to.



Many Thanks,

Axl

[Macdave_19]

Why Don't you create a Dynamic Named Range and use that as the source:

Click on Cell A1 on the "Data" Sheet then Hit CTRL+F3

and Give the Range a Name i.e. "MYDATA"

Then in the bottom box type this

Please Login or Register  to view this content.

When your data changes so will the size of the named range, so long as there are no blank column headings in row 1 or blank fields in column 1, if there are use a column that will always contain a value in place of "A".

Then in your Macro just Reference "MYDATA" in the source

If you want to exclude a few columns off the end then just add -2 to the second counta like so, be sure to give it a new name like "MYDATALESS2":

Please Login or Register  to view this content.

Hope that helps