Using macro to remove unwanted fields and then create Pivot Table in Excel 2010

see attached for example Macro-to-remove-X-and-create-pivot.xlsx

Noted everything but you created the pivot in Cell B3 and this was not mentioned is it always in cell B3 ?

gowflow

good question !

sorry that I have missed out this information.

a) it does not matter which cell as long as it is easily readable in the new Pivot table worksheet.

b) Alternatively, if it is to be created in the same worksheet as the data (in this case would be in "Desired result - original data" worksheet), then the pivot should always be at column O, following the title row; meaning if the "title" is at row 46 then pivot should be created Cell O46.

actually thinking of it now, i would prefer b) as that would be neater else there will be lots of pivot table worksheets.

Thanks!

Looking at it at first that is what I thought was the most logical as you had Title already there.

So lets agree on something:
1) By Title you mean the word Title will be in that cell or it will be an actual tilte
2) Will it always be in Col A ?
3) Will the Title be always on the same row as the selected Table ?

We need to have some consistency to build the macro and not just scattered info.

If it is the case then I would appreciate you attach a worksheet that is the closest to reality with the title as it is there and you can simply modify the data if confidential.

gowlfow

1) By Title you mean the word Title will be in that cell or it will be an actual tilte
= the title will be in that cell.
2) Will it always be in Col A ?
= it will always be in col A
3) Will the Title be always on the same row as the selected Table ?
= yes

meaning if title is at cell A10, then highlighted section will be F10:H10 and onwards, ie. F10:Hxx, where xx is a variable.

Worksheet i attached previously is closest to reality and all data is modified for confidentiality already.
For example, the word "title" has been changed to make it generic.
thanks.

ok fine tks the info.

Is this what your looking for ?

Here is how it work.
1) Download the file and make sure your macros are enabled.
2) Goto sheet Original and you will see a button Create Pivot and your raw data.
3) Just make sure first you select your entire data and heading you should always have a heading above the data as these are used as picot fields. You did not have it in your Original sheet I included it.
4) After selecting the entire data and fields simply press on the button Create Pivot and follow the instructions.

I created a sheet original backup that have your original data you can re-copy at a different location and try if it works for you.

Here is the main code for that

Option Explicit

Sub CreatePivot(Sel As Range)
Dim WS As Worksheet
Dim cCell As Range

Set WS = ActiveSheet

'---> Remove X from the table if any
For Each cCell In Sel
    If cCell.Column = Sel.Offset(0, 2).Column Then
        If Left(cCell, 1) = "X" Then cCell = Right(cCell, Len(cCell) - 1)
    End If
Next cCell

'---> Get the Variables for Pivot
CreatePivotTable WS, Sel

End Sub


Sub CreatePivotTable(WS As Worksheet, Sel As Range)
          
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Sel, Version:=xlPivotTableVersion14).CreatePivotTable TableDestination:=WS.Cells(Sel.Row + 1, 1), TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion14
WS.Cells(Sel.Row, 1).Select
With WS.PivotTables("PivotTable1").PivotFields(WS.Cells(Sel.Row, Sel.Offset(, 2).Column).Value)
    .Orientation = xlRowField
    .Position = 1
End With
WS.PivotTables("PivotTable1").AddDataField WS.PivotTables( _
    "PivotTable1").PivotFields(WS.Cells(Sel.Row, Sel.Column).Value), "Sum of No", xlSum

End Sub

Select allOpen in new window

Let me know
gowflow
Macro-to-remove-X-and-create-pivot.xlsm

thanks,

The pivot table should always be at column O, following the title row; meaning if the "title" is at row 46 then pivot should be created Cell O46. (link)

in your macro, the pivot is at A47.

Well you need to make a decision as I already asked you these questions and basis your reply I built the macro. For your convenient here are your answers in the last post before I built the macro:

1) By Title you mean the word Title will be in that cell or it will be an actual tilte
 = the title will be in that cell.
 2) Will it always be in Col A ?
 = it will always be in col A
 3) Will the Title be always on the same row as the selected Table ?
 = yes

So now you changed to Col O

What is it then ? Under the Title where ever the title is or ....?


Pls be clear.
gowflow

Sorry i believe is miscommunication.

1) By Title you mean the word Title will be in that cell or it will be an actual tilte
 = the title will be in that cell. --- I don't mean title of the Pivot table. The title is just a heading for the data that is under it.

 2) Will it always be in Col A ?
 = it will always be in col A  --- by "it", I mean the word "Title" will also be a column A. NOT refering to the pivot.
The word "Title" can be be at cell A10, or A"X" where "X" is a variable.

 3) Will the Title be always on the same row as the selected Table ?
 = yes -- which means if the word "Title" is at cell A10, then the Pivot table will start at cell O10.
The highlighted section ie, the data for the pivot will be F10:H10 and onwards, ie. F10:Hxx, where xx is a variable.

I hope this clarifies.

ok fine here it is.
I gather that you ALWAYS want the pivot in Col O

gowflow
Macro-to-remove-X-and-create-pivot-V01.x

Yes you are right. Always at Col O.

Is it possible to just run as a code via Alt+F8 and not having a button ?

I changed the code slightly to :

Sub CreatePivotTable()
Dim Sel As Range
Set Sel = Application.selection

If Not Sel Is Nothing Then
    If MsgBox("Are you ready to Create a pivot table from the data in Range: " & Sel.Address & " ?", vbQuestion + vbYesNo, "Create Pivot") = vbYes Then
        CreatePivot Sel
    Else
        MsgBox "Request Cancelled by user", vbInformation
    End If
Else
    MsgBox "Nothing have been selected to create a pivot table. You need first to select the data from which you want to create a pivot table then press on the button Create Pivot", vbInformation, "Create Pivot"
End If
End Sub

Select allOpen in new window

and the pivot table starts at cell A47 instead of Column O - Cell O46.

but of course if I use the command button click then it works.

please advise.