Pivot tables and VBA (looked at other threads!)

S

slay0r

Board Regular
Joined
Jul 8, 2011
Messages
231
Hi Guys,I'm getting really confused over VBA and pivot tables.My data will change week in and week out so the sizes of the spreadsheet will be different. Haven't got a clue how to do that with my current code that I have! I also don't want to end up with any "N/A columns in it!"
Code: 
Sub createpivot()
'
' createpivot Macro
' Macro recorded 23/11/2011 by jmartin
'
'
   ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                                  "Sheet3!R1C1:R8471C44").CreatePivotTable TableDestination:="", TableName:= _
                                  "PivotTable2", DefaultVersion:=xlPivotTableVersion10
   ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
   ActiveSheet.Cells(3, 1).Select
   ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Alphaname ", _
                                                    ColumnFields:="Import/Export"
   ActiveSheet.PivotTables("PivotTable2").PivotFields("Over 45 Days").Orientation _
         = xlDataField
   Application.CommandBars("PivotTable").Visible = False
   ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
I haven't really got a lot of knowledge so I don't know how the above is working on that, do I need to change the range and that's it or is there more to it? Also Pivottable2? Is that because I tested one out before, do I need to change this as I won't be saving the spreadsheet and the first one won't be there tomorrow? Need a VBA course baaaadly!
 
Last edited by a moderator:

Excel Facts

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
Sort by date Sort by votes
slay0r said:
Hi Guys,I'm getting really confused over VBA and pivot tables.My data will change week in and week out so the sizes of the spreadsheet will be different. Haven't got a clue how to do that with my current code that I have! I also don't want to end up with any "N/A columns in it!"Sub createpivot()'' createpivot Macro' Macro recorded 23/11/2011 by jmartin'' ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _ "Sheet3!R1C1:R8471C44").CreatePivotTable TableDestination:="", TableName:= _ "PivotTable2", DefaultVersion:=xlPivotTableVersion10 ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Alphaname ", _ ColumnFields:="Import/Export" ActiveSheet.PivotTables("PivotTable2").PivotFields("Over 45 Days").Orientation _ = xlDataField Application.CommandBars("PivotTable").Visible = False ActiveWorkbook.ShowPivotTableFieldList = False End SubI haven't really got a lot of knowledge so I don't know how the above is working on that, do I need to change the range and that's it or is there more to it? Also Pivottable2? Is that because I tested one out before, do I need to change this as I won't be saving the spreadsheet and the first one won't be there tomorrow? Need a VBA course baaaadly!
Click to expand...

Did try and do this properly but it wouldn't let me put spaces in between lines..GRR
 
Upvote 0
Try using code tags in the future, it makes things easier to read, to do that press the # button in the message field of your post and put your code in between the tags that appear

Back to your question, try this out, it's a nice easy code to create new pivots easily:

Code: 
Sub createpivot()

NewAddress = Sheets("SHEET NAME HERE").[A1].CurrentRegion.Address(ReferenceStyle:=xlR1C1)
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="SHEET NAME HERE!" & NewAddress).CreatePivotTable _
    TableDestination:="", TableName:="TABLE NAME HERE", DefaultVersion:=xlPivotTableVersion10
End Sub

Just punch in the sheet your pulling the data from where it says SHEET NAME HERE and the table name (whatever you want it to be) where it says TABLE NAME HERE
 
Last edited:
Upvote 0
#ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="sheet3" & NewAddress).CreatePivotTable _ TableDestination:="", TableName:="over1000", DefaultVersion:=xlPivotTableVersion10#Don't get why it's stopped on this. I've put in everything you said but it's just debugging on me saying the above is incorrect. How would this do my pivot as well? Don't see how it's selecting certain columns etc out of the data, can you explain it to me a bit?The help is very much appreciated by the way, thanks for the quick response last night!Tried pressing # and it's just done absolutely nothing for me, quite frustrating.
 
Upvote 0
Can anyone help me? :( Completely lost on this, put a post on the other forums asking for some advice on courses etc to help me with all of this in the future, I just need help with this or I can't progress with anything else in my project until this issue is fixed, I've almost hit the point I need it and it's still not working!
 
Upvote 0
Try this - I've added some comments so that hopefully you can see what is happening:
Code: 
Sub createpivot()
'
' createpivot Macro
' Macro recorded 23/11/2011 by jmartin
'
'
   Dim PC                As Excel.PivotCache
   Dim PT                As Excel.PivotTable
   Dim wksPT             As Excel.Worksheet

   ' add sheet for pivot table
   Set wksPT = Worksheets.Add

   ' create cache using table starting in A1
   With Sheets("Sheet3")
      Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                                              "'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1))
   End With
   ' create pivot table from cache, on new sheet at A3
   Set PT = PC.CreatePivotTable(TableDestination:=wksPT.Cells(3, 1), TableName:= _
                                "PivotTable2", DefaultVersion:=xlPivotTableVersion10)
   ' add one row field and one column field
   PT.AddFields RowFields:="Alphaname ", ColumnFields:="Import/Export"
   ' add data field
   PT.PivotFields("Over 45 Days").Orientation = xlDataField
   Application.CommandBars("PivotTable").Visible = False
   ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
 
Upvote 0
Rory..you sir are a GOD. Thank you very much for that explanation as well, really appreciated because now I can see how it's working!Bamblack thanks for your help as well, I'm just a really, really bad novice so it's a bit confusing.
 
Last edited by a moderator:
Upvote 0
Hey Guys,

Rory gave me the below code. It was working fine up until now when I've copied it across to a blank excel spreadsheet and tried to run it. It's falling over on the bolded text below saying I need more than 2 rows of source data for it to work? I thought I was setting those below but do I need to combine the two so it does it at the same time? Bit confused now.

Code: 
Sub createpivot7()
'
' createpivot Macro
' Macro recorded 23/11/2011 by jmartin
'
'
   Dim PC                As Excel.PivotCache
   Dim PT                As Excel.PivotTable
   Dim wksPT             As Excel.Worksheet
   ' add sheet for pivot table
   Set wksPT = Worksheets.Add
   ' create cache using table starting in A1
   With Sheets("Sheet3")
      Set PC = ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
                                              "'" & .Name & "'!" & .Range("A1").CurrentRegion.Address(ReferenceStyle:=xlR1C1))
   End With
  [B] ' create pivot table from cache, on new sheet at A3
   Set PT = PC.CreatePivotTable(TableDestination:=wksPT.Cells(3, 1), TableName:= _[/B]
                                "PivotTable2", DefaultVersion:=xlPivotTableVersion10)
   ' add one row field and one column field
   PT.AddFields RowFields:="Alphaname ", ColumnFields:="Import/Export"
   ' add data field
   PT.PivotFields("Over 45 Days").Orientation = xlDataField
   Application.CommandBars("PivotTable").Visible = False
   ActiveWorkbook.ShowPivotTableFieldList = False
End Sub
 
Upvote 0
Do you have data on Sheet3? If so, does it start in A1?
 
Upvote 0
You must log in or register to reply here.

Similar threads

U
  • Solved
Re-organizing pivot table fields in vba
Replies
2
Views
587
ummjay
U
U
  • Solved
VBA question involving 2 different workbooks
Replies
2
Views
408
ummjay
U
U
  • Question
VBA to look for an open file
Replies
1
Views
513
NateSC
NateSC
S
  • Solved
VBA to create pivot tables
2
Replies
12
Views
1K
Domenic
D
S
  • Question
VBA Pivot Table
Replies
3
Views
1K
Domenic
D

Forum statistics

Threads
1,215,051
Messages
6,122,871
Members
449,097
Latest member
dbomb1414

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top