Automatically Pivot data in Excel 2

[DrSmyth]

Hi,

I've got a spreadsheet that i need to run a pivot from every night. The spreadsheet is created on a daily basis and the amount of rows in it will change everytime.

I've recorded myself pivoting the data and get this

Sub tektips()

Range("A3:M89&quot.Select
Range("M89&quot.Activate
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Sols Not Instructed'!R3C1:R89C13", TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTables("PivotTable1&quot.AddFields RowFields:= _
"Probate Controller", ColumnFields:="Day's until Sols Instructed Due"
ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Name and Address&quot. _
Orientation = xlDataField
End Sub

But the problem is that the number of rows to be included changes everytime.

Does anybody have any suggestions how i can change this code to accomodate changes in row numbers or an alternative suggestion??

 

[SkipVought]

Hey Doc,

Name the data table range dynamically using the OFFSET function.

Menu item Insert/Name/Define -- give your table range a name and then use this expression in the RefersTo Textbox...

=OFFSET(PerishableTools!$A$1,0,0,COUNTA(PerishableTools!$A:$A),COUNTA(PerishableTools!$1:$1))

Then in the range textbox in the PivatTable Wizard, enter you tabe range name -- mine is dbData

=dbData

Happy Pivotting! ;-)


Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[murad5]

Try using this code:

dim strRows as string

strRows = range("M65536&quot.end(xlUp).row ' find out how many rows there are
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Sols Not Instructed'!R3C1:R"+strRows+"C13", TableDestination:="", TableName:= _
"PivotTable1"
' This tells the wizard what size the data range it needs to populate the pivot table is
ActiveSheet.PivotTables("PivotTable1&quot.AddFields RowFields:= _
"Probate Controller", ColumnFields:="Day's until Sols Instructed Due"
ActiveSheet.PivotTables("PivotTable1&quot.PivotFields("Name and Address&quot. _
Orientation = xlDataField

Presuming your data always starts in row 3 and goes from column A to column M, this should work for any number of rows.

 

[DrSmyth]

Fantastic.......... I've used Murads idea as it is a bit simpler... Thanks for your help though guys - Stars all round