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.