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

Macro should do the following in sequence:
1. Remove all X the prefix in cells at column H, for example if H5 contains "XYAB" then X should be removed and the result is "YAB"
2. Create a pivot table using data at the highlighted area.
for example if F46:H82 is highlighted, a pivot table will automatically created where the following headers are created for the pivot table:
F46 = No
G46 = Wt  
H46 = Cty

and the pivot table list:
Row Labels = Cty
Sum Values = Sum of No

Location of the pivot table will be in a new sheet named "Pivot [Previous Mth-YY]", ie if i were to create this pivot in the month of Aug 2014, the the sheet name will be "Pivot Aug14".

Thanks.
ceneiqeAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
gowflowConnect With a Mentor Commented:
Here it is you have both options.

If you click on the button it will create it in O46 and if you simply select the area and press on developer tab and choose CreatePivot it will also create it at O46
your choice.

gowflow
Macro-to-remove-X-and-create-pivot-V02.x
0
 
ceneiqeAuthor Commented:
see attached for example Macro-to-remove-X-and-create-pivot.xlsx
0
 
gowflowCommented:
Noted everything but you created the pivot in Cell B3 and this was not mentioned is it always in cell B3 ?

gowflow
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
ceneiqeAuthor Commented:
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!
0
 
gowflowCommented:
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
0
 
ceneiqeAuthor Commented:
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.
0
 
gowflowCommented:
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

Open in new window


Let me know
gowflow
Macro-to-remove-X-and-create-pivot.xlsm
0
 
ceneiqeAuthor Commented:
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.
0
 
gowflowCommented:
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
0
 
ceneiqeAuthor Commented:
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.
0
 
gowflowCommented:
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
0
 
ceneiqeAuthor Commented:
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

Open 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.
0
All Courses

From novice to tech pro — start learning today.