Very slow code....

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
636
Office Version
  1. 2016
Platform
  1. Windows
Hello

Is there anyone who could tell my why this code is very slow? I copy columns from one book to another:

Code:
Sub KopierProdukter()

    Windows("Produktliste.xlsm").Activate
    Sheets("Produktliste").Select
    Range("C:C, L:L, U:U, AD:AD, AM:AM, AV:AV, BE:BE, BN:BN").Select
    Selection.Copy
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet2").Select
    Range("C1").Select
    ActiveSheet.Paste
        
    Workbooks("Prisliste.xlsm").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    
End Sub

Regards
Espen
 

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
Hello

You should not copy entire columns, but "fixed" (static or danymic) ranges.

Also, there's is a difference between copying cells and transferring cell values. In the latter case, it is usually faster since formatting is neglected.
 
Upvote 0
Hmm, could you give me an example on how it should look, I have tried to go for C:C200, L:L200 and so on, but I couldn't get it to work. I'm still a newbee to a lot of this stuff.

Regards
Espen
 
Upvote 0
Maybe this will help you:

Code:
Sub KopierProdukter()
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
 
    Windows("Produktliste.xlsm").Activate
    Sheets("Produktliste").Range("C1:C100, L1:L100, U1:U100, AD1:AD100, AM1:AM100, AV1:AV100, BE1:BE100, BN1:BN100").Copy
 
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet2").Range("C1").Paste
 
    ActiveWorkbook.Save
    ActiveWorkbook.Close
 
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
End Sub
 
Upvote 0
Hello

Is there anyone who could tell my why this code is very slow? I copy columns from one book to another:

Code:
Sub KopierProdukter()
[COLOR=Red]Application.ScreenUpdating = False[/COLOR]
    
'Windows("Produktliste.xlsm").Activate
    'Sheets("Produktliste").Select
   ' Range("C:C, L:L, U:U, AD:AD, AM:AM, AV:AV, BE:BE, BN:BN").Select
    'Selection.Copy

[COLOR=Red]Workbooks("Produktliste.xlsm").Sheets("Produktliste").Range("Your range").copy

Workbooks("Prisliste.xlsm").Sheets("Sheet2").Range("C1"). PasteSpecial xlPasteValues[/COLOR]

    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet2").Select
    Range("C1").Select
    ActiveSheet.Paste
        
    Workbooks("Prisliste.xlsm").Activate
    ActiveWorkbook.Save
    ActiveWorkbook.Close
    [COLOR=Red]Application. Screenupdating=True[/COLOR]
End Sub
Regards
Espen

This will work much faster. You don't need to select things to copy them. And turning off screenupdating speeds things up by a big margin.
 
Upvote 0
Thank you guys. I tried two of the codes you suggested. It seems to me that there is something weird going on, it is extremely slow when it opens the other workbook.

It takes like 10 seconds. Here are the two codes that I run when it really slows down the speed.


Code:
Sub copysheet()

    Dim DesktopOpen8 As String
    
    DesktopOpen8 = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
    Workbooks.Open DesktopOpen8 & "\SalesTools\Prisliste.xlsm"
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet1").Columns("C:P").ClearContents

    Windows("Produktliste.xlsm").Activate
    Sheets("Priser").Columns("C:C").Copy
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet1").Columns("C:C").PasteSpecial xlPasteValues

    
    Windows("Produktliste.xlsm").Activate
    Sheets("Priser").Columns("E:E").Copy
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet1").Columns("E:E").PasteSpecial xlPasteValues

    
    Windows("Produktliste.xlsm").Activate
    Sheets("Priser").Columns("N:P").Copy
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet1").Columns("N:P").PasteSpecial xlPasteValues
    Range("F2").Select
    
    Call KopierProdukter

End Sub

Sub KopierProdukter()
   Application.ScreenUpdating = False
   
Workbooks("Produktliste.xlsm").Sheets("Produktliste").Range("C:C, L:L, U:U, AD:AD, AM:AM, AV:AV, BE:BE, BN:BN").Copy

Workbooks("Prisliste.xlsm").Sheets("Sheet2").Range("C1").PasteSpecial xlPasteValues

Application.ScreenUpdating = True
End Sub
 
Upvote 0
Add this at the start of copysheet():

Code:
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With

... and this at the end of copysheet():

Code:
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
 
Upvote 0
Thank you JamesW for the code. Unfortunately it didn't speed it up, I think there is some other stuff jamming it up.

Norie: I'm not sure where it slows down. I just think it has to do with the jumping between the two workbooks.

I have the following codes:

Code:
Sub copysheet()

    Dim DesktopOpen8 As String
    
    With Application
        .ScreenUpdating = False
        .Calculation = xlCalculationManual
    End With
    
    DesktopOpen8 = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
    Workbooks.Open DesktopOpen8 & "\SalesTools\Prisliste.xlsm"
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet1").Columns("C:P").ClearContents

    Windows("Produktliste.xlsm").Activate
    Sheets("Priser").Columns("C:C").Copy
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet1").Columns("C:C").PasteSpecial xlPasteValues

    
    Windows("Produktliste.xlsm").Activate
    Sheets("Priser").Columns("E:E").Copy
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet1").Columns("E:E").PasteSpecial xlPasteValues

    
    Windows("Produktliste.xlsm").Activate
    Sheets("Priser").Columns("N:P").Copy
    
    Windows("Prisliste.xlsm").Activate
    Sheets("Sheet1").Columns("N:P").PasteSpecial xlPasteValues
    Range("F2").Select
    
    With Application
        .ScreenUpdating = True
        .Calculation = xlCalculationAutomatic
    End With
    
    Call KopierProdukter

End Sub
Code:
Sub KopierProdukter()
   Application.ScreenUpdating = False
   
Workbooks("Produktliste.xlsm").Sheets("Produktliste").Range("C:C, L:L, U:U, AD:AD, AM:AM, AV:AV, BE:BE, BN:BN").Copy

Workbooks("Prisliste.xlsm").Sheets("Sheet2").Range("C1").PasteSpecial xlPasteValues

Application.ScreenUpdating = True
End Sub

These are the two last modules in a section of 4 codes, but as far as I can see the first one are no problems regarding the speed. It is somewhere in these, when it opens the workbook I think.

Regards
Espen
 
Upvote 0

Forum statistics

Threads
1,213,490
Messages
6,113,957
Members
448,535
Latest member
alrossman

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