Hi Guys

I want to create a pivot table where the source data is one sheet and the destination is a sheet in another workbook.

But the complexity is that the new workbook is created by user input, so it could be different everytime. How do i manage to create the pivot everytime in that new user created file.

I have tried to do something but it is not working...

Source data workbook is Scount and sheet is Sheet1

So i need to use the data from the above workbook but create the actual pivot table in a new one.

Is it possible?


Sub Pivot_macro()

Application.ScreenUpdating = False

Dim wBook As New Workbook
Dim mydate As String
Dim myPivot As PivotTable
Dim LastRow&, LastCol&

mydate = InputBox("Enter Today's date in the format dd.mm.yy")
currentsheet = InputBox("Please Enter Outlet's Name")
Set wBook = Application.Workbooks.Add
wBook.SaveAs "T:\stocktake\" & currentsheet & "stocktake" & mydate & ".xls"

Windows("scount.xls").Activate

Sheet1.Activate
LastRow = Sheet1.Rows.Count
LastCol = Sheet1.Columns.Count
myPivot.SourceData = "'" & Sheet1 & "'!R1C1:R" & LastRow & "C" & LastCol



Application.ScreenUpdating = True

End Sub