title: How to improve the speed of VBA macro code?
body: <p>I do not have much experience with writing macros, and therefore need the help of this community for the following issue encountered:</p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>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. </p>
  
  <p>Thank you for taking time and helping me to find a solution, here is the code:</p>
  
  <pre><code>Sub Macro1()
  Application.ScreenUpdating = False
  
      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) &gt; 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.ScreenUpdating = True
  End Sub
  </code></pre>