Hi ,
I am having a mcaro creating the Pivot table from data coming dynamically.
Its working with static data but having problem with dynamic data.
i.e I have a excel template with two macros one for chart another for pivot table and using JXLS i am generating a report in excel format using this template as input and filling data. When i opened this excel sheet the chart aoutmatically created but in creating Pivot table it gives the above error of no open the source file name.
Code of macro is :
Sub create_pivot()
'Delete_All_Pivot_Tables_In_Sheet
'Create new sheet for creating Pivot tables
Worksheets.Add
ActiveSheet.Name = "PivotTable"
'Find the Range of data(Whole) from Excel sheet
Set m1 = Worksheets("QSRS_data_sheet").Cells(1, 1)
Set m2 = Worksheets("QSRS_data_sheet").Range("A1").End(xlToRight)
Set m3 = m2.End(xlDown)
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, _
SourceData:=Sheets("QSRS_data_sheet").Range(m1.Address() & ":" & m3.Address())).CreatePivotTable _
TableDestination:=Sheets("PivotTable").Range("A1"), _
TableName:="PivotTable1", _
DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTables(1).AddFields RowFields:=Worksheets("QSRS_data_sheet").Cells(1, 2).Value
ActiveSheet.PivotTables(1).PivotFields(Worksheets("QSRS_data_sheet").Cells(1, 2).Value).Orientation = xlDataField
ActiveSheet.PivotTables(1).AddFields RowFields:=Worksheets("QSRS_data_sheet").Cells(1, 1).Value
End Sub
Can anyone help me.
Thanks in advance.
Amit Nain.
Bookmarks