Introduction:
Most of the time in Analysis Office we use single query and multiple queries in multiple sheet. When two queries are used in same sheet of Analysis Office, one after another there may be a risk of overlapping of the crosstab if the data increases and we get a warning message in Analysis Office. Causing below error:
Overlap warning message
To overcome it we can write VB macro so the data can be presented without any issue.
The Crosstab1 can start at A5 cell whereas the Crosstab2 can start at A10000 as per the code the blank rows between this 2 Crosstab will be in hide and will get increased as the data increases in Crosstab1.
VB Code:
This Workbook:
Public Sub Workbook_SAP_Initialize()
' register callbacks
Call Application.Run("SAPExectueCommand", "RegisterCallback", "AfterRedisplay", "CallbackAfterRedisplay")
End Sub
Create a Module and write below code:
Public Sub Callback_AfterRedisplay()
Dim intGap As Integer
Dim intGap2 As Integer
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Set ws1 = ThisWorkbook.Worksheets("Sheet1")
Set ws2 = ThisWorkbook.Worksheets("Sheet2")
If ws1 Is ActiveSheet Then
intGap = ThisWorkbook.Worksheets("Sheet1")
Worksheets(“Sheet1").Application.ScreenUpdating = False
Rows("1:9995").Select
Selection.EntireRow.Hidden = False
Rows(intGap & ":9999").Select
Selection.EntireRow.Hidden = True
Worksheets(“Sheet1").Activate
Worksheets("Sheet1").Application.ScreenUpdating = True
Else
intGap2 = ThisWorkbook.Worksheets("Sheet2")
Worksheets("Sheet2").Application.ScreenUpdating = False
Rows("1:9995").Select
Selection.EntireRow.Hidden = False
Rows(intGap2 & ":9999").Select
Selection.EntireRow.Hidden = True
Worksheets("Sheet2").Activate
Worksheets("Sheet2").Application.ScreenUpdating = True
End If
End Sub
Conclusion:
To overcome the overlap of 2 different queries in the same sheet we can use VB macro code, this code is not only for one sheet if the user is going on for 2 or 3 sheet, they can use ELSE IF loop to proceed the code and attain the result.