Very slow code....

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
632
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
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

wigi

Well-known Member
Joined
Jul 10, 2006
Messages
7,958
Office Version
  1. 365
Platform
  1. Windows
  2. Mobile
  3. Web
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.
 

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
632
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
 

DaveL485

New Member
Joined
Nov 6, 2009
Messages
25
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

Try C1:C200 and L1:L200
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197

ADVERTISEMENT

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
 

Albatros

New Member
Joined
Aug 31, 2011
Messages
26
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.
 

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
632

ADVERTISEMENT

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
 

JamesW

Well-known Member
Joined
Oct 30, 2009
Messages
1,197
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
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
76,218
Office Version
  1. 365
Platform
  1. Windows
Which part takes 10 seconds?
 

espenskeie

Well-known Member
Joined
Mar 30, 2009
Messages
632
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,809
Messages
5,598,202
Members
414,218
Latest member
speedbit

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
Top