Fixing Macro to work in active sheets

[PWilson0727]

I am trying to fix one of my macros so that it works in active sheet. It used to have to be changed for each sheet I wanted to run it on but now I am getting runtime errors saying Compile Error: Argument not optional. I have highlighted the 3 places that I switched it to be looking for the active workbook. I have attached the macro below and bold/underline the sections causing the error - Any suggestions?



___________________________________________
Sub CopyData()


Dim vSearch As Variant

Dim i As Long
Dim k As Long
Dim lRowToCopy As Long

'Tells user to enter a search phrase
vSearch = InputBox("Search")
'makes sure to search from 1st row
i = 1
'macro will loop until it finds a row with no records
'if the macro finds a row with no records it quits the loop
Do Until WorksheetFunction.CountA.ActiveWorkbook.ActiveSheet.Rows(i) = 0

'here I let the macro to continue its run despite a possible errors (explanation below)
On Error Resume Next
lRowToCopy = 0
'if Find method finds no value VBA returns an error, this is why I allowed macro to run despite that. In case of error variable lRowToCopy keeps 0 value
'if Find method finds a searched value it assigns the row number to var lRowToCopy
lRowToCopy = ActiveWorkbook.ActiveSheet.Rows(i).Find(What:=vSearch, LookIn:=xlValues, LookAt:=xlPart, SearchOrder:=xlByRows).Row
'here we allow macro to disiplay error messages
On Error GoTo 0

'if var lRowToCopy does not equal to 0 that means a row with a searched value has been found
If lRowToCopy > 0 Then

'this loop looks for the first blank row in 2nd sheet, I also used COUNTA to find absolutely empty row
For k = 1 To Sheets("ToCopy").Rows.Count

'when the row is found, the macro performs copy-paste operation
If WorksheetFunction.CountA(Sheets("ToCopy").Rows(k)) = 0 Then

ActiveWorkbook.ActiveSheet.Rows(i).Copy
Sheets("ToCopy").Select
Rows(k).Select
ActiveSheet.Paste
'do not forget to exit for loop as it will fill all empty rows in 2nd sheet
Exit For

End If
Next k

End If

i = i + 1
Loop

End Sub

[Brendan_Floyde]

try adding brackets as per below:

Please Login or Register  to view this content.

[Norie]

You are changing the active sheet here.

Please Login or Register  to view this content.

Try setting a reference to the active sheet at the start of the code.

Please Login or Register  to view this content.