Crashing Macro

J

JamesRo

New Member
Joined
Jun 30, 2022
Messages
26
Office Version
  1. 2021
Platform
  1. Windows
Hi.
Wondering if anybody can shine any light...

Win 11 Pro
Office MHAB 2021

Have a problem with a macro that was previously working okay but now when I run it, Excel hangs and eventually crashes.
Nothing in the macro has changed and I can run it in a different sheet and works fine.
The workbook has connections to SQL server database and is fairly complicated to paste here but the macro is below.
I have stepped through the code and seems to get to the "copy I column comments" ActiveSheet.Paste line then hangs.
I know this is vague but is there anything to check?
As I said, the macro was working fine until a couple of weeks ago, any reason the paste might be failing?

TIA


VBA Code: 
Sub New_Week()
'
    If MsgBox("Are you sure?" & vbNewLine & "This action cannot be undone!", vbYesNo) = vbNo Then Exit Sub
'
    ActiveSheet.Unprotect

' copy comments area WEEK 1

' Mon
    Range("E58:H58").Select
    Selection.Copy
    Range("E2:H2").Select
    ActiveSheet.Paste
    
'Tue
    Range("E69:H69").Select
    Selection.Copy
    Range("E13:H13").Select
    ActiveSheet.Paste
    
'Wed
    Range("E81:H81").Select
    Selection.Copy
    Range("E25:H25").Select
    ActiveSheet.Paste
    
'Thu
    Range("E93:H93").Select
    Selection.Copy
    Range("E37:H37").Select
    ActiveSheet.Paste
    
'Fri
    Range("E104:H104").Select
    Selection.Copy
    Range("E48:H48").Select
    ActiveSheet.Paste
    
    ' copy comments area WEEK 2
'Mon
    Range("E114:H114").Select
    Selection.Copy
    Range("E58:H58").Select
    ActiveSheet.Paste

'Tue
    Range("E125:H125").Select
    Selection.Copy
    Range("E69:H69").Select
    ActiveSheet.Paste

'Wed
    Range("E137:H137").Select
    Selection.Copy
    Range("E81:H81").Select
    ActiveSheet.Paste

'Thu
    Range("E149:H149").Select
    Selection.Copy
    Range("E93:H93").Select
    ActiveSheet.Paste

'Fri
    Range("E160:H160").Select
    Selection.Copy
    Range("E104:H104").Select
    ActiveSheet.Paste
    
    ' copy comments area WEEK 3
'Mon
    Range("E170:H170").Select
    Selection.Cut
    Range("E114:H114").Select
    ActiveSheet.Paste

'Tue
    Range("E181:H181").Select
    Selection.Cut
    Range("E125:H125").Select
    ActiveSheet.Paste

'Wed
    Range("E193:H193").Select
    Selection.Cut
    Range("E137:H137").Select
    ActiveSheet.Paste

'Thu
    Range("E205:H205").Select
    Selection.Cut
    Range("E149:H149").Select
    ActiveSheet.Paste

'Fri
    Range("E216:H216").Select
    Selection.Cut
    Range("E160:H160").Select
    ActiveSheet.Paste
    
' unprotect editable cells

    Range("E170:H170").Locked = False
    Range("I170:I179").Locked = False
    
    Range("E181:H181").Locked = False
    Range("I181:I191").Locked = False
    
    Range("E193:H193").Locked = False
    Range("I193:I203").Locked = False
    
    Range("E205:H205").Locked = False
    Range("I205:I214").Locked = False
    
    Range("E216:H216").Locked = False
    Range("I216:I223").Locked = False
   
    
' copy I column comments
    Range("I58:I223").Select
    Selection.Copy
    Range("I2:I167").Select
    ActiveSheet.Paste
    
    Range("I170:I223").Select
    Selection.ClearContents
   
' copy VM quantities
'W1 Mon
    Range("P63").Select
    Selection.Copy
    Range("P7").Select
    ActiveSheet.Paste
'W1 Tue
    Range("P74").Select
    Selection.Copy
    Range("P18").Select
    ActiveSheet.Paste
'W1 Wed
    Range("P86").Select
    Selection.Copy
    Range("P30").Select
    ActiveSheet.Paste
'W1 Thu
    Range("P98").Select
    Selection.Copy
    Range("P42").Select
    ActiveSheet.Paste
'W1 Fri
    Range("P109").Select
    Selection.Copy
    Range("P53").Select
    ActiveSheet.Paste
    
'W2 Mon
    Range("P119").Select
    Selection.Copy
    Range("P63").Select
    ActiveSheet.Paste
'W2 Tue
    Range("P130").Select
    Selection.Copy
    Range("P74").Select
    ActiveSheet.Paste
'W2 Wed
    Range("P142").Select
    Selection.Copy
    Range("P86").Select
    ActiveSheet.Paste
'W2 Thu
    Range("P154").Select
    Selection.Copy
    Range("P98").Select
    ActiveSheet.Paste
'W2 Fri
    Range("P165").Select
    Selection.Copy
    Range("P109").Select
    ActiveSheet.Paste
    
'W3 Mon
    Range("P175").Select
    Selection.Copy
    Range("P119").Select
    ActiveSheet.Paste
'W3 Tue
    Range("P186").Select
    Selection.Copy
    Range("P130").Select
    ActiveSheet.Paste
'W3 Wed
    Range("P198").Select
    Selection.Copy
    Range("P142").Select
    ActiveSheet.Paste
'W3 Thu
    Range("P210").Select
    Selection.Copy
    Range("P154").Select
    ActiveSheet.Paste
'W3 Fri
    Range("P221").Select
    Selection.Copy
    Range("P165").Select
    ActiveSheet.Paste
   
' add comments

   ' Range("E158:I207").Select
   ' Selection.ClearContents
    'Range("E128").Select
    'ActiveCell.FormulaR1C1 = "Mxxx"
    'Range("E128").Select
    'Selection.Copy
   
    'Range("E136").Select
    'ActiveSheet.Paste
   
    'Range("E145").Select
    'ActiveSheet.Paste
   
    'Range("E153").Select
    'ActiveSheet.Paste
   
    'Range("E161").Select
    'ActiveSheet.Paste
    
' zero VM for new week

    Range("P175").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("P186").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("P198").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("P210").Select
    ActiveCell.FormulaR1C1 = "0"
    Range("P221").Select
    ActiveCell.FormulaR1C1 = "0"
    
' set cell formatting for new week
    Range("I167").Select
    Selection.Copy
    
    Range("E170:I170,I171:I179,E181:I181,I182:I191,E193:I193,I194:I203,E205:I205,I206:I214,E216:I216,I217:I223").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False

    
    
    Range("A2").Select
        
    ActiveSheet.Protect
    
    End Sub
 

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.
Sort by date Sort by votes
Update:

Looks like the pastes do complete but take a very long time ~15minutes for the larger selections...
 
Upvote 0
You could try adding inside your macro:
at the beginning:
Application.ScreenUpdating = False
and at the end:
Application.ScreenUpdating = True

and if you have many formulas throughtout the sheet you could add manual/automatic Calculation and Application.EnableEvents False/True if you have a sheet macro that trigger with Worksheet_Change event.
 
Last edited:
Upvote 0
As per @rollis13 's advice, turn calculation to manual for the duration of the code (don't forget to turn it back to Automatic at the conclusion). But also, please get rid of all those Selects. For example, this:
VBA Code: 
Range("E58:H58").Select
    Selection.Copy
    Range("E2:H2").Select
    ActiveSheet.Paste

can be written in one line like this:
VBA Code: 
Range("E58:H58").Copy Range("E2")

and losing all those selects will help speed things up.
 
Upvote 0
Thanks for the suggestions, maybe helped a little but still extremely slow in pasting.
 
Upvote 0
If you make ALL the changes suggested to ALL of the sections of your code, your final code should look something like this:
VBA Code: 
Sub New_Week()

    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    Application.EnableEvents = False
'
    If MsgBox("Are you sure?" & vbNewLine & "This action cannot be undone!", vbYesNo) = vbNo Then Exit Sub
'
    ActiveSheet.Unprotect

' copy comments area WEEK 1

' Mon
    Range("E58:H58").Copy Range("E2")
    
'Tue
    Range("E69:H69").Copy Range("E13")
    
'Wed
    Range("E81:H81").Copy Range("E25")
    
'Thu
    Range("E93:H93").Copy Range("E37")
    
'Fri
    Range("E104:H104").Copy Range("E48")
    
    ' copy comments area WEEK 2
'Mon
    Range("E114:H114").Copy Range("E58")

'Tue
    Range("E125:H125").Copy Range("E69")

'Wed
    Range("E137:H137").Copy Range("E81")

'Thu
    Range("E149:H149").Copy Range("E93")

'Fri
    Range("E160:H160").Copy Range("E104")
    
    ' copy comments area WEEK 3
'Mon
    Range("E170:H170").Cut Range("E114")

'Tue
    Range("E181:H181").Cut Range("E125")

'Wed
    Range("E193:H193").Cut Range("E137")

'Thu
    Range("E205:H205").Cut Range("E149")

'Fri
    Range("E216:H216").Cut Range("E160")
    
' unprotect editable cells

    Range("E170:H170").Locked = False
    Range("I170:I179").Locked = False
    
    Range("E181:H181").Locked = False
    Range("I181:I191").Locked = False
    
    Range("E193:H193").Locked = False
    Range("I193:I203").Locked = False
    
    Range("E205:H205").Locked = False
    Range("I205:I214").Locked = False
    
    Range("E216:H216").Locked = False
    Range("I216:I223").Locked = False
   
    
' copy I column comments
    Range("I58:I223").Copy Range("I2")
    
    Range("I170:I223").ClearContents
   
' copy VM quantities
'W1 Mon
    Range("P63").Copy Range("P7")
'W1 Tue
    Range("P74").Copy Range("P18")
'W1 Wed
    Range("P86").Copy Range("P30")
'W1 Thu
    Range("P98").Copy Range("P42")
'W1 Fri
    Range("P109").Copy Range("P53")
    
'W2 Mon
    Range("P119").Copy Range("P63")
'W2 Tue
    Range("P130").Copy Range("P74")
'W2 Wed
    Range("P142").Copy Range("P86")
'W2 Thu
    Range("P154").Copy Range("P98")
'W2 Fri
    Range("P165").Copy Range("P109")
    
'W3 Mon
    Range("P175").Copy Range("P119")
'W3 Tue
    Range("P186").Copy Range("P130")
'W3 Wed
    Range("P198").Copy Range("P142")
'W3 Thu
    Range("P210").Copy Range("P154")
'W3 Fri
    Range("P221").Copy Range("P165")
   
' add comments

   ' Range("E158:I207").ClearContents
    'Range("E128").FormulaR1C1 = "Mxxx"
    'Range("E128").Copy Range("E136")
    'Range("E128").Copy Range("E145")
    'Range("E128").Copy Range("E153")
    'Range("E128").Copy Range("E161")
    
' zero VM for new week

    Range("P175").FormulaR1C1 = "0"
    Range("P186").FormulaR1C1 = "0"
    Range("P198").FormulaR1C1 = "0"
    Range("P210").FormulaR1C1 = "0"
    Range("P221").FormulaR1C1 = "0"
    
' set cell formatting for new week
    Range("I167").Copy
    
    Range("E170:I170,I171:I179,E181:I181,I182:I191,E193:I193,I194:I203,E205:I205,I206:I214,E216:I216,I217:I223").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
            SkipBlanks:=False, Transpose:=False
    
    Range("A2").Select
        
    ActiveSheet.Protect
    
    Application.ScreenUpdating = True
    Application.Calculation = xlCalculationAutomatic
    Application.EnableEvents = True

End Sub
If you run this version, does it run any faster?
 
Upvote 0
Thanks, I have copy/pasted but it still hangs.
I step through it gets up to the "copy comments area WEEK 3" quite normally then starts to slow down on those ranges. Around 5-10 seconds for each of those to complete.
I can then step through fine until "copy I column comments" range then it will become unresponsive.

There is no real difference in the week 3 area of the sheet compared to any earlier week in terms of formulae etc. In fact it is identical apart from dates.
 
Upvote 0
Is it possible to share your file via Google Drive, Dropbox or similar file sharing platform?
 
Upvote 0
I suspect a network/system/computer issue on your side.
There is nothing special in the code that would be causing those sort of issues.
Does this workbook contain any links or have any interaction with other files or programs?
 
Upvote 0
You must log in or register to reply here.

Similar threads

N
  • Question Question
Adding to an existing MACRO
Replies
2
Views
125
Northern NY Design
N
A
  • Question Question
VBA help - Locking & unlocking on certain times of day
2
Replies
17
Views
468
dmt32
D
P
  • Question Question
Help with Vba Code
Replies
3
Views
478
Coyotex3
C
C
  • Question Question
How to get my macro to stop at the last recognized line
Replies
6
Views
555
Coco1
C
D
  • Solved
Use the same macro on a weekly file which contains date info
Replies
1
Views
449
John_w
J

Forum statistics

Threads
1,224,822
Messages
6,181,165
Members
453,021
Latest member
Justyna P

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top