Macros running slow

[EvansB2]

Hi there,

I'm running the code below and it is taking 2 minutes to run. The code is repeated six times in the same module to identify the different workbooks and then copy over the relevant date in the stipulated range, this one being "HouseTypeB".

I can physically see the figures being updated when running the macro each time and I would though it should be instant.

Any help would be much appreciated.

Regards
Ben

Sub HouseTypeB()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
Set x = ThisWorkbook.Sheets(2)<o></o>
Set a = Workbooks("Lifecycle Model_WYPA").Sheets(3)<o></o>
For i = 19 To 74<o></o>
For J = 5 To 29<o></o>
<o></o>
x.Cells(i, J) = a.Cells(i, J)<o></o>
<o></o>
Next J<o></o>
<o></o>
Next i<o></o>
<o></o>
On Error GoTo complete<o></o>
<o></o>
complete:<o></o>
MsgBox "Lifecycle Updated", vbInformation<o></o>
<o></o>
End Sub<o></o>

 

[Trevor G]

Try adding the application to be note show the screen updating whilst it runs the macro so add this line before it begins and then place it back on once its run the macro so change False to True (Copy the code to the end of the macro)

Application.ScreenUpdating = False

 

[Ruddles]

What's the On Error directive doing?

Or did you omit the code which follows it?

 

[JackDanIce]

What is contained in the cells you are moving over? You do not appear to be doing any calculations so it may be simpler to use something like:

Sub HouseTypeB()<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /><o:p></o:p>

' Should really be declaring your variable data types...
Dim x as WorkSheet, a as WorkSheet
Dim i as Long, j as Long
 
Set x = ThisWorkbook.Sheets(2)<o:p></o:p>
Set a = Workbooks("Lifecycle Model_WYPA").Sheets(3)<o:p></o:p>

Application.ScreenUpdating = False
 
' For i = 19 To 74<o:p></o:p>
'   For J = 5 To 29
'     x.Cells(i, J) = a.Cells(i, J)
'   Next J
' Next i<o:p></o:p>

<o:p></o:p> 
<o:p>' Instead of the two nexted loops above, whatabout:</o:p>
<o:p>x.Range(Cells(19,5), Cells(74,29)).Copy</o:p>
<o:p>a.Range(Cells(19,5)</o:p><o:p>.PasteSpecial Paste:=xlvalues</o:p>
<o:p></o:p> 
Application.ScreenUpdating = True

On Error GoTo complete<o:p></o:p>
<o:p></o:p>
complete:<o:p></o:p>
MsgBox "Lifecycle Updated", vbInformation<o:p></o:p>
<o:p></o:p>
End Sub<o:p></o:p>

As Ruddles ask - what causes an error? Your original code implies you're just copying values over and then once done you'll get a MsgBox with the message you have anyway...

 

[EvansB2]

I have written the second code you suggested that I think will work better, however I get the error message "Compile error: Expected list separator or)" hanging over the word 'Paste'

Would appreciate your help.

Ben

 

[JackDanIce]

My typo. Change that line to:

a.Range(Cells(19,5)).PasteSpecial Paste:=xlvalues

I missed out the closing bracket (in red), hence your error message

 

[EvansB2]

I've done that, however, a run time error '1004' pops up 'Application-defined or object-defined error' and against this line of code.

Ben

 

[Sektor]

May be this.

Sub HouseTypeB()
    
    Dim x As Worksheet, a As Worksheet
    
    Set x = ThisWorkbook.Sheets(2)
    Set a = Workbooks("Lifecycle Model_WYPA").Sheets(3)
    
    a.Range("E19:AC74").Copy x.Range("E19")
    
    MsgBox "Lifecycle Updated", vbInformation

End Sub

 

[EvansB2]

Works a treat!

Thank you for your patience.

Ben

 

[EvansB2]

Sorry! Just noticed the macro copies the formula of the worksheet it is copying - Is it possible just to copy over the values?

Ben