1

I am trying to copy and paste data from one sheet to the next from a selected range entered by the user. TxtDateStart takes the start date, and TxtDateEnd takes the end date. Then it would copy and paste the data from the range of dates to a new sheet. When I run the code in a form, it works but I rather have the form call the module. This is where I get the run-time error. I'm no expert in VBA, help would be appreciated. The sheet where the data is is called Unit2Data, and the sheet i want to paste the data is Graphing Sheet.

The error occurs in this line 

Sheets("Unit2Data").Range(Cells(i, 1), Cells(i, 73)).Select

Sub Unit2Data()

Dim lrow As Long, i As Long, x As Date, y As Date, erow As Long

x = TxtDateStart
y = TxtDateEnd

'Find the Last Row of Sheet1
lrow = Sheets("Unit2Data").Range("A" & Rows.Count).End(xlUp).Row

'start counting from row 3 to last row
For i = 4 To lrow
' Date value converted as numeric value by multiplying with number 1
If Cells(i, 1) * 1 >= x * 1 Then
If Cells(i, 1) * 1 <= y * 1 Then

'If above conditions matched then select the matched range/ entire column

Sheets("Unit2Data").Range(Cells(i, 1), Cells(i, 73)).Select

'copy the selected row
Selection.Copy

'to make sheet2 active where we want to paste the selected row
Sheets("Graphing Sheet").Activate


'to find the empty row from where the copied row of sheet1 to be pasted in sheet2
erow = Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Row

'to activate or select the empty row of sheet2
ActiveSheet.Cells(erow, 1).Select

'paste the copied data
ActiveSheet.Paste

'to deselect the copy and selected mode
Application.CutCopyMode = False

'for above the if we need 3 end if to close if conditions
End If
End If
'to activate sheet1 for searching the matched data
Sheets("Unit2Data").Activate
'continue for look until above matched found
Next i
End Sub

Date              Data 
01/01/2019          2
02/02/2019          3
flamingbird123
  • 133
  • 1
  • 9
  • 1
    You need to specify which worksheet you working from. Who knows which sheet is active when this line runs `If Cells(i, 1) * 1 >= x * 1 Then`? You can also avoid using `.Activate` or [`.Select`](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). In fact, copy pasting can be done in one line, preventing Excel to store data onto the clipboard. Most of the time, copy pasting can be avoided alltogether. That being said, what line do you get an error on? – JvdV Jul 10 '19 at 13:33
  • I added the Line in which I get the error on. I made the form open on another sheet called "Form." The form is opened when a button is pressed. And when a button is pressed on the form it runs the module. – flamingbird123 Jul 10 '19 at 13:35

1 Answers1

2

First you should avoid using Select in VBA. There, almost always, are better ways to achieve whatever you are using Select for.

In your case, and regarding only the specific error/question raised, delete the error-causing line and the next line (Selection.Copy) and replace with this:

With Sheets("Unit2Data")
    .Range(.Cells(i, 1), .Cells(i, 73)).Copy
End With

Rewriting your entire code to avoid using Select:

Sub Unit2Data()
Dim lrow As Long, i As Long, x As Date, y As Date, erow As Long

x = TxtDateStart
y = TxtDateEnd

With Sheets("Unit2Data")
    lrow = .Range("A" & .Rows.Count).End(xlUp).Row
    For i = 4 To lrow
        If .Cells(i, 1) * 1 >= x * 1 Then
            If .Cells(i, 1) * 1 <= y * 1 Then
                With Sheets("Graphing Sheet")
                    erow = .Cells(.Rows.Count, 1).End(xlUp).Offset(1, 0).Row
                End With
                .Range(.Cells(i, 1), .Cells(i, 73)).Copy _
                    Destination:= Sheets("Graphing Sheet").Cells(erow, 1)
            End If
        End If
    Next i
End With

End Sub
AAA
  • 3,520
  • 1
  • 15
  • 31