Hi All!
First post on the Excel Forum.

I've had quite a bit of experience with using VBA for Access, but haven't gotten into VBA with Excel.

I've been assigned the task of making multiple pivot tables based on a sheet of given data.

The program will start with 1 sheet in the workbook having 20 something rows of data. I want to automate this workbook somehow, probably via VBA, to spit out a couple of pivot charts that give different views of the data. I need these charts to be put on seperate sheets in the workbook.

I've been surfing the net some looking for ways to do this, but most sites don't go very indepth with VBA.

Any advise, strategy, or ideas are greatly appreciated.

Thanks,
David

Pivot tables are a breed unto themselves and will take a lot of explanation. For ease of explaining we'll assume that one column is called "Row Data", one column is called "Column Data" and one row is called "Page Data". We'll assume you have 20 rows of data and 10 columns (A-J).

---------------------------------------------------------------------------------------
  dim oTarget As Worksheet
  ActiveWorkbook.Sheets.Add After:="Sheet1"
  ActiveWorkbook.Activesheet.Name = "Pivot A" 'Whatever you want tab name to be
  set oTarget = ActiveWorkbook.Worksheets("Pivot A")
  ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlDatabase, _
    SourceData:= "'Sheet1'!A1:J20").CreatePivotTable _
    TableDestination:=oTarget.Range("A3"), _
    TableName:="PivotA"
  oTarget.PivotTables("PivotA").SmallGrid = False 'Never found a good use for true yet
  oTarget.PivotTables("PivotA").AddFields RowFields:="Row Data", ColumnFields:="Column Data", PageFields:="Page Data"
  With oTarget.PivotTables("PivotA").PivotFields("Page Data") 'Orientation for data fields has to be set manually
    .Orientation = xlDataField
    .Caption = "Count of Page Data"
    .Function = xlCount 'Count of rows that match set criteria.
  End With
---------------------------------------------------------------------------------------

Pivot tables are really complex and there is a lot more that you can do, but this should get you started to the point that VBA help and fiddling will get you the results you want.

Hope this helps.

Thanks a bunch allenm!

I'm gonna give this a shot.

No problem. I just edited my message and updated the code. I missed a " mark for the SourceData:= line.

My bad, sorry if it confused you.