PDA

View Full Version : [SOLVED] Out of memory for below code



r_know
11-02-2014, 06:57 AM
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
11-02-2014, 07:19 AM
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
11-02-2014, 08:00 AM
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
11-03-2014, 07:37 AM
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
11-03-2014, 10:05 AM
Ohh thanks for this also

SamT
11-04-2014, 07:28 AM
Hey, Fred.

They finally give you a moments break from work.

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

fredlo2010
11-04-2014, 07:32 AM
lol I know right :)

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