Solved

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

Posted on 2014-09-25
12
802 Views
Last Modified: 2014-10-10
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.
0
Comment
Question by:ceneiqe
  • 6
  • 6
12 Comments
 

Author Comment

by:ceneiqe
Comment Utility
see attached for example Macro-to-remove-X-and-create-pivot.xlsx
0
 
LVL 29

Expert Comment

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

gowflow
0
 

Author Comment

by:ceneiqe
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:ceneiqe
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:ceneiqe
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:ceneiqe
Comment Utility
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
 
LVL 29

Expert Comment

by:gowflow
Comment Utility
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
 

Author Comment

by:ceneiqe
Comment Utility
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
 
LVL 29

Accepted Solution

by:
gowflow earned 500 total points
Comment Utility
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

Featured Post

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
In this article we discuss how to recover the missing Outlook 2011 for Mac data like Emails and Contacts manually.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…

762 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now