pivot table for active work sheet

tpkelley_no

Board Regular
Joined
Oct 14, 2011
Messages
169
How do I allow the VBA code to read any active work sheet? not just a sheet called "SourceData:="report1597179135841!R1C1:R138C18"" and any amount of rows and columns.


VBA Code:
Sub Sales_tax()
Columns("D:F").Insert
Columns("C:C").TextToColumns Destination:=Range("C1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar:="<", FieldInfo:=Array(Array(1, 1), Array(2, 1)), TrailingMinusNumbers:=True
Columns("D:D").Replace What:="br>", Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2
Columns("D:D").TextToColumns Destination:=Range("D1"), DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=False, Comma:=True, Space:=False, Other:=False, OtherChar:="<", FieldInfo:=Array(Array(1, 1), Array(2, 1), Array(3, 1)), TrailingMinusNumbers:=True
Range("D1:F1").Value = Array("Client city", "Client  State", "Client Zip Code")
Set NewSht = Sheets.Add
Set PT = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:="report1597179135841!R1C1:R138C18").CreatePivotTable(TableDestination:=NewSht.Range("A3"))
With PT
  .RepeatAllLabels xlRepeatLabels
  With .PivotFields("Client city")
    .Orientation = xlRowField
    .Position = 1
  End With
  .AddDataField .PivotFields("Net Fee Earned"), "Sum of Net Fee Earned", xlSum
  .PivotFields("Sum of Net Fee Earned").NumberFormat = "#,##0.00"
End With
End Sub
 

Some videos you may like

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.

Watch MrExcel Video

Forum statistics

Threads
1,114,492
Messages
5,548,362
Members
410,828
Latest member
A9Bosv3
Top