Periodic Freezing - Gremlins in the Macro?

[dominicwellsuk]

Hi, I've searched far and wide on this forum for users with a similar problem but mine seems to be unique.

I have the following code to clear the sheet then bring in fresh data from various worksheets and paste it sequentially in the new workbook...

Sub copyconcerns()

ScreenUpdating = False
Application.DisplayAlerts = False

Range("B7:U3000").Select
Selection.ClearContents
Range("A1").Select

Application.Workbooks.Open ("d:\DW.xls")
Worksheets("Log").Activate
LastRow = Range("B2000").End(xlUp).Row
Range("B4:U" & LastRow).Activate
Selection.Copy
Workbooks("DW.xls").Close SaveChanges = False
Windows("Management Document v2.xls").Activate
Range("B7").Select
ActiveSheet.Paste

Application.Workbooks.Open ("d:\RM.xls")
Worksheets("Log").Activate
LastRow = Range("B2000").End(xlUp).Row
Range("B4:U" & LastRow).Select
Selection.Copy
Workbooks("RM.xls").Close SaveChanges = False
Windows("Management Document v2.xls").Activate
Range("B65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

End Sub

This works a treat in terms of what I want it to do, but as soon as the copied data is in the new sheet, every 5 seconds or so, the sheet stalls for 2 seconds, meaning working with it is a nightmare. I've tried deleting the data from the sheet but the stalling problem remains, even when I make it an entirely blank workbook!

Any idea what might be causing this? There are no formulas or lookups etc in the data that is being copied, it's just pure text and numbers. I've tried removing autocalculation and autoupdating of links but to no avail, and there is nothing to calculate or link to anyway!

The amount of data I'm working with is about 40 rows x 15 columns, so not very much.

This has me stumped. Any ideas on how to stop this would be greatly appreciated.

Thanks

 

[Jerry Sullivan]

There are several aspects of the code that might be causing the problem.

Try the code below which is a revision of the top half of your procedure....
(after changing "MySheet" to match your sheet name).

Sub copyconcerns()
    Dim LastRow As Long
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    Range("B7:U3000").ClearContents
    Workbooks.Open ("d:\DW.xls")
    With Worksheets("Log")
        LastRow = .Range("B2000").End(xlUp).Row
        .Range("B4:U" & LastRow).Copy Destination:= _
        Workbooks("Management Document v2.xls") _
            .Sheets("MySheet").Range("B7")
    End With
    Workbooks("DW.xls").Close SaveChanges:=False
    '...similar for second half of code
    
    Application.DisplayAlerts = True
End Sub

A few notes:
1. You don't need to use Activate, Select and Selection
2. Make fully qualified references that include Workbook, Sheet and Range.

 

[dominicwellsuk]

Thanks, that seems to have made things much better. I still get a short freeze every 10 seconds or so, but the sheet is workable. I had no idea the structure of a macro could affect the sheet in that way.

 

[Jerry Sullivan]

Thanks, that seems to have made things much better. I still get a short freeze every 10 seconds or so, but the sheet is workable.

Hmm... that still sounds like a problem worth fixing. Have you isolated it to just this code and/or this set of workbooks?

What happens when you...
Run this on another PC?
Run this, close Excel and reopen the workbook - does keep doing the short freezes?

I had no idea the structure of a macro could affect the sheet in that way.

Just to clarify, the changes to the structure of the macro don't make it work differently. By simplifying the code it was easier to identify places that the code had been copying and pasting without specific ranges identified.

I also picked up a couple syntax errors like...
Workbooks("DW.xls").Close SaveChanges = False
which should be:
Workbooks("DW.xls").Close SaveChanges:= False

So, it's hard to say which changes contributed to the improvement- but it wasn't the structure per se.