Pivot Table Code

[shawzito]

Hi,

I have some code to create a pivot table in excel based on the format of the data (I work with a lot trial balances and i constantly have to trend the information). My question is what is the way to alter the code below such that I can run this macro on any tab whether or not the tab is named "sheet1" and "sheet2". Right now it's defined to only use those sheets, such that the macro will only run if I run it on "sheet1" and there is also a "sheet2" in the workbook. I basically just want to be able to run it on any sheet, get the same outcome, regardless of the name. Thanks!

Sub SetupforPivot()
'Dim variables here
Dim wSrc As Worksheet
Dim wDest As Worksheet
Dim LC As Long
Dim LR As Long
Dim RW As Long
Dim i As Long
Dim a As Long: a = 2
Dim b As Long: b = 1
Dim j As Long: j = 2

Application.ScreenUpdating = False

Set wSrc = Sheets("Sheet1")
Set wDest = Sheets("Sheet2")

LC = wSrc.Cells(1, Columns.Count).End(xlToLeft).Column / 2
wDest.Range("A1:C1").Value = Array("Description", "Amount", "Date")

For i = 1 To LC

RW = wSrc.Cells(Rows.Count, b).End(xlUp).Row - 1
LR = wDest.Cells(Rows.Count, 1).End(xlUp).Offset(1).Row
wSrc.Cells(2, b).Resize(RW).Copy wDest.Cells(LR, 1)
wSrc.Cells(2, a).Resize(RW).Copy wDest.Cells(LR, 2)
wDest.Cells(j, 3).Resize(RW) = wSrc.Cells(1, a)

j = j + RW
a = a + 2
b = b + 2
Next i

With Sheets("Sheet2")
.UsedRange.Borders.LineStyle = xlContinuous
.UsedRange.Columns.AutoFit
.UsedRange.HorizontalAlignment = xlCenter
End With

Set wSrc = Nothing
Set wDest = Nothing

Call CreatePT

Application.ScreenUpdating = True
End Sub

Sub CreatePT()
'Dim variables here
Dim LastRow As Long 'This variable makes the PT dynamic -- auto adjusts as your data grows
Dim DataSheet As String
Dim PivotSheet As String

'Set last row in column A to get the last cell
LastRow = Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Row

'Activate the Sheet with the data
Application.Goto Sheets("Sheet2").Range("A1")

'Establish the active sheet as the data sheet
DataSheet = ActiveSheet.Name

'Add a new sheet
Sheets.Add

'Qualify new sheet Pivot sheet
PivotSheet = ActiveSheet.Name

'Look at second line...added the LastRow variable and the DataSheet/PivotSheet variable
ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
DataSheet & "!R1C1:R" & LastRow & "C3", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:=PivotSheet & "!R3C1", TableName:="PivotTable1", DefaultVersion:=xlPivotTableVersion10
'Here we identify the sheet for the pivot table
Sheets(PivotSheet).Select
Cells(3, 1).Select
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Description")
.Orientation = xlRowField
.Position = 1
End With
ActiveSheet.PivotTables("PivotTable1").AddDataField ActiveSheet.PivotTables( _
"PivotTable1").PivotFields("Amount"), "Sum of Amount", xlSum
With ActiveSheet.PivotTables("PivotTable1").PivotFields("Date")
.Orientation = xlColumnField
.Position = 1
End With
End Sub