Results 1 to 3 of 3

How to improve the running speed of this VBA macro code?

Threaded View

  1. #1
    Registered User
    Join Date
    10-08-2012
    Location
    Amsterdam, Netherlands
    MS-Off Ver
    Excel 2010
    Posts
    8

    How to improve the running speed of this VBA macro code?

    I do not have much experience with writing macros, and therefore need the help of this community for the following issue encountered:

    My macro copies a range of values entered in a vertical range in one worksheet and then pastes the values horizontally (transpose) in another worksheet. It would in theory paste the values from the first sheet to first row of the second worksheet which does not have content. Since the first five rows have contents, it thus pastes the values to the sixth row. The problem I have with the running of the macro is that I feel like it is too slow and I would therefore like it to run faster.

    I have the same macro doing the same thing but that instead pastes the values to another worksheet to the first row, and it runs perfect.

    My best guess is therefore that the second macro is running slow because it has to start pasting on the sixth row and there may be some contents on the first 5 rows that take a lot of time for the macro to go through (there a lot of cell references to other workbooks) to determine where the next row for pasting should be. That is my best guess though and since I hardly know anything about macros, I cannot say for sure what the problem is.

    I hereby provide you with the code of my macro and sincerely hope that somebody can tell me what is making my macro slow and provide me with a solution as to how to make it run faster. I am thinking that a solution might potentially be that the macro should not consider the first five rows of data and start pasting immediately on row 6 for the first entry. Then on row 7 the next time, and etc. This might be a solution but I do not know how to write the code in a way that it would do that.

    Thank you for taking time and helping me to find a solution, here is the code:

    Sub Macro1()
    wksPartsDataEntry.Unprotect
    Sheet11.Unprotect
    Application.ScreenUpdating = False
    Application.Calculation = xlCalculationManual
    
        Dim historyWks As Worksheet
        Dim inputWks As Worksheet
    
        Dim nextRow As Long
        Dim oCol As Long
    
        Dim myCopy As Range
        Dim myTest As Range
        
        Dim lRsp As Long
    
        Set inputWks = wksPartsDataEntry
        Set historyWks = Sheet11
        
          'cells to copy from Input sheet - some contain formulas
          Set myCopy = inputWks.Range("OrderEntry2")
      
          With historyWks
              nextRow = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0).Row
          End With
      
          With inputWks
              Set myTest = myCopy.Offset(0, 2)
      
              If Application.Count(myTest) > 0 Then
                  MsgBox "Please fill in all the cells!"
                  Exit Sub
              End If
          End With
      
          With historyWks
              With .Cells(nextRow, "A")
                  .Value = Now
                  .NumberFormat = "mm/dd/yyyy hh:mm:ss"
              End With
              .Cells(nextRow, "B").Value = Application.UserName
              oCol = 3
              myCopy.Copy
              .Cells(nextRow, 3).PasteSpecial Paste:=xlPasteValues, Transpose:=True
              Application.CutCopyMode = False
          End With
          
          'clear input cells that contain constants
          With inputWks
            On Error Resume Next
               With myCopy.Cells.SpecialCells(xlCellTypeConstants)
                    .ClearContents
                    Application.GoTo .Cells(1) ', Scroll:=True
               End With
            On Error GoTo 0
          End With
      
    Application.Calculation = xlCalculationAutomatic
    wksPartsDataEntry.Protect
    Sheet11.Protect
    Application.ScreenUpdating = True
    End Sub
    Last edited by arlu1201; 10-24-2012 at 01:45 PM.

Thread Information

Users Browsing this Thread

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

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