CODEWITHSUNDEEP
vbaexcel
stack mark
stack mark

Reputation: 101

Copying rows and pasting in differnt sheet using VBA (code edited)

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

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

Upvotes: 0

Views: 39

Answers (2)

R3uK
R3uK

Reputation: 14537

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

Upvotes: 2

Rick de Gier
Rick de Gier

Reputation: 62

find smaller date pseudo code

Dim rng As Range, cell As Range
Set rng = Range("K2:K200")
For Each cell In rng
  if cell.value < userdate then
    //TODO copy to previous
  end if
Next cell

Upvotes: 1

Related Questions