Why is my code so inefficient?

[Lucy89]

The following code takes an age! It does work but takes a very long time.

Any help please

Sub Copy_Paste_Filtered_Selections()'
' Macro3 Macro
'


'
    Sheets("Selections").Select
    Range("A26:AO124").Copy Sheets("Filtered Selections").Range("A10000").End(xlUp).Offset(2, 0)
        
End Sub

 

[mrhstn]

Hi

Is there any other code, particularly event code, in your workbook?

 

[Lucy89]

Hi

Is there any other code, particularly event code, in your workbook?

The code is part of a module that is triggered by a command button.

The module:

Sub Filter_Selections()'
' Macro1 Macro
'


'
    Sheets("Selections").Select
    Range("O24:AJ124").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Range("O1:AJ24"), Unique:=False
   
   
End Sub




Sub Copy_Paste_Filtered_Selections()
'
' Macro3 Macro
'


'
    Sheets("Selections").Select
    Range("A26:AO124").Copy Sheets("Filtered Selections").Range("A10000").End(xlUp).Offset(2, 0)
        
End Sub




Sub Clear_Selections_Filter()
'
' Macro1 Macro
'


'
    Sheets("Selections").Select
    ActiveSheet.ShowAllData
    Sheets("Filtered Selections").Select
    Range("DH1").Select
End Sub

 

[mrhstn]

I see

The reason I ask is your code shouldn't take an age to run, simple copy paste job shouldn't cause a delay, so I'm thinking some kind of event code in one of your sheet modules or the ThisWorkbook module.

Try turning off event code while it does it's thing

Application.EnableEvents = False

' your exisiting code

Application.EnableEvents = True

And see if that helps at all

 

[Lucy89]

I see

The reason I ask is your code shouldn't take an age to run, simple copy paste job shouldn't cause a delay, so I'm thinking some kind of event code in one of your sheet modules or the ThisWorkbook module.

Try turning off event code while it does it's thing

Application.EnableEvents = False

' your exisiting code

Application.EnableEvents = True

And see if that helps at all

Just tried it and exactly the same.

You did mean like this:

Sub Copy_Paste_Filtered_Selections()
'
' Macro3 Macro
'
Application.EnableEvents = False
'
    Sheets("Selections").Select
    Range("A26:AO124").Copy Sheets("Filtered Selections").Range("A10000").End(xlUp).Offset(2, 0)
  Application.EnableEvents = True
End Sub

 

[Fluff]

If you have loads of formulas try turning calculation off

Application.Calculation = xlCalculationManual

    Sheets("Selections").Select
    Range("A26:AO124").Copy Sheets("Filtered Selections").Range("A10000").End(xlUp).Offset(2, 0)

Application.Calculation = xlCalculationAutomatic

 

[Lucy89]

If you have loads of formulas try turning calculation off

Application.Calculation = xlCalculationManual

    Sheets("Selections").Select
    Range("A26:AO124").Copy Sheets("Filtered Selections").Range("A10000").End(xlUp).Offset(2, 0)

Application.Calculation = xlCalculationAutomatic

Thank you.

Still freezes though saying (Excel Not Responding)

 

[mrhstn]

Are you copying over just values, an option might be to just let the range equal the values rather than copy it over...

Sub Copy_Paste_Filtered_Selections() '
With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .Calculation = xlCalculationManual
    Dim lst As Long
    With Sheets("Filtered Selections")
    lst = .Range("A" & Rows.Count).End(xlUp).Row + 2
        .Range("A" & lst).Resize(99, 41).Value = Sheets("Selections").Range("A26:AO124").Value
    End With
    .ScreenUpdating = True
    .EnableEvents = True
    .Calculation = xlCalculationAutomatic
End With
End Sub

Also slimmed down Excel activities whilst that goes ahead.

 

[MARK858]

I don't think it will help as I believe there is something else happening with your workbook that you don't know/haven't revealed yet but what happens if you comment out your current codes and replace them with the codes below.

Sub Filter_Selections() '
' Macro1 Macro
 Sheets("Selections").Range("O24:AJ124").AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
        Sheets("Selections").Range("O1:AJ24"), Unique:=False
End Sub

Sub Copy_Paste_Filtered_Selections()
Dim screenUpdateState As Boolean, statusBarState As Boolean
Dim CalcState, eventsState As Boolean, displayPageBreaksState As Boolean

displayPageBreaksState = Sheets("Selections").DisplayPageBreaks
  With Application
   screenUpdateState = .ScreenUpdating
      statusBarState = .DisplayStatusBar
           CalcState = .Calculation
         eventsState = .EnableEvents

                       .ScreenUpdating = False
                       .DisplayStatusBar = False
                       .Calculation = xlCalculationManual
                       .EnableEvents = False
  End With
 Sheets("Selections").DisplayPageBreaks = False
 
 Sheets("Selections").Range("A26:AO124").SpecialCells(12).Copy Sheets("Filtered Selections").Range("A10000").End(xlUp).Offset(2, 0)
  
  With Application
        .ScreenUpdating = screenUpdateState
        .DisplayStatusBar = statusBarState
        .Calculation = CalcState
        .EnableEvents = eventsState
  End With
Sheets("Selections").DisplayPageBreaks = displayPageBreaksState

End Sub

Sub Clear_Selections_Filter()
Sheets("Selections").ShowAllData
Application.Goto Sheets("Filtered Selections").Range("DH1")
End Sub

 

[Lucy89]

I don't think it will help as I believe there is something else happening with your workbook that you don't know/haven't revealed yet

Hi MARK858 it appears that you were right.

I had a good nights sleep last night and felt refreshed so had another look at the spreadsheet this morning. "Conditional Formatting" was the cause of my problem. I guess pretty colors do not help when there is a large amount of data and formulas/calculations.

A girl and her colors i have cleared the rules for the entire sheet and hey presto all works well!

Thank you both for your time and help, many thanks.

L