Very slow code....

[espenskeie]

Hello

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

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

 

[Albatros]

You should run the macro line by line and see exactly which instruction(s) take(s) so long to execute.

 

[espenskeie]

Ok, I have never done that before, how do I test line by line?

 

[Albatros]

GO into Developer tab, click on Macros icon, select the macro and then click on Step into. A Yellow Line should appear highlighting the procedure.
Then press F8 and the first line of code should be executed. Keep pressing F8 to move line by line. Once you have detected the culprit lines pressing F5 will execute the rest of the lines in one go.

 

[Norie]

Espen

If it is to do with 'jumping' between workbooks, don't do it.

You don't need that for what you want to do.

In fact in the KopierProdukter you aren't jumping between workbooks, you've used full workbook/worksheet references.

Sub copysheet()
Dim wbPris As Workbook
Dim wbProdukt As Workbook
Dim DesktopOpen8 As String
 
    Application.DisplayAlerts = False

    Application.Calculation = xlCalculationManual
 
    DesktopOpen8 = CreateObject("WScript.Shell").SpecialFolders("Desktop") & Application.PathSeparator
 
    Set wbPris = Workbooks.Open(DesktopOpen8 & "\SalesTools\Prisliste.xlsm")
 
    Set wbProdukt = Workbooks("Produktliste.xlsm")    ' use ThisWorkbook if the code is in the 'Produktliste' workbook
 
    wbPris.Sheets("Sheet1").Columns("C:P").ClearContents
 
    wbProdukt.Sheets("Priser").Sheets("Priser").Columns("C:C").Copy
    wbPris.Sheets("Sheet1").Columns("C1").PasteSpecial xlPasteValues
 
    wbProdukt.Sheets("Priser").Columns("E:E").Copy
    wbPris.Sheets("Sheet1").Columns("E1").PasteSpecial xlPasteValues
 
    wbProdukt.Sheets("Priser").Columns("N:P").Copy
    wbPris.Sheets("Sheet1").Columns("N1").PasteSpecial xlPasteValues
 
    Call KopierProdukter
 
    Application.Calculation = xlCalculationAutomatic
    Application.ScreenUpdating = True
 
End Sub

I think you should also try not to copy entire columns.

Is the last row in each column the same, or is there a specific column you could use to find the last row of data?

If there is it could be used to only copy the relevant no of rows.

 

[espenskeie]

Hi Norie

Thank you for answering me.

As for kopierprodukter the columns do have different sizes, or length, but the first one C:C will always be the longest one. So I could use the column C:C for a standard number of rows to copy.

Regards
Espen

 

[espenskeie]

Hi Albatros

Thank you for the information, I will go through it now and see if I can spot the problem.

Regards
Espen

 

[espenskeie]

Ok, that was quick. It seems like the most time-eating issue is when I open the other workbook.

It takes quite some time to open it. Even thought it only have a small ammount of data inside, and no codes or formulas.

I fell like my excel has become slower when opening and saving books.

This is where it stopped and took 10-15 seconds:

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"
    
    [B][SIZE="4"]Windows("Prisliste.xlsm").Activate[/SIZE][/B]
    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

 

[Norie]

If you didn't use Activate/Select etc this code would be quicker.

Try just using the last code I posted in place of what you've got.

The code I posted does the same without using Activate/Select.

 

[espenskeie]

Ok, I tried that code now, it's much more clean and tidy. But the slowdown project seems to be related to opening the book.

I have written many codes latly, and it seems to me that excel has become slower and slower. Could it be that I have opened up librarys under Tools in Developer?

Regards
Espen

 

[Norie]

Espen

I've never heard of anything like that.

Having a lot of references/add-ins could perhaps slow down opening Excel itself but it shouldn't impact on opening workbooks.

What happens when you just open the workbook(s) with a double click or whatever?