Hey all, I am trying to create a macro that will take a jumbled mess of data and turn it into a nice pivot table.

I usually have to do a text to columns function that when I created a macro for looks like this....

Columns("A:A").Select
Cells.EntireColumn.AutoFit
Selection.TextToColumns Destination:=ActiveCell, DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
Semicolon:=False, Comma:=False, Space:=False, Other:=True, OtherChar _
:="^", FieldInfo:=Array(1, 1), TrailingMinusNumbers:=True


Then I am looking to take that data and put it into a pivot table. However when I create the macro for this it only works with the original file that I created the macro in.

See the code below.

' Macro2 Macro
'

'
Range("A1").Select
Sheets.Add
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"Sheet1!R1C1:R842C52", Version:=xlPivotTableVersion15).CreatePivotTable _
TableDestination:="Sheet5!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion15
Sheets("Sheet5").Select
Cells(3, 1).Select
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Operation Number"), "Sum of Operation Number", _
xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Sum of Operation Number")
.Caption = "Count of Operation Number"
.Function = xlCount
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Plan/Actual Ingredient Item")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"Required Completion Date")
.Orientation = xlColumnField
.Position = 1
End With
End Sub


How do I get this to work with any active sheet?

Thanks for your help in advance!