I was created one BI workbook with dashboard. actually we cannot create dashboard directly from dynamic BI report. So I took copy of the dynamic report using VBA code to count row numbers and column numbers and pasted to another sheet after clear that destination sheet and change into table using same VBA code. then i created multiple pivot tables and pivot charts using that copied table . I wrote a code inside to refresh the pivot table whenever made any changes to dynamic report. but unfortunately when i save this workbook in SAP BI then pivot table has been disappeared. pivot refresh code working fine when i use the button click event and can easily save the workbook.
anyone has idea about this and how to rectify this?
VBA code mentioned below
Public Sub Callback_AfterRedisplay()
Dim click As Long
Dim wsCopy As Worksheet
Dim wsDest As Worksheet
Dim lCopyLastRow As Long
Dim lDestLastRow As Long
If (Application.Run("SAPExecuteCommand", "Refresh") = 0) Then
Set wsCopy = ThisWorkbook.Worksheets("Master_Table")
Set wsDest = ThisWorkbook.Worksheets("Sheet1")
'1. Find last used row in the copy range based on data in column C
lCopyLastRow = wsCopy.Cells(wsCopy.Rows.Count, "C").End(xlUp).Row
'2. Find first blank row in the destination range based on data in column C
'Offset property moves down 1 row
lDestLastRow = wsDest.Cells(wsDest.Rows.Count, "C").End(xlUp).Offset(1).Row
'3. Clear contents of existing data range
wsDest.Range("C3:S" & lDestLastRow).ClearContents
wsDest.Range("C3:S" & lDestLastRow).ClearFormats
'4. Copy & Paste Data
wsCopy.Range("C3:S" & lCopyLastRow).Copy _
wsDest.Range("C3")
Dim tbOb As ListObject
Dim TblRng As Range
With Sheets("sheet1")
lLastRow = .Cells(.Rows.Count, "C").End(xlUp).Row
lLastColumn = .Cells(3, .Columns.Count).End(xlToLeft).Column
Set TblRng = .Range("C3", .Cells(lLastRow, lLastColumn))
Set tbOb = .ListObjects.Add(xlSrcRange, TblRng, , xlYes)
tbOb.Name = "DynamicTable1"
tbOb.TableStyle = "TableStyleMedium6"
End With
Dim PT As PivotTable
For Each PT In ThisWorkbook.Sheets("sheet2").PivotTables
PT.RefreshTable
Next PT
End If
End Sub
