Anyone have a Macro that Automates Drag fields in a Pivot table

Hi All,

Would anyone be able to help a poor sap such as myself?
I have data on Sheet1 and pivot table on Sheet2.
I would like a Macro to automatically drag the fields (in this case the Columns) from Sheet1 into the fields/box.
Basically Cell A1 from Sheet1 to the Row labels field box, then and Cells B1, C1, D1... etc in the Values field box.

BUT, the columns in Sheet1 will constantly increase and change.

is this possible?

Regards,

Found this on the internet!

Sub test()


Dim PCache As PivotCache
Dim PT As PivotTable
Dim wksSource As Worksheet
Dim wksDest As Worksheet
Dim i As Long

Set wksSource = Worksheets("Sheet1") 'change the source worksheet name accordingly
Set wksDest = Worksheets("Sheet2") 'change the destination worksheet name accordingly

Set PCache = ActiveWorkbook.PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=wksSource.Range("A1").CurrentRegion)

Set PT = wksDest.PivotTables.Add( _
PivotCache:=PCache, _
TableDestination:=wksDest.Range("A2"), _
TableName:="MyPivotTable")

With PT
For i = 1 To .PivotFields.Count
If i > 1 Then
.PivotFields(i).Orientation = xlDataField
Else
.PivotFields(i).Orientation = xlRowField
End If
Next i
.DisplayFieldCaptions = False 'optional
.TableStyle2 = "PivotStyleMedium2" 'optional
End With

wksDest.Select

End Sub