Macro1

[Workbook]

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:=xlDatabase, 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

 

[Don Guillett]

Can be done with a loop but why not just filter the original and work with
the filtered data.

 

[Workbook]

Hi Don,

Because something happens with the filtered data. It doesn't always come
out right in the pivot table when I use the code.