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>

 

[JackDanIce]

Change Sektor's code to:

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").PasteSpecial Paste:=xlValues
    
    MsgBox "Lifecycle Updated", vbInformation

End Sub

 

[Sektor]

Only values.

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").PasteSpecial xlPasteValues
    
    MsgBox "Lifecycle Updated", vbInformation

End Sub

 

[EvansB2]

Thanks - All good.

Ben