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 (sheet3)

account noEpisode Noinvoice datePaid dateareaorder signned off byorder typecust Surnamecust ForenamelocationcodeSummary Statusremarks
111111458690202 Jan 2013 11:1702 Jan 2013 13:57sydneydr AN Othersingle transxxxxxxyyyyyy12321Not Started
222222458789401 Jan 2013 21:0101 Jan 2013 23:17melbourneDr B Burvasmultipleddddddqqqqqqq1232132Not Started
333333458565130 Dec 2012 17:0031 Dec 2012 17:02newyourkdr c charilemultipleabcdefgh1221Not Started
444444453633328 Dec 2012 08:3429 Dec 2012 07:00petersburgdr F haugtonmultipleaaaaaatttt4343Not Started
555555458302928 Dec 2012 01:0028 Dec 2012 21:00londonDr T Tangomultiplebbbbbbrrrr4343wNot Started
666666458032428 Dec 2012 08:3428 Dec 2012 16:53birminghamDr Jonathan Mantilsingle transcccccnnnnsgdsgdgNot Started

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>
Pivot

Count of order signned off bySummary Status
order signned off byNot StartedGrand Total
dr AN Other11
Dr B Burvas11
dr c charile11
dr F haugton11
Dr Jonathan Mantil11
Dr T Tango11
Grand Total66

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
sheet 4

account noEpisode Noinvoice datePaid dateareaorder signned off byorder typeSurnameForenamelocationcodeSummary Statusremarks
222222458789401/01/2013 21:0101/01/2013 23:17melbourneDr B Burvasmultipleddddddqqqqqqq1232132Not Started

<colgroup><col><col><col span="2"><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>

<colgroup><col><col><col><col><col><col><col><col><col><col><col></colgroup><tbody>
</tbody>


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("
order signned off by

<colgroup><col width="136"></colgroup><tbody>
</tbody>
" _
)
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("
order signned off by

<tbody>
</tbody>
"), _
"Count of
order signned off by

<colgroup><col width="136"></colgroup><tbody>
</tbody>
", 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

Show numbers in thousands?
Use a custom number format of #,##0,K. Each comma after the final 0 will divide the displayed number by another thousand
You must log in or register to reply here.

Similar threads

P
  • Question
Help with Vba Code
Replies
3
Views
213
Coyotex3
C
Z
  • Question
VBA column print hyperlinks
2
Replies
10
Views
319
Micron
M
L
  • Solved
Would like to make edits to VBA Calendar Code
Replies
8
Views
467
Legacy 352679
L
C
  • Question
Please help clean up my convoluted VBA code. It seems very redundant and repetitive.
Replies
1
Views
111
myall_blues
myall_blues
E
  • Question
Help Cleaning Up / Optimizing VBA Code
Replies
6
Views
325
offthelip
O

Forum statistics

Threads
1,216,081
Messages
6,128,695
Members
449,464
Latest member
againofsoul

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