Out of memory for below code

[r_know]

Hi,

I am getting error "Out of memory" for below code, can anyone suggest , how I can avoid such error?

Thanks, RL



Private Sub Worksheet_Change(ByVal Target As Range)


If Target.Address = "$L$5" Then
Range("O6").Select
Selection.Copy
Range("K6:N17").Select
ActiveSheet.Paste
Application.CutCopyMode = False

Worksheets("Sheet5").Range("K5").Value = Worksheets("Sheet5").Range("L5").Value

End If

End Sub

[SamT]

It might be the contents of O6 that you are pasting into 48 individual cells.

The first thing to try is turning screenupdating and calculations off

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.ScreenUpdating = False
'.Calculation = xlCalculationManual
End With

If Target.Address = "$L$5" Then
Range("O6").Copy
Range("K6:N17").PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Worksheets("Sheet5").Range("K5").Value = Worksheets("Sheet5").Range("L5").Value
End If

With Application
'.Calculate
'.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
End With
End Sub

[r_know]

Hi Sam,

I know you, we worked together on some projects. Hope you had good Diwali!

Thanks for your help & I hope it worked....

[fredlo2010]

I would also disable the events in case they are cascading. I added it to Sam's code.



Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
With Application
.ScreenUpdating = False
.EnableEvents = False
.Calculation = xlCalculationManual
End With

If Target.Address = "$L$5" Then
Range("O6").Copy
Range("K6:N17").PasteSpecial (xlPasteAll)
Application.CutCopyMode = False
Worksheets("Sheet5").Range("K5").Value = Worksheets("Sheet5").Range("L5").Value
End If

With Application
.Calculate
.Calculation = xlCalculationAutomatic
.EnableEvents = True
.ScreenUpdating = True
End With
End Sub


Thanks

[r_know]

Ohh thanks for this also

[SamT]

Hey, Fred.

They finally give you a moments break from work.

Thanks for catching my brain-fart on the EnableEvents code.

[fredlo2010]

lol I know right :)

I just notice because recently one of my macros got stuck in an infinite loop because of this... :)