This is the best explanation to creating a pivot table from Dynamic Range I've been able to find. So many unnecessary complications on some of the other websites and videos. Thanks man.
I have been struggling with this issue for way too long than I care to admit. Thank you so much for explaining it in simple terms and not trying to make it complicated.
Great video, I'm glad I have found this channel. I have a couple of questions : What if we had already an existent sheet where we want to place the pivot table ? What should we change in this case ? What do we do to stop the macro from creating several sheets with the same outcome every time we execute this code ?
Very informative & helpful. Just wanted to know why I am not able to run the micro if i do the pivot on the same worksheet? What changes need to be made?
Thanks a lot man, this is the best explanation I watched and helped me a lot. I only have a question about; it could be possible associate the datasource to a the active sheet in the book that somene is using no matter the names of the others sheets. I mean, is it possible to execute the same macro in every workbook's sheet?
What do I do if I need to create multiple Pivot Tables within the same worksheet? Do I have to rename the 'pivotWS' for each pivot table? (ie., pivotWS1 for the first pivot table and then pivotWS2 for another one?
Please help. In your video yiu have told how to take data upto the last row. I need to know how can we take data upto the last second row of data. How to work it out in VBA. Please reply.
Ater hours of searching, found all I need in this video. But one last thing, is there a way I could get the additional columns of data I add? Like you did to get additional rows of data you added
Pivot in same worksheet - I have recorded the macro placing the pivot table in the same worksheet - however when I rerun the macro it stops at the following point: Range("V2").Select ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Table1", Version:=6).CreatePivotTable TableDestination:= _ "New Hires_SAP_KDVS02!R2C22", TableName:="PivotTable3", DefaultVersion:=6 How can I fix this?
I have a Master data sales report . I slice the data into separate worksheets by customer name wise. The number of rows on each sheet vary depending upon data each month. I need a macro to create a pivot table at the bottom of the data on each sheet for further working. I tried after watching this video but it's not working. Please help
Text values in value field gets converted to number.. But i want it to remain as text... I have recorded it by adding data model and using ConcateneX but this doesn't work for other sheets. Could u please tell how to add datamodel and give text in pivot table value filed.
@@ExcelGoogleSheets I'm having the same issue, but I made sure the format was exactly like what you did in your video. Any help would be greatly appreciated!
@@ExcelGoogleSheets Sir, I've been your subscribers for years. Please make a video on this topic. Yes sir, it has to be through VBA. Because we are working on office 2007. So there is no Power Query and PowerPivot.
When ever i add variable as pivotws & "!R3C1" It is throwing an error as invalid procedure call or argument (error 5) Please give me a solution for it 4:23 Whenever I use concatenation it is giving the error
Thank for this informative video. Could you explain what to do if the field is the same for column area as well as the Value area? because then, it only considers the field in the value area and continues to run the subroutine.
The explanation is good. I have tried this in my computer and it works fine. But I have a doubt. How does it not through an error as the variable is not declared?
Thanks for the information, just wanted to know, if i want to name the output tab any specific other than "sheet 2", how can i do that? Also will it work for multiple pivots in different tabs from sheet 1 in same sheet? Thanks in advance!
I’m facing an issue where I’m in the middle of recording a macro with pivot table and when I click the “Do Not Show Subtotals” button under Design tab, where’s an error pops up saying “unable to record”. Is there anyway to solve this? Thanks
Try adding this step manually instead of recording, Dim PvtTbl As PivotTable Dim pvtFld As PivotField Set PvtTbl = ActiveSheet.PivotTables(1) 'hide Subtotals for all fields in the PivotTable . With PvtTbl For Each pvtFld In .PivotFields pvtFld.Subtotals(1) = True pvtFld.Subtotals(1) = False Next pvtFld End With
It is not working for me.I changed the name from Sheet2 to PivotWS as per the video,bt it didnt work out,I am using excel 2013.Please let me know any other solution. I need this urgently.
@@ExcelGoogleSheets thanks, I did. but have got another kind of error for the last line in this code: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "08.04.2022 (2)!R1C1:R19C9", Version:=6).CreatePivotTable TableDestination _ :=pivotWS & "!R3C1", TableName:="PivotTable1", DefaultVersion:=6
I followed step by step, when I added lr and tested the Macro, I get the error '1004' saying Pivottable Field name not valid. Here is code: Sub PivotEmployeesPriojectTask() ' ' PivotEmployeesPriojectTask Macro ' Pivot employees on project-task by earnings period end date. ' ' lr = Cells(Rows.Count, 1).End(xlUp).Row
Sheets.Add pivotWS = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _ "Salary-Fringe Combined_1!A1:BO" & lr, Version:=6).CreatePivotTable _ TableDestination:=pivotWS & "!R3C1", TableName:="PivotTable1", _ DefaultVersion:=6 Sheets(pivotWS).Select Cells(3, 1).Select With ActiveSheet.PivotTables("PivotTable1").PivotFields("CCOA TASK Code") .Orientation = xlRowField .Position = 1 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name") .Orientation = xlRowField .Position = 2 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee ID") .Orientation = xlRowField .Position = 3 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Transaction Type") .Orientation = xlRowField .Position = 4 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields("Journal Template Code" _ ) .Orientation = xlRowField .Position = 5 End With With ActiveSheet.PivotTables("PivotTable1").PivotFields( _ "Earnings Period End Date") .Orientation = xlColumnField .Position = 1 End With ActiveSheet.PivotTables("PivotTable1").PivotFields("Earnings Period End Date"). _ AutoGroup ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _ "PivotTable1").PivotFields("Monetary Amount"), "Sum of Monetary Amount", xlSum Range("B8").Select With ActiveSheet.PivotTables("PivotTable1").PivotFields( _ "Sum of Monetary Amount") .NumberFormat = "$#,##0.00" End With End Sub
This is the best explanation to creating a pivot table from Dynamic Range I've been able to find. So many unnecessary complications on some of the other websites and videos. Thanks man.
Thanks!
Dude, you solved my long time problem in minutes, I was desperately looking for this video, Thanks a ton.
I have been struggling with this issue for way too long than I care to admit. Thank you so much for explaining it in simple terms and not trying to make it complicated.
I was getting very frustrated trying to create a Pivot table by recording. This worked perfectly. Thank you.
Atlast I found the correct UA-cam channel for What I exactly want
You just fixed a problem I gave up on yesterday by doing what I wouldn't advise anyone to do in coding. Thanks, you are a blessing !!!
Awesome tutorial, thank you for taking the time and sharing!
You Sire are a Legend and a Scholar! Thank you!
Simply Awesome....loved it. Thanks a lot for this lucid tutorial!
Thank you. Your explanation was clear and this helped me complete the macro without giving up.
Excellent explanation. Wish I saw this video first. Would of saved me hours trying to find the answer.
excellent explanation
This trick worked! Thank you for the video!
This is very workable pivot macro! Thank you.
Glad it was helpful!
The best video so far....Hope to learn more from you. Thank you so much
Happy to hear that!
Amazing explaination. Simple and easy to grab. :) Thank you so much for sharing.
Great to hear!
This helped! Thanks a lot for making this video !!! :)
This video was really helpful. Thanks buddy !
This is what I exactly 💯 want without unwanted things !!! How to auto genarate pivot VBA excel macro thanks man
Great video, I'm glad I have found this channel.
I have a couple of questions :
What if we had already an existent sheet where we want to place the pivot table ? What should we change in this case ?
What do we do to stop the macro from creating several sheets with the same outcome every time we execute this code ?
Thanks Super. I really happy to see this type of videos
you are a legend Sir! thanks for the easy example
You are a god, thank you so much
Excellent training. Good job!
Thank you!
Very informative & helpful. Just wanted to know why I am not able to run the micro if i do the pivot on the same worksheet? What changes need to be made?
Thanks for sharing.
You saved my career
:)
Man, you are awsomeee, do more of those plz.
Quite helpful. Thankyou ! for sharing
Thank you! You are a genius!!!!!!!!!!
Thank you so much for this video!
Bro! I just love you 😘😘😘😘😘😘😘
👍
Thanks!
Thanks a lot man, this is the best explanation I watched and helped me a lot. I only have a question about; it could be possible associate the datasource to a the active sheet in the book that somene is using no matter the names of the others sheets. I mean, is it possible to execute the same macro in every workbook's sheet?
Thank you for sharing this truly useful video. 👏 🌟 🌟
Great Video and tutorial good sir! You've taught me how to understand VBA. But I have a question, How do I make column range dynamic as well?
Thank u sir
You information is very helpfull for my job👍
:)
What do I do if I need to create multiple Pivot Tables within the same worksheet? Do I have to rename the 'pivotWS' for each pivot table? (ie., pivotWS1 for the first pivot table and then pivotWS2 for another one?
Please help. In your video yiu have told how to take data upto the last row. I need to know how can we take data upto the last second row of data. How to work it out in VBA. Please reply.
Thanks, Great Help 💻💻 Best Explaination
Ater hours of searching, found all I need in this video. But one last thing, is there a way I could get the additional columns of data I add? Like you did to get additional rows of data you added
Thanks for the video and information! Is there a way to use last row and last column as the pivot table range?
Getting a message that the macro cannot run in break mode. What is it?
Good!
Thanks!
Hi Brother, My code shows error when I change to "'" & dataWS & "'!A1:F" Could you please help ???
Pivot in same worksheet - I have recorded the macro placing the pivot table in the same worksheet - however when I rerun the macro it stops at the following point:
Range("V2").Select
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Table1", Version:=6).CreatePivotTable TableDestination:= _
"New Hires_SAP_KDVS02!R2C22", TableName:="PivotTable3", DefaultVersion:=6
How can I fix this?
amazing
Thank you! Cheers!
Hey! Is there any way to make pivot table by dynamic column... You shows dynamic rows...plz tell me dynamic column also
I have a Master data sales report . I slice the data into separate worksheets by customer name wise. The number of rows on each sheet vary depending upon data each month. I need a macro to create a pivot table at the bottom of the data on each sheet for further working. I tried after watching this video but it's not working. Please help
Text values in value field gets converted to number.. But i want it to remain as text... I have recorded it by adding data model and using ConcateneX but this doesn't work for other sheets.
Could u please tell how to add datamodel and give text in pivot table value filed.
Bro You missed to tell how the last column code works, as for my data column increases
Facing issues while specifying the source data from R1C1 syntax to regular syntax , throwing me pivot table field name is not valid
Make sure you include headers in your range and there should be no blanks or duplicate names in headers.
@@ExcelGoogleSheets I'm having the same issue, but I made sure the format was exactly like what you did in your video. Any help would be greatly appreciated!
It's easy when the header of table no change..
How if the header change?
How can I create Macro If i need filter the data?
love you
Sir, would you kindly make a video on "How to split number and text from an Alphanumeric String through VBA UDF?"
Power Query has a build-in solution for this. Does it have to be VBA?
@@ExcelGoogleSheets Sir, I've been your subscribers for years. Please make a video on this topic.
Yes sir, it has to be through VBA. Because we are working on office 2007. So there is no Power Query and PowerPivot.
Check this out stackoverflow.com/questions/48513694/excel-vba-split-numbers-and-text-from-a-string-into-columns
When ever i add variable as pivotws & "!R3C1"
It is throwing an error as invalid procedure call or argument (error 5)
Please give me a solution for it 4:23
Whenever I use concatenation it is giving the error
what in pivotws ?
@@ExcelGoogleSheets pivotws is a variable in the code which refers to the sheet
as string or as sheet object?
Thank for this informative video. Could you explain what to do if the field is the same for column area as well as the Value area?
because then, it only considers the field in the value area and continues to run the subroutine.
If you move .AddDataField line above .PivotFields with blocks, it should fix your problem.
The explanation is good. I have tried this in my computer and it works fine. But I have a doubt. How does it not through an error as the variable is not declared?
It will only force you to declare variables if you have Option Explicit on top.
Thanks for the information, just wanted to know, if i want to name the output tab any specific other than "sheet 2", how can i do that? Also will it work for multiple pivots in different tabs from sheet 1 in same sheet?
Thanks in advance!
Add
activesheet.name = "New Name"
by the end of the code
and yes.
Hi, I am getting "Compile Error : Variable not defined", when I add pivotWS variable. Do I need to change some setting?
I was using Open Explicit. Removing that solved it.
I’m facing an issue where I’m in the middle of recording a macro with pivot table and when I click the “Do Not Show Subtotals” button under Design tab, where’s an error pops up saying “unable to record”. Is there anyway to solve this? Thanks
Try adding this step manually instead of recording,
Dim PvtTbl As PivotTable
Dim pvtFld As PivotField
Set PvtTbl = ActiveSheet.PivotTables(1)
'hide Subtotals for all fields in the PivotTable .
With PvtTbl
For Each pvtFld In .PivotFields
pvtFld.Subtotals(1) = True
pvtFld.Subtotals(1) = False
Next pvtFld
End With
Sir, it occur a “424” error (object required), in debugger, it highlighting “lr=Cells(Row.Court. 1).End(xlUp).Row”, how can I solve it? Thank you.
rows.count
why does it seems that you can assign var right away, while me need to declare it first ? like 'dim Name As String' ??
If you remove "option explicit" on top then you won't have to declare the variables.
Hey, after giving pivotWS as activesheet name, still getting error, "Can't execute code in breaking mode"?
Reset, "Stop" button.
I am getting error as Cannot execute code in break mode. Please help
Plz respond
It is not working for me.I changed the name from Sheet2 to PivotWS as per the video,bt it didnt work out,I am using excel 2013.Please let me know any other solution.
I need this urgently.
This works in Excel 2013. Check your code.
I followed the same as you have told in this video but I was getting a error stating "cant execute code in break mode"
Hit "reset" button. Same as stop button on old players.
@@ExcelGoogleSheets I got same error and fixed by reset button
thanks a lot
huh complex explanation
Thanks for this video, how to change the Pivot sheet name
ActiveSheet.Name = "New Name"
Mine says can't execute in break mode. Help
Hit Reset button.
I literally cannot get this to work
Hi, when i write: TableDestination _
:= pivotWS&"!R3C1" I get Compile error Expected: end of statement. Could somebody help here?
try adding a space before and after &
@@ExcelGoogleSheets thanks, I did. but have got another kind of error for the last line in this code: ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"08.04.2022 (2)!R1C1:R19C9", Version:=6).CreatePivotTable TableDestination _
:=pivotWS & "!R3C1", TableName:="PivotTable1", DefaultVersion:=6
@@annapetmikel4356 You didn't update SourceData like mentioned in the video.
Thanks for the video. Can I add a name to the new worksheet?
Sure.
ActiveSheet.Name = "New Name"
Hi This is not worked in office 365
It works fine in office 365 so long as you are not using the web browser version.
i got error after following ur steps
it gives me error when i try to use A1 and F22 formula
I followed step by step, when I added lr and tested the Macro, I get the error '1004' saying Pivottable Field name not valid. Here is code:
Sub PivotEmployeesPriojectTask()
'
' PivotEmployeesPriojectTask Macro
' Pivot employees on project-task by earnings period end date.
'
'
lr = Cells(Rows.Count, 1).End(xlUp).Row
Sheets.Add
pivotWS = ActiveSheet.Name
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Salary-Fringe Combined_1!A1:BO" & lr, Version:=6).CreatePivotTable _
TableDestination:=pivotWS & "!R3C1", TableName:="PivotTable1", _
DefaultVersion:=6
Sheets(pivotWS).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("CCOA TASK Code")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee Name")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Employee ID")
.Orientation = xlRowField
.Position = 3
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Transaction Type")
.Orientation = xlRowField
.Position = 4
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Journal Template Code" _
)
.Orientation = xlRowField
.Position = 5
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Earnings Period End Date")
.Orientation = xlColumnField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").PivotFields("Earnings Period End Date"). _
AutoGroup
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Monetary Amount"), "Sum of Monetary Amount", xlSum
Range("B8").Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Sum of Monetary Amount")
.NumberFormat = "$#,##0.00"
End With
End Sub
Any help on this would be greatly appreciated!
Which line is highlighted in yellow when you get this error?
Thanks!
Thank You!