Results 1 to 10 of 10

Speed up code? This simple VBA code makes me a report but its taking 15 minutes to finish.

Threaded View

1.jclozano Speed up code? This simple... 05-17-2018, 08:37 PM
BoredWorker Re: Speed up code? This... 05-17-2018, 09:31 PM
1.jclozano Re: Speed up code? This... 05-17-2018, 09:35 PM
bakerman2 Re: Speed up code? This... 05-17-2018, 10:00 PM
1.jclozano Re: Speed up code? This... 05-18-2018, 04:28 PM
1.jclozano Re: Speed up code? This... 05-18-2018, 08:05 PM
bakerman2 Re: Speed up code? This... 05-18-2018, 08:28 PM
jolivanes Re: Speed up code? This... 05-18-2018, 11:40 PM
jolivanes Re: Speed up code? This... 05-18-2018, 11:41 PM
1.jclozano Re: Speed up code? This... 05-21-2018, 04:34 PM
  1. #1
    Registered User
    Join Date
    09-25-2017
    Location
    London
    MS-Off Ver
    7
    Posts
    13

    Speed up code? This simple VBA code makes me a report but its taking 15 minutes to finish.

    This macro is for a bus workshop, it reviews the excel DB and makes me a report of all the expenses per bus which ends up giving 2k rows.

    Can you please tell me how to speed up this macro? 15 minutes is a lot of time.

    Sub getallExpensesperbus()
    
    Application.ScreenUpdating = False
        
    Dim datasheet As Worksheet
    Dim reportsheet As Worksheet
    Dim selectsheet As Worksheet
    
    Dim idcamion As String
    Dim finalrow As Integer
    Dim i As Integer
    
    Set datasheet = Page1
    Set reportsheet = Page2
    Set selectsheet = Page3
    
    reportsheet.Select
    reportsheet.Range("A4:K300000").ClearContents
    Sheets("BDproducts").Visible = True
    datasheet.Select
    finalrow = Page1.Range("Q2").Value
    
    For i = 2 To finalrow
        If Cells(i, 8).Value <> "" Then
        price = Cells(i, 3).Value
        ID = Cells(i, 2).Value
        product = Cells(i, 1).Value
        unit = Cells(i, 4).Value
        quantity = Cells(i, 5).Value
        address = Cells(i, 9).Value
        time = Cells(i, 10).Value
        comments = Cells(i, 7).Value
        side = Cells(i, 13).Value
        mileage = Cells(i, 14).Value
        bus= Cells(i, 8).Value
        
        reportsheet.Select
        finalrow2 = WorksheetFunction.CountA(Range("A:A")) + 2
        
        Cells(finalrow2, 1).Value = bus
        Cells(finalrow2, 2).Value = product
        Cells(finalrow2, 3).Value = ID
        Cells(finalrow2, 4).Value = unit
        Cells(finalrow2, 5).Value = quantity
        Cells(finalrow2, 6).Value = price
        
        Cells(finalrow2, 7).Value = adress
        Cells(finalrow2, 8).Value = time
        Cells(finalrow2, 9).Value = comments
        Cells(finalrow2, 10).Value = side
        Cells(finalrow2, 11).Value = mileage
        
        datasheet.Select
        End If
    Next i
    
    reportsheet.Select
        Range("A4").Select
    
    Application.ScreenUpdating = True
    
    End Sub
    Last edited by jeffreybrown; 05-17-2018 at 09:18 PM. Reason: Please use code tags when posting code!

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 9
    Last Post: 04-17-2016, 07:34 PM
  2. Agging report, openingbalance report,customer report vab code was not working
    By rana19 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-28-2015, 03:38 AM
  3. Replies: 4
    Last Post: 08-18-2015, 10:02 AM
  4. [SOLVED] VBA Delete Row Code - Need more efficient code to speed it up
    By matcapir in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 07-02-2015, 04:32 PM
  5. VBA code to analyse data unless function code makes more sense than VBA,
    By vorabj in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 05-03-2015, 12:38 PM
  6. [SOLVED] Vba code makes workboos slow. Any alternate code ??
    By satputenandkumar0 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 04-23-2014, 04:30 AM
  7. [SOLVED] Slow VBA Code for simple Loop and add pasted values Any Way to Speed Up
    By John Vieren in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-10-2013, 11:16 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1