a macro to create a pivot table from variable size data? 1

[daggers]

Hi,

I am trying to write a macro that will create a pivot table from a list in an excel worksheet. The list will change in size every time I want the macro run.

Range("B125&quot.Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _"'3M'!R1C2:R25C4&quot.CreatePivotTable TableDestination:="", TableName:= _"PivotTable1"

This is the part of the macro I have a problem with - If x and y is the top left corner of the list, and xx and yy is the bottom right corner of the list, then Range(Cells(y,x),Cells(yy,xx)).Select should replace the first line...but how do I change the 'SourceData' property to reflect variables instead of constants?

TIA
Ross

 

[xlbo]

This should do the trick:

dim srcRng as range
set srcRng = sheets("Sheetname&quot.range(cells(x,y),cells(xx,yy))

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= srcRng).CreatePivotTable TableDestination:="", TableName:= _"PivotTable1"

HTH Rgds
~Geoff~

 

[daggers]

Unfortunately, I get the error message "Application-defined or Object-Defined Error" using the code:

set srcRng = Sheets("Sheetname&quot.Range(Cells(x,y),Cells(xx,yy))

And if I replace Range(Cells(x,y),Cells(xx,yy)) with Range("A225&quot, I then get the error message "Invalid procedure call or argument" with reference to the code:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= srcRng).CreatePivotTable TableDestination:="", TableName:= _"PivotTable1"

So much fun! Any other ideas?

Thanks
Ross

 

[xlbo]

Well, this worked for me (xl97)

Dim srcRng As Range
Set srcRng = Sheets("Sheet1&quot.Range(Cells(1, 1), Cells(215, 2))

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=srcRng, TableDestination:="R1C4", TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1&quot.AddFields RowFields:="Product Name"
ActiveSheet.PivotTables("PivotTable1&quot.PivotFields( _
"Number if Sites in the set with Product&quot.Orientation = xlDataField

Presumably you use 2000 / 2oo2 if you are using the PivotCache method - eiother way, the range reference should work the same Rgds
~Geoff~

 

[BrainB]

May need to check that Tools/References in the VB Editor are the same. (??)

Cannot see why this should be though. Regards
BrianB
** Let us know if you get something that works !
================================

 

[daggers]

I got it to work last night using the code in Geoff's second post. It is (works for Excel 2000):

Dim srcRng As Range
Set srcRng = Sheets("worksheet&quot.Range(Cells(1,2), Cells(x,4))

ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=srcRnga, TableDestination:="'DestWSheet'!R1C1", TableName:="PivotTable1"
ActiveSheet.PivotTables("PivotTable1&quot.SmallGrid=False
ActiveSheet.PivotTables("PivotTable1&quot.AddFields RowFields:="columndata", _ ColumnFields:="rowdata"
ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("sumofdata&quot.Orientation = _ xlDataField


as Geoff said, not sure why Range(cells) did not work but it worked last night.

Thanks for your help
Ross

 

[xlbo]

Well, if you used my exact code, you used the PivotTableWIZARD method rather than the PivotCACHE method you had in your 1st post..... Rgds
~Geoff~

 

[daggers]

?? not sure what you mean - I got it to work by using PivotTableWizard rather than PivotCaches (which the recorder will generate in Excel 2000)

If you look at my code in my last post it is using the PivotTableWizard method.

Ross

 

[xlbo]

That's what I mean - you got it working with the WIZARD method but not the CACHES method which was part of your 1st post. The code you posted in response to my 1st post still used the cache method and that's probably why it failed. The learning point here may be to go to "Help" and find out what the differences between the pivotcache and pivottablewizard method are. I would, but I can't 'cos I only have xl97 Rgds
~Geoff~