Pivot Table, select data and put into new sheet.

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

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

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.

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.

Originally Posted by 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

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

Sub Transfer_Checks()
'clear cells
Sheets("RA_Temp").Select
Range("A4:H3000").Select
    Selection.ClearContents
    Range("A2").Select
    
Sheets("Hazard_Tree").Select
LR = Cells(Rows.Count, "A").End(xlUp).row
drow = 4
'Step1
For r = 2 To LR
  'Sheets("Hazard_Tree").Select
  If Cells(r, 7) = True Then
    'Step Name
    Sheets("RA_Temp").Cells(drow, 2) = Sheets("Hazard_Tree").Cells(1, 7).Value
    'Hazard
    Sheets("RA_Temp").Cells(drow, 3) = Sheets("Hazard_Tree").Cells(r, 3).Value
    'Risk
    Sheets("RA_Temp").Cells(drow, 4) = Sheets("Hazard_Tree").Cells(r, 4).Value
    'Control
    Sheets("RA_Temp").Cells(drow, 5) = Sheets("Hazard_Tree").Cells(r, 5).Value
    
    'Sheets("RA_Temp").Cells(drow, 11) = Sheets("Hazard_Tree").Cells(r, 1).Value
    'Sheets("Hazard_Tree").Select
    drow = drow + 1
   End If
   'Sheets("RA_Temp").Select
   
Next

Sheets("RA_Temp").Select
End Sub

Test checkbox 25-12-13.xlsm

This code is within Master module.

To run the whole thing I use "sequence"

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.