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
Bookmarks