Excel Sleeping On PivotTableUpdate Copy Paste Formats Only

[rickincanada]

I am running the following code on PivotTableUpdate, however it is making Excel go into some strange sleep mode for about 5-6 seconds every time it runs. What I mean by this is that Excel is unresponsive however there is no hourglass.

Sub FormattingCopy(xSheet As Worksheet)
Application.ScreenUpdating = False
Dim xCell As Range
Const MaxRows = 100
For Each xCell In xSheet.Range("C5:C" & MaxRows)


xSheet.Cells(xCell.Row, xCell.Column - 1).Copy
xSheet.Cells(xCell.Row, xCell.Column).Select
Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False

Next xCell
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

Any help that you can provide would be GREATLY appreciated!

Thanks,
Rick

 

[Jerry Sullivan]

Hi Rick, Stepping through and selecting each cell is less efficient than referencing the entire range without Selecting it.

Try this code...

Sub FormattingCopy(xSheet As Worksheet)
    Application.ScreenUpdating = False
    Dim xCell As Range
    
    Const MaxRows = 100
    With xSheet
        .Range("B5:B" & MaxRows).Copy
        .Range("C5").PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    End With
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Are you trying to format the data range of the PivotTable?
If so there are other alternatives to this approach.