Copy rows and paste into a different sheet using VBA (edited code)

advertisements

I have the below code that gets the date input from the user. What I want to do is:

  • To find all the dates before the entered date in COl K of sheet "Latency"
  • To copy the entire row and paste it in a different sheet named "Previous".

My code:

Sub Previousweek()
Dim userdate

userdate = InputBox("Enter the date", "Enter Date", Date)
 If IsDate(userdate) Then
  'logic here
    End If

End Sub

Updated Code:

Sub Previousweek()
Dim userdate

userdate = InputBox("Enter the date", "Enter Date", Date)
Dim rng As Range, cell As Range
Set rng = Range("K2:K200")
For Each cell In rng
  If cell.Value < userdate Then
    cell.EntireRow.Copy
    Sheets("Previous").Range("A65536").End(xlUp).Offset(1, 0).Select
            Sheets("Previous").Paste
        End If
     Next cell
   End Sub

This is showing error in below line as Subscript out of range :

Sheets("Previous").Range("A65536").End(xlUp).Offset(1, 0).Select


Just change Previous in With ThisWorkbook.Sheets("Previous")
to the name of the sheet you want to paste the data in!

Sub Previousweek()
    Dim UserDate As String
    Dim CeLL As Range
    UserDate = InputBox("Enter the date", "Enter Date", Date)

    If IsDate(UserDate) Then
        With ThisWorkbook.Sheets("Previous")
            For Each CeLL In ThisWorkbook.Sheets("Latency").Range("K2:K200").Cells
                If CeLL.Value < UserDate Then
                    CeLL.EntireRow.Copy .Range("A" & .Rows.Count).End(xlUp).Offset(1, 0)
                End If
            Next CeLL
        End With 'ThisWorkbook.Sheets("Previous")
    Else
    End If
End Sub