ceneiqe
asked on
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.
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.
Noted everything but you created the pivot in Cell B3 and this was not mentioned is it always in cell B3 ?
gowflow
gowflow
ASKER
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!
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
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
ASKER
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.
= 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
Let me know
gowflow
Macro-to-remove-X-and-create-pivot.xlsm
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
Let me know
gowflow
Macro-to-remove-X-and-create-pivot.xlsm
ASKER
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.
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:
So now you changed to Col O
What is it then ? Under the Title where ever the title is or ....?
Pls be clear.
gowflow
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
ASKER
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.
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
I gather that you ALWAYS want the pivot in Col O
gowflow
Macro-to-remove-X-and-create-pivot-V01.x
ASKER
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 :
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.
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
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER