Workbook_SheetActivate Paste Problem

[Wayne Cressman]

I have a workbook with 16 pivot tables on 16 worksheets. My problem is
that I cannot copy and paste data between worksheets while I have the
following Workbook_SheetActivate event active. No error message is
generated, just the paste function is disabled. Removing the event
solves the problem, but I need to figure out what in the event is
causing the problem.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
If CheckPivotTableExists(ThisWorkbook.ActiveSheet) = True Then
Dim strReportType As String, strReportPeriod As String,
arrShName As Variant
Application.ScreenUpdating = False
arrShName = Split(Sh.Name, "-")
strReportType = Trim(arrShName(0))
If UBound(arrShName) = 1 Then
strReportPeriod = Trim(arrShName(1))
End If
SetActiveButton arrReportType, strReportType
SetActiveButton arrReportPeriod, strReportPeriod
blnDisplayFullScreen = GetPivotSetting("DisplayFullScreen")
If blnDisplayFullScreen = True Then
ActiveWindow.DisplayHeadings = False
ElseIf blnDisplayFullScreen = False Then
ActiveWindow.DisplayHeadings = True
End If

'set up wrap text on legends on 2nd line.
Dim pt As PivotTable, objTopLeft As Range, objBottomRight As Range
Set pt = Sh.PivotTables.Item(1)
Set objTopLeft = pt.ColumnRange.Cells(1, 1).Offset(1, 0)
Set objBottomRight = pt.ColumnRange.Cells(1, 1).Offset(1,
0).End(xlToRight)
Range(objTopLeft, objBottomRight).WrapText = True


Sh.Cells(1.1).Select
Application.ScreenUpdating = True
End If
End Sub

Thanks,
Wayne C.

[Dave Peterson]

Lots of things in code can cause the clipboard to be killed.

Maybe you can sprinkle lines like:

debug.print "Step 1: " & application.cutcopymode

through out the code. Then when you narrow down the portion, put one of those
debug.print lines after each line of your code.

Wayne Cressman wrote:
>
> I have a workbook with 16 pivot tables on 16 worksheets. My problem is
> that I cannot copy and paste data between worksheets while I have the
> following Workbook_SheetActivate event active. No error message is
> generated, just the paste function is disabled. Removing the event
> solves the problem, but I need to figure out what in the event is
> causing the problem.
>
> Private Sub Workbook_SheetActivate(ByVal Sh As Object)
> If CheckPivotTableExists(ThisWorkbook.ActiveSheet) = True Then
> Dim strReportType As String, strReportPeriod As String,
> arrShName As Variant
> Application.ScreenUpdating = False
> arrShName = Split(Sh.Name, "-")
> strReportType = Trim(arrShName(0))
> If UBound(arrShName) = 1 Then
> strReportPeriod = Trim(arrShName(1))
> End If
> SetActiveButton arrReportType, strReportType
> SetActiveButton arrReportPeriod, strReportPeriod
> blnDisplayFullScreen = GetPivotSetting("DisplayFullScreen")
> If blnDisplayFullScreen = True Then
> ActiveWindow.DisplayHeadings = False
> ElseIf blnDisplayFullScreen = False Then
> ActiveWindow.DisplayHeadings = True
> End If
>
> 'set up wrap text on legends on 2nd line.
> Dim pt As PivotTable, objTopLeft As Range, objBottomRight As Range
> Set pt = Sh.PivotTables.Item(1)
> Set objTopLeft = pt.ColumnRange.Cells(1, 1).Offset(1, 0)
> Set objBottomRight = pt.ColumnRange.Cells(1, 1).Offset(1,
> 0).End(xlToRight)
> Range(objTopLeft, objBottomRight).WrapText = True
>
> Sh.Cells(1.1).Select
> Application.ScreenUpdating = True
> End If
> End Sub
>
> Thanks,
> Wayne C.

--

Dave Peterson