excel macro
 

1. copies 3 spreadsheet columns
2. pastes into new workbook
3. insets 2 columns and creates month value from date in col.A
4. selects the active range in columns C, D, E and creates 2 pivot
tables
It fails in the pivot table operation. Also should be more efficient
by using the range twice for the 2 pivot tables.

' Macro recorded 9/27/2004 by lander11
'
ActiveWindow.LargeScroll ToRight:=1
Range("L:L,S:S,U:U").Select
Range("U1").Activate
Selection.Copy
Workbooks.Add Template:="Workbook"
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=False
Range("B4").Select
Application.CutCopyMode = False
' insert 2 columns
Selection.EntireColumn.Insert
Selection.EntireColumn.Insert
Range("D4").Select
Dim lastRow As Long
lastRow = Cells(Rows.Count, ActiveCell.Column).End(xlUp).Row
Range("C4").Select
ActiveCell.FormulaR1C1 = "mon"
Range("C5").Select
ActiveCell.FormulaR1C1 = "=MONTH(RC[-2])"
Range("C5").Select
Selection.AutoFill Destination:=Range("C5:C" & lastRow)
' column C only
Dim xyz As Range
Range("C4").Select
Intersect(ActiveCell.CurrentRegion,
ActiveCell.EntireColumn).Select
Selection.Offset(0, 0).Resize(Selection.Rows.Count,
Selection.Columns.Count + 2).Select
Selection.Name = "xyz"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, _
SourceData:=xyz).CreatePivotTable TableDestination:="", _
TableName:="PivotTable5" ' DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable5").SmallGrid = False
ActiveSheet.PivotTables("PivotTable5").AddFields
RowFields:="CUST_CONC_CD", _
ColumnFields:="mon"
With ActiveSheet.PivotTables("PivotTable5").PivotFields ("mon")
.Orientation = xlDataField
.Caption = "Count of mon"
.Function = xlCount
End With
Sheets("Sheet1").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Sheet1!R4C3:R999C5").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable6"
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable6").SmallGrid = False
ActiveSheet.PivotTables("PivotTable6").AddFields RowFields:= _
"PART_NUM_CAUS_BASE", ColumnFields:="mon"
With ActiveSheet.PivotTables("PivotTable6").PivotFields ("mon")
.Orientation = xlDataField
.Caption = "Count of mon"
.Function = xlCount
End With
End Sub