Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations biv343 on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Automatically Pivot data in Excel 2

Status
Not open for further replies.

DrSmyth

Technical User
Jul 16, 2003
557
GB
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").Select
Range("M89").Activate
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:= _
"'Sols Not Instructed'!R3C1:R89C13", TableDestination:="", TableName:= _
"PivotTable1"
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:= _
"Probate Controller", ColumnFields:="Day's until Sols Instructed Due"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name and Address"). _
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??
 
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...
Code:
=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
Code:
=dbData
Happy Pivotting! ;-)


Skip,
Skip@TheOfficeExperts.com
 
Try using this code:

dim strRows as string

strRows = range("M65536").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").AddFields RowFields:= _
"Probate Controller", ColumnFields:="Day's until Sols Instructed Due"
ActiveSheet.PivotTables("PivotTable1").PivotFields("Name and Address"). _
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.
 
Fantastic.......... I've used Murads idea as it is a bit simpler... Thanks for your help though guys - Stars all round
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top