I ran your macro at the end of this one. Below is part of the macr
after the import.....
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:
_
"AAASSY!R6C1:R9617C13").CreatePivotTable TableDestination:=""
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3
1)
ActiveSheet.Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Par
Number")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataFiel
ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Total Cost"), "Sum of Total Cost"
xlSum
Range("B5").Select
Selection.Sort Key1:="R5C2", Order1:=xlDescending
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Range("A1").Select
Application.CommandBars("PivotTable").Visible = False
Range("B3").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect ""
xlDataAndLabel, True
Selection.Copy
Range("D3").Select
ActiveSheet.Paste
Columns("D:D").EntireColumn.AutoFit
Range("D4").Select
ActiveSheet.PivotTables("PivotTable6").PivotFields ("Par
Number").Orientation _
= xlHidden
With ActiveSheet.PivotTables("PivotTable6").PivotFields ("Dep
Held")
.Orientation = xlRowField
.Position = 1
End With
Range("E5").Select
Selection.Sort Key1:="R5C5", Order1:=xlDescending
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
ActiveWorkbook.ShowPivotTableFieldList = False
Range("E3").Select
ActiveSheet.PivotTables("PivotTable6").PivotSelect ""
xlDataAndLabel, True
Selection.Copy
Range("G3").Select
ActiveSheet.Paste
Columns("G:G").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveWorkbook.ShowPivotTableFieldList = True
Range("G4").Select
ActiveSheet.PivotTables("PivotTable7").PivotFields ("Dep
Held").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable7").PivotFields ("Ship")
.Orientation = xlRowField
.Position = 1
End With
Range("H5").Select
Selection.Sort Key1:="R5C8", Order1:=xlDescending
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
ActiveWorkbook.ShowPivotTableFieldList = False
Range("A1").Select
ActiveCell.FormulaR1C1 = "By Part Number"
Range("D1").Select
ActiveCell.FormulaR1C1 = "By Department"
Range("G1").Select
ActiveCell.FormulaR1C1 = "By Ship Serial"
Range("A1:G1").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A2").Select
Sheets("Sheet1").Select
Sheets("Sheet1").Name = "Cost Summary"
Sheets("Cost Summary").Select
Sheets("Cost Summary").Move After:=Sheets(2)
Sheets("AAASSY").Select
ActiveSheet.Shapes.AddShape(msoShapeRectangle, 276#, 15.75, 346.5
27.75). _
Select
ActiveSheet.Shapes("Rectangle 1").Select
Selection.Characters.Text = _
"Data sorted by Total Hours (Column F)" & Chr(10) & "Click o
Cost and Hours Summary tabs at the bottom for rollup data"
With Selection.Characters(Start:=1, Length:=104).Font
.Name = "Arial"
.FontStyle = "Regular"
.Size = 10
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
With Selection
.Placement = xlMoveAndSize
.PrintObject = False
End With
Selection.ShapeRange.ScaleWidth 0.91, msoFalse
msoScaleFromTopLeft
Selection.ShapeRange.ScaleWidth 0.97, msoFalse
msoScaleFromTopLeft
Selection.ShapeRange.Fill.Visible = msoTrue
Selection.ShapeRange.Fill.Solid
Selection.ShapeRange.Fill.ForeColor.SchemeColor = 65
Selection.ShapeRange.Fill.Transparency = 0#
Selection.ShapeRange.Line.Weight = 0.75
Selection.ShapeRange.Line.DashStyle = msoLineSolid
Selection.ShapeRange.Line.Style = msoLineSingle
Selection.ShapeRange.Line.Transparency = 0#
Selection.ShapeRange.Line.Visible = msoTrue
Selection.ShapeRange.Line.ForeColor.SchemeColor = 12
Selection.ShapeRange.Line.BackColor.RGB = RGB(255, 255, 255)
Range("C10").Select
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:=
_
"AAASSY!R6C1:R617C13").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable1", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3,
1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Part
Number"
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Total
Hours").Orientation _
= xlDataField
ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count of Total
Hours"). _
Function = xlSum
Range("B5").Select
Selection.Sort Key1:="R5C2", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
Application.CommandBars("PivotTable").Visible = False
ActiveWorkbook.ShowPivotTableFieldList = False
Range("B3").Select
ActiveSheet.PivotTables("PivotTable1").PivotSelect "",
xlDataAndLabel, True
Selection.Copy
Range("D3").Select
ActiveSheet.Paste
Columns("D:D").EntireColumn.AutoFit
Range("D4").Select
ActiveSheet.PivotTables("PivotTable8").PivotFields ("Part
Number").Orientation _
= xlHidden
With ActiveSheet.PivotTables("PivotTable8").PivotFields ("Dept
Held")
.Orientation = xlRowField
.Position = 1
End With
Range("E5").Select
Selection.Sort Key1:="R5C5", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
ActiveWorkbook.ShowPivotTableFieldList = False
Range("E3").Select
ActiveSheet.PivotTables("PivotTable8").PivotSelect "",
xlDataAndLabel, True
Selection.Copy
Range("G3").Select
ActiveSheet.Paste
Columns("G:G").EntireColumn.AutoFit
Application.CutCopyMode = False
ActiveWorkbook.ShowPivotTableFieldList = True
Range("G4").Select
ActiveSheet.PivotTables("PivotTable9").PivotFields ("Dept
Held").Orientation = _
xlHidden
With ActiveSheet.PivotTables("PivotTable9").PivotFields ("Ship")
.Orientation = xlRowField
.Position = 1
End With
Range("H5").Select
Selection.Sort Key1:="R5C8", Order1:=xlDescending,
Type:=xlSortValues, _
OrderCustom:=1, Orientation:=xlTopToBottom
ActiveWorkbook.ShowPivotTableFieldList = False
' ActiveSheet.PivotTables("PivotTable1").PivotFields ("Count of Total
Hours"). _
' Function = xlSum
' Range("D3").Select
' ActiveSheet.PivotTables("PivotTable8").PivotFields ("Count of Total
Hours"). _
' Function = xlSum
' Range("G3").Select
' ActiveSheet.PivotTables("PivotTable9").PivotFields ("Count of Total
Hours"). _
' Function = xlSum
' Range("B5").Select
' Selection.Sort Key1:="R5C2", Order1:=xlDescending,
Type:=xlSortValues, _
' OrderCustom:=1, Orientation:=xlTopToBottom
' Range("E5").Select
' Selection.Sort Key1:="R5C5", Order1:=xlDescending,
Type:=xlSortValues, _
' OrderCustom:=1, Orientation:=xlTopToBottom
' Range("H5").Select
' Selection.Sort Key1:="R5C8", Order1:=xlDescending,
Type:=xlSortValues, _
' OrderCustom:=1, Orientation:=xlTopToBottom
Range("A1").Select
ActiveCell.FormulaR1C1 = "By Part Number"
Range("D1").Select
ActiveCell.FormulaR1C1 = "By Department"
Range("G1").Select
ActiveCell.FormulaR1C1 = "By Ship Serial"
Range("A1:G1").Select
Selection.Font.Bold = True
With Selection.Font
.Name = "Arial"
.Size = 12
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow = False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A2").Select
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "Hours Summary"
Sheets("Hours Summary").Select
Sheets("Hours Summary").Move After:=Sheets(3)
Sheets(Array("Cost Summary", "Hours Summary")).Select
Sheets("Cost Summary").Activate
Range("A:A,D:D,G:G").Select
Range("G1").Activate
With Selection
.HorizontalAlignment = xlLeft
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
.MergeCells = False
End With
Sheets("Cost Summary").Select
Range("A2").Select
Sheets("Hours Summary").Select
Range("A2").Select
Sheets("AAASSY").Select
Sheets("AAASSY").Name = "C-130 Assembly"
Range("A5").Select
Application.DisplayAlerts = False
ActiveWorkbook.SaveAs Filename:= _
"U:\My Documents\TxtFiles\LMAASRR\Aaassy.xls",
FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False
Application.DisplayAlerts = True
ActiveWindow.Close
---
Message posted from
http://www.ExcelForum.com/