Presently I have the following code.

Sub Macro1()
Range("A1").Select
Selection.AutoFilter
Selection.AutoFilter Field:=2, Criteria1:="11"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets.Add
Sheets("Sheet2").Select
Sheets("Sheet2").Name = "11"
Range("A1").Select
ActiveSheet.Paste
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=2, Criteria1:="12"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Sheets("Sheet3").Select
Sheets("Sheet3").Name = "12"
Range("A1").Select
Sheets("Sheet1").Select
Range("A1").Select
Application.CutCopyMode = False
Selection.AutoFilter Field:=2, Criteria1:="01"
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.Copy
Sheets.Add
ActiveSheet.Paste
Sheets("Sheet4").Select
Sheets("Sheet4").Name = "01"
Range("A1").Select
Sheets("11").Select
Range("A1").Select
Application.CutCopyMode = False
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'11'!R1C1:R427C12").CreatePivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable2").AddFields RowFields:="Order
type", _
ColumnFields:="Created on"

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Material").Orientation = _
xlDataField
ActiveWorkbook.ShowPivotTableFieldList = True
Sheets("Sheet5").Select
Sheets("Sheet5").Name = "Pivot Table - 11"
Range("A1").Select
End Sub



My only problem is that this code does not work all the time. I am trying
to create a code that will perform the following functions, all the time.

Copy every row that has an 11 in Column B2:B800.

Open a new worksheet (Sheet 2) and make A1:L1 Sheet 2 the same as A1:L1 in
Sheet 1.

Paste all the rows that had an 11 in Column B into Sheet 2, starting at cell
A2.

Rename Sheet 2 €œ11€�.

Return to Sheet 1.

Copy every row that has a 12 in Column B2:B800.

Open a new worksheet (Sheet 3) and make A1:L1 Sheet 3 the same as A1:L1 in
Sheet 1.

Paste all the rows that had a 12 in Column B into Sheet 2, starting at cell
A2.

Rename Sheet 3 €œ12€�.

Return to Sheet 1.

Copy every row that has a 01 in Column B2:B800.

Open a new worksheet (Sheet 4) and make A1:L1 Sheet 4 the same as A1:L1 in
Sheet 1.

Paste all the rows that had a 01 in Column B into Sheet 2, starting at cell
A2.

Rename Sheet 4 €œ01€�.

Go to Sheet €œ11€� Cell A1.

Data

Pivot Table & Pivot Chart Wizard

Next

Next

Layout

Place Created On into Column field.

Place Order Type into Row Field.

Place Material into Data Field.

Ok

Finish. Put the Pivot Table into a New Worksheet.

Rename New Worksheet Pivot Table €“ 11.


What do you think?
WB