Pivot Table, select data and put into new sheet.

[ratdogexcel]

I have a set of data that I would like to be able to select some of the rows of data and put them into a new sheet.

I would like to use the format of pivot table as that is the best way to be sorting all my data.

I can do the pivot table part, but is there a way to check or tick data in pivot tables and then run a marco or to collate the data with ticks into a different sheet? Is there another way of doing this?


Pivot table test.xlsx

Thanks in advance

[wakeupcall]

Hi,

Sorry, but I do not see why you need a macro transferring a subset of your data to a new sheet. You can easily achieve the layout you want by modfying the pivot table and it will be much easier to maintain upon source data additons, removals or changes in logic

Click anywhere on the pivot table, go to PivotTable Tools tab --> Design sub tab ---> Report Layout --> Show in tabular form
Then click on a field with a plus sign and go to Options sub tab --> Expand entire field (repeat that for any field you need expanding)

If you want each item to be repeated then right-click anywhere on the pivot field, select Field Settings option --> Layout & print sub tab --> tick Repeat Item labels option (again repeat for as many fields as necessary

Once done modifying, you can copy and paste the pivot table in a new sheet and tweak filters to show a single category (e.g. Biological)

[ratdogexcel]

Wakeupcall

Thanks for those tips. Ill be able to use them at some stage but the reason I want to be able to highlight or tick some of the data is that I want to be able to select only a few of the options in the last field then transfer them into the result tab. e.g.

UTILISE disposable Gloves
UTILISE disposable Mask
UTILISE Ex Rated ventilation

just like the example in the "result" tab

[wakeupcall]

I see,

won't applying a label filter on the last pivot field do the trick? Something like "contains UTILISE"? What is your criteria for highlighting records that should appear in the result tab?

[ratdogexcel]

once again i gave a bad example in the previous post.

From the pivot table i would like to have free range on what i would like to select and therefor transfer to the result tab.

The aim is to have a pivot table where i can open and close each of the fields, once the final field is open then select which data in the final field is transferred to the result tab.

I hope this makes sense this time.

[wakeupcall]

Hi ratdogexcel,

is this what you are after?

[ratdogexcel]

Pivot table transformed.xlsx


Currently i run a macro that looks at the before tab and then looks for True in the transfer column. If there is a true then it copies that row into the after sheet.

I like the look of the normal pivot table but i would like the option to select certain records to be transferred to the after page.

[wakeupcall]

Hi ratdogexcel,

In that case it would defintely help if I could see the code of your current macro so that I know what condtion it searches for to equal True and in what Excel object exactly
Also, does the before tab contain a pivot table, table or just data range. The result tab - is it cumulative or is it overwritten every time the macro is run?

To what extent is the structure of your original file (in terms of sheets and objects in them) replicated in the excel file I have seen?

If there is not an exact match, it would be helpful to see such

[ratdogexcel]

Hi ratdogexcel,

In that case it would defintely help if I could see the code of your current macro so that I know what condtion it searches for to equal True and in what Excel object exactly
Also, does the before tab contain a pivot table, table or just data range. The result tab - is it cumulative or is it overwritten every time the macro is run?

To what extent is the structure of your original file (in terms of sheets and objects in them) replicated in the excel file I have seen?

If there is not an exact match, it would be helpful to see such

Here is the code i use to transfer the selected items into a new sheet

Please Login or Register  to view this content.

Test checkbox 25-12-13.xlsm

This code is within Master module.

To run the whole thing I use "sequence"

[ratdogexcel]

I found a solution in the contextures website

http://blog.contextures.com/archives...in-excel-2010/

it adds a slicer from an associated pivot table and does a check on an id no. Really quite smart by AlexJ.