VBA for Pivot Table problem

thelostscott

thelostscott

Board Regular
Joined
May 7, 2010
Messages
226
Hi all,

I'm having an issue trying to create a Pivot Table in VBA. How do I get the red section of the code below to reflect a changing array, not a set array as it is showing?

I want to be able to use this Macro in other workbooks where the Source Data will change.

Code: 
Sheets("SOURCE").Range("A1").Select
    
    Range(Selection, Selection.End(xlToRight)).Select
    
    Range(Selection, Selection.End(xlDown)).Select
    
    Sheets.Add
    
[COLOR=Black]ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, [COLOR=Red]SourceData:="SOURCE!R1C1:R26523C46"[/COLOR] Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12[/COLOR]
Thanks
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
You can use something like:
Code: 
Dim strAddress as string

   strAddress = "SOURCE!" & sheets("SOURCE").Range("A1").currentregion.address(referencestyle:=xlr1c1)
    Sheets.Add
    
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=strAddress,  Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:="Sheet2!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion12

or use a dynamic named range.
 
Upvote 0
Try
Code: 
Dim Rng As Range
Set Rng = Range("A1").CurrentRegion
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=Rng, Version:=xlPivotTableVersion12).CreatePivotTable TableDestination:=Range("A3"), TableName:="PT1", DefaultVersion:=xlPivotTableVersion12
 
Upvote 0
Thanks for that! rorya, it didn't seem to like that for some reason but I plugged in Misca's coding and it worked a treat! Thanks very much!
 
Upvote 0
Worked fine for me as long as you have a SOURCE sheet and the new sheet is called Sheet2. If the target sheet is wrong you would get an error so it would be better to specify the sheet name.
Note: in my experience, if you have large data sources, using R1C1 referencing is better than A1 or range objects.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question Question
VBA Coding for adding Pivot Table with changing data table
Replies
6
Views
247
darmst24
D
S
  • Solved
Having trouble with pivot table macro
2
Replies
12
Views
329
RoryA
RoryA
S
  • Solved
Pivot Table Macro
Replies
4
Views
429
SCPbrito
S
S
  • Question Question
Macro for
Replies
2
Views
120
Sanderb
S
C
  • Solved
VBA Add Pivot Table Sheet Name
Replies
1
Views
191
Anthony47
A

Forum statistics

Threads
1,224,620
Messages
6,179,927
Members
452,949
Latest member
beartooth91

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