I am trying to search my entire workbook, in column C starting with row 2 on each page, for a user defined
value. I'd like it to then copy the entire row and paste it to a separate sheet for all matching values. I seem to be
hitting a snag after changing my code from just doing "Sheet1" to the entire workbook. Please help trouble
Thanks in advanced!
Dim LSearchRow As Integer
Dim LCopyToRow As Integer
Dim LSearchValue As String
On Error GoTo Err_Execute
LSearchValue = InputBox("Please enter a
value to search for.", "Enter value")
'Start search in row 2
LSearchRow = 2
'Start copying data to row 2 in Sheet2 (row counter variable)
LCopyToRow = 2
For j = 1 To ActiveWorkbook.Sheets.Count
i = 1
Do While .Range("C1").Cells(i) <> ""
'If value in column E = LSearchValue, copy
entire row to Sheet2
If UCase(LSearchValue) = UCase(.Range("C1").Cells(i)) Then
'Select row in Sheet1 to copy
Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
'Paste row into Sheet2 in next row
Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
'Move counter to next row
LCopyToRow = LCopyToRow + 1
i = i + 1
LSearchRow = LSearchRow + 1
'Position on cell A3
Application.CutCopyMode = False
MsgBox "All matching data has been copied."
MsgBox "An error occurred."