tpkelley_no
Board Regular
- Joined
- Oct 14, 2011
- Messages
- 188
- Office Version
- 2010
- Platform
- Windows
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