To overcome two Crosstab Overlap in Analysis Office.

[Sruti_P]

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.