PDA

View Full Version : [SOLVED] Pivot Table VBA



ced0802
01-30-2016, 10:25 AM
Hi everyone,

I have this macro which purpose is to create a pivot table. It is working but only when I run it from VBA window and with the file "Stock" opened.
I would like ti to work when I press the macro bouton from my main file.


Code :



Sub CreatePivotTable2()


Dim sht As Worksheet
Dim pvtCache As PivotCache
Dim pvt As PivotTable
Dim StartPvt As String
Dim SrcData As String

'Determine the data range you want to pivot
SrcData = ActiveSheet.Name & "!" & Range("A1:N5000").Address(ReferenceStyle:=xlR1C1)

'Create a new worksheet
Set sht = Workbooks("Stock").Worksheets.Add

'Where do you want Pivot Table to start?
StartPvt = sht.Range("A3").Address(ReferenceStyle:=xlR1C1, External:=True)

'Create Pivot Cache from Source Data
Set pvtCache = Workbooks("Stock").PivotCaches.Create( _
SourceType:=xlDatabase, _
SourceData:=SrcData)

'Create Pivot table from Pivot Cache
Set pvt = pvtCache.CreatePivotTable( _
TableDestination:=StartPvt, _
TableName:="PivotTable1")

End Sub



In Bold, the part where I have the error "1004".

Thank you for your help.

ced0802
01-30-2016, 11:57 AM
Found out why. My main workbook's sheet has to have the same name as the sheet of the "Stock" File.

SrcData = ActiveSheet.Name