vba pivot help please

S

semicar

New Member
Joined
Feb 5, 2012
Messages
14
Dear all,

attached is my table (sheet 3), number of table on this sheet will differ on a daily basis; what I am trying to do is do a automatic Pivot table (sheet 1) based on the sheet 3 values , expand the values of the pivot then automaticall rename each sheets based on the value on coloumn f2 (sheet 2).

eg if there are 10 names on the pivot,then we should have 10 differnent sheets with the sheet being named as per the value on col f2 following the expansion of pivot data values.

sorry I dont know how to attach the file, i would be grateful for anyone for your help.

[TABLE="width: 1197"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]Table (sheet3)

[TABLE="width: 1269"]
<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]account no[/TD]
[TD]Episode No[/TD]
[TD]invoice date[/TD]
[TD]Paid date[/TD]
[TD]area[/TD]
[TD]order signned off by[/TD]
[TD]order type[/TD]
[TD]cust Surname[/TD]
[TD]cust Forename[/TD]
[TD]locationcode[/TD]
[TD]Summary Status[/TD]
[TD]remarks[/TD]
[/TR]
[TR]
[TD]111111[/TD]
[TD]4586902[/TD]
[TD]02 Jan 2013 11:17[/TD]
[TD]02 Jan 2013 13:57[/TD]
[TD]sydney[/TD]
[TD]dr AN Other[/TD]
[TD]single trans[/TD]
[TD]xxxxxx[/TD]
[TD]yyyyyy[/TD]
[TD]12321[/TD]
[TD]Not Started[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]222222[/TD]
[TD]4587894[/TD]
[TD]01 Jan 2013 21:01[/TD]
[TD]01 Jan 2013 23:17[/TD]
[TD]melbourne[/TD]
[TD]Dr B Burvas[/TD]
[TD]multiple[/TD]
[TD]dddddd[/TD]
[TD]qqqqqqq[/TD]
[TD]1232132[/TD]
[TD]Not Started[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]333333[/TD]
[TD]4585651[/TD]
[TD]30 Dec 2012 17:00[/TD]
[TD]31 Dec 2012 17:02[/TD]
[TD]newyourk[/TD]
[TD]dr c charile[/TD]
[TD]multiple[/TD]
[TD]abcd[/TD]
[TD]efgh[/TD]
[TD]1221[/TD]
[TD]Not Started[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]444444[/TD]
[TD]4536333[/TD]
[TD]28 Dec 2012 08:34[/TD]
[TD]29 Dec 2012 07:00[/TD]
[TD]petersburg[/TD]
[TD]dr F haugton[/TD]
[TD]multiple[/TD]
[TD]aaaaaa[/TD]
[TD]tttt[/TD]
[TD]4343[/TD]
[TD]Not Started[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]555555[/TD]
[TD]4583029[/TD]
[TD]28 Dec 2012 01:00[/TD]
[TD]28 Dec 2012 21:00[/TD]
[TD]london[/TD]
[TD]Dr T Tango[/TD]
[TD]multiple[/TD]
[TD]bbbbbb[/TD]
[TD]rrrr[/TD]
[TD]4343w[/TD]
[TD]Not Started[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]666666[/TD]
[TD]4580324[/TD]
[TD]28 Dec 2012 08:34[/TD]
[TD]28 Dec 2012 16:53[/TD]
[TD]birmingham[/TD]
[TD]Dr Jonathan Mantil[/TD]
[TD]single trans[/TD]
[TD]ccccc[/TD]
[TD]nnnn[/TD]
[TD]sgdsgdg[/TD]
[TD]Not Started[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Pivot

[TABLE="width: 435"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Count of order signned off by[/TD]
[TD]Summary Status[/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]order signned off by[/TD]
[TD]Not Started[/TD]
[TD]Grand Total[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dr AN Other[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dr B Burvas[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dr c charile[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]dr F haugton[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dr Jonathan Mantil[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Dr T Tango[/TD]
[TD="align: right"]1[/TD]
[TD="align: right"]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Grand Total[/TD]
[TD="align: right"]6[/TD]
[TD="align: right"]6[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]sheet 4

[TABLE="width: 1042"]
<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>[TR]
[TD]account no[/TD]
[TD]Episode No[/TD]
[TD]invoice date[/TD]
[TD]Paid date[/TD]
[TD]area[/TD]
[TD]order signned off by[/TD]
[TD]order type[/TD]
[TD]Surname[/TD]
[TD]Forename[/TD]
[TD]locationcode[/TD]
[TD]Summary Status[/TD]
[TD]remarks[/TD]
[/TR]
[TR]
[TD="align: right"]222222[/TD]
[TD]4587894[/TD]
[TD="align: right"]01/01/2013 21:01[/TD]
[TD="align: right"]01/01/2013 23:17[/TD]
[TD]melbourne[/TD]
[TD]Dr B Burvas[/TD]
[TD]multiple[/TD]
[TD]dddddd[/TD]
[TD]qqqqqqq[/TD]
[TD="align: right"]1232132[/TD]
[TD]Not Started[/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


code:

Sub formatting()
'
' formatting Macro
'

'
Columns("E:E").EntireColumn.AutoFit
Columns("E:E").ColumnWidth = 12.86
Columns("F:F").Select
Range("F172").Activate
Selection.Delete Shift:=xlToLeft
Columns("E:E").EntireColumn.AutoFit
ActiveWindow.SmallScroll Down:=-21
ActiveWindow.ScrollRow = 137
ActiveWindow.ScrollRow = 135
ActiveWindow.ScrollRow = 134
ActiveWindow.ScrollRow = 132
ActiveWindow.ScrollRow = 129
ActiveWindow.ScrollRow = 126
ActiveWindow.ScrollRow = 123
ActiveWindow.ScrollRow = 120
ActiveWindow.ScrollRow = 116
ActiveWindow.ScrollRow = 108
ActiveWindow.ScrollRow = 102
ActiveWindow.ScrollRow = 95
ActiveWindow.ScrollRow = 89
ActiveWindow.ScrollRow = 82
ActiveWindow.ScrollRow = 76
ActiveWindow.ScrollRow = 69
ActiveWindow.ScrollRow = 62
ActiveWindow.ScrollRow = 56
ActiveWindow.ScrollRow = 51
ActiveWindow.ScrollRow = 44
ActiveWindow.ScrollRow = 40
ActiveWindow.ScrollRow = 37
ActiveWindow.ScrollRow = 35
ActiveWindow.ScrollRow = 32
ActiveWindow.ScrollRow = 30
ActiveWindow.ScrollRow = 28
ActiveWindow.ScrollRow = 26
ActiveWindow.ScrollRow = 24
ActiveWindow.ScrollRow = 22
ActiveWindow.ScrollRow = 21
ActiveWindow.ScrollRow = 19
ActiveWindow.ScrollRow = 18
ActiveWindow.ScrollRow = 17
ActiveWindow.ScrollRow = 15
ActiveWindow.ScrollRow = 13
ActiveWindow.ScrollRow = 12
ActiveWindow.ScrollRow = 10
ActiveWindow.ScrollRow = 9
ActiveWindow.ScrollRow = 8
ActiveWindow.ScrollRow = 7
ActiveWindow.ScrollRow = 6
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
ActiveWindow.ScrollColumn = 1
Range("B5").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet3!R5C2:R170C13", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet6!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10
Sheets("Sheet6").Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("[TABLE="width: 136"]
<colgroup><col width="136"></colgroup><tbody>[TR]
[TD="class: xl65, width: 136"]order signned off by[/TD]
[/TR]
</tbody>[/TABLE]
" _
)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("[TABLE="width: 136"]
<tbody>[TR]
[TD="class: xl65, width: 136"]order signned off by
[/TD]
[/TR]
</tbody>[/TABLE]
"), _
"Count of [TABLE="width: 136"]
<colgroup><col width="136"></colgroup><tbody>[TR]
[TD="class: xl65, width: 136"]order signned off by[/TD]
[/TR]
</tbody>[/TABLE]
", xlCount
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Summary Status")
.Orientation = xlColumnField
.Position = 1
End With
Range("D5").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E6").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E7").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E8").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E9").Select
Selection.ShowDetail = True
Range("E46").Select
Sheets("Sheet1").Select
Range("E10").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E11").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E12").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E13").Select
Selection.ShowDetail = True
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet1").Select
Range("E14").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E15").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E16").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E17").Select
Selection.ShowDetail = True
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet1").Select
Range("E18").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E19").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E20").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E21").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E22").Select
Selection.ShowDetail = True
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet1").Select
Range("E23").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E24").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E25").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E26").Select
Selection.ShowDetail = True
Sheets("Sheet1").Select
Range("E27").Select
Selection.ShowDetail = True
ActiveWindow.ScrollWorkbookTabs Position:=xlLast
Sheets("Sheet1").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
ActiveWindow.ScrollWorkbookTabs Sheets:=-11
Sheets("Sheet7").Select
ActiveWindow.SmallScroll Down:=-15
Range("K2").Select
Selection.FormatConditions.Add Type:=xlCellValue, Operator:=xlEqual, _
Formula1:="=""not started"""
Selection.FormatConditions(Selection.FormatConditions.Count).SetFirstPriority
With Selection.FormatConditions(1).Font
.Color = -11489280
.TintAndShade = 0
End With
With Selection.FormatConditions(1).Interior
.Pattern = xlSolid
.PatternColorIndex = xlAutomatic
.Color = 255
.TintAndShade = 0
End With
Selection.FormatConditions(1).StopIfTrue = True
Selection.Copy
Range("K3:K12").Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
Range("K2").Select
Selection.Copy
Sheets("Sheet8").Select
ActiveWindow.SmallScroll Down:=-18
Range("K2:K3").Select
ActiveSheet.Paste
Sheets("Sheet9").Select
Range("K2:K3").Select
ActiveSheet.Paste
Sheets("Sheet10").Select
Range("K2").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Selection.AutoFill Destination:=Range("K2:K5"), Type:=xlFillDefault
Range("K2:K5").Select
Range("K3").Select
Sheets("Sheet11").Select
Range("K2:K8").Select
Sheets("Sheet10").Select
Range("K2").Select
Selection.Copy
Sheets("Sheet11").Select
Range("K2:K9").Select
Range("K11").Select
Sheets("Sheet10").Select
Application.CutCopyMode = False
Call SheetName

End Sub

Sub SheetName()

Dim shName As String, myName As String, sh
For Each sh In Worksheets
If Left(sh.Name, 5) = "Sheet" Then
sh.Name = sh.Range("A2")
End If

End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
You must log in or register to reply here.

Similar threads

P
  • Question Question
Help with Vba Code
Replies
3
Views
433
Coyotex3
C
I
  • Solved
Simplify my working code please
Replies
7
Views
127
ipbr21054
I
C
  • Solved
Need Help VBA
2
Replies
10
Views
410
Trebor76
T
G
  • Solved
Help with Excel vba loop with some different ways to end it
Replies
3
Views
258
Cubist
Cubist
C
  • Question Question
VBA code stuck (extract data from worksheet)
Replies
1
Views
393
Alex Blakenburg
A

Forum statistics

Threads
1,223,237
Messages
6,170,928
Members
452,366
Latest member
TePunaBloke

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top