Creating a Pivot Table

[pyromaniac]

I need to create a pivot table of the amount of sales, the problem is the size of the data block varies, from month to month eg.january is 25 cells down, and february is 100 cells down. How can i write a macro that selects only the required data, so as it can be put into a pivot table?

 

[ajcarr]

I have a standard Subroutine which I use for creating Pivot Tables with variable data fields. Below is a stripped down version. The master is much more complex but this shows the basics.

Datasheet is a string containing the worksheet name
HeaderRow is an integer cotaining the row number of the Pivot Table headings
Startcol is the starting column for the PivotTable
Endline and Endcol are integers (Endline may need to be Long with big worksheets)

First set a string variable as follows

DataRange = DataSheet + "!R" + CStr(HeaderRow) + "C" +Cstr(Startcol)+":R" + CStr(EndLine) + "C" + CStr(EndCol)

Pivot_Table DataRange, "Table Name", "Field Name", "Column Name"
..
..
Sub Pivot_Table(DataRange, Table_Name, Field_Name, Column_Name)
ActiveSheet.PivotTableWizard SourceType:=xlDatabase, SourceData:=DataRange, TableDestination:="", TableName:=Table_Name
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.PivotTables(Table_Name).PivotSelect "", xlOrigin
ActiveSheet.PivotTables(Table_Name).AddFields RowFields:=Field_Name, ColumnFields:=Column_Name
ActiveSheet.PivotTables(Table_Name).PivotFields(Field_Name).Orientation = xlDataField
End Sub

 

[pyromaniac]

Thanks very much it seems to be working O.K.
What does the master version look like?

 

[pyromaniac]

Sorry ajcarr the macro ain't working, i need more help, but thanks for trying.