Macro to look up Request Number and copy to new cell

[razor22]

I am trying to use a macro that will look up "RequestNumber" from Col A, copy all information on that Row (B:F), and paste it into a new sheet. The code used to use this same exact concept with Date (instead of RequestNumber). :

Sub CopyDateRows()
Dim myDate As String
Dim lr As Long

myDate = InputBox("Enter Date")
If IsDate(myDate) Then
Application.ScreenUpdating = False
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
lr = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range("A1:A" & lr)
.AutoFilter Field:=1, Criteria1:= _
"<>" & CLng(DateValue(myDate))
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End With
Application.ScreenUpdating = True
Else
MsgBox "No valid date entered"
End If
End Sub

Here is what I have so far to use on my book. I am a beginner so please bear with me. Any help is greatly appreciated. I will be online for quick replys.

Sub CopyNumberRows()
Dim myRequestNumber As String
Dim lr As Long

myRequestNumber = InputBox("Enter Request Number")
If IsRequestNumber(myRequestNumber) Then
Application.ScreenUpdating = False
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
lr = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range("A1:A" & lr)
.AutoFilter Field:=1, Criteria1:= _
"<>" & CLng(DateValue(myRequestNumber))
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End With
Application.ScreenUpdating = True
Else
MsgBox "No valid request number entered"
End If
End Sub

 

[tweedle]

We'll assume that IsRequestNumber() is a function that returns true/false depending whether the value is found or not.

Sub CopyNumberRows()
Dim myRequestNumber As String
Dim lr As Long
 
myRequestNumber = InputBox("Enter Request Number")
If IsRequestNumber(myRequestNumber) Then
Application.ScreenUpdating = False
ActiveSheet.Copy After:=ActiveSheet
With ActiveSheet
lr = .Range("A" & .Rows.Count).End(xlUp).Row
With .Range("A1:[COLOR=blue][B]A[/B][/COLOR]" & lr) ' [COLOR=blue]May need to extend this column label [/COLOR]
'.AutoFilter Field:=1, Criteria1:= _
'"<>" & CLng(DateValue(myRequestNumber))
 
[COLOR=blue]'Make sure this indicates the column that contains the RequestNumber within the data list[/COLOR]
.AutoFilter Field:=[COLOR=blue]1[/COLOR], Criteria1:= _
"<>" & myRequestNumber [COLOR=blue]'Stripped off date conversion functions[/COLOR]
 
.Offset(1).EntireRow.Delete
.AutoFilter
End With
End With
Application.ScreenUpdating = True
Else
MsgBox "No valid request number entered"
End If
End Sub

 

[razor22]

Thank you for your response Tweedle.

That is exactly what I want to do. Return True/False if the value is found or not and then copy it to new sheet.

When I am running the macro I am getting an error: Can't Execute in Break Mode and then it is going back to the code. Can you please help me with this?

Thank you very much for you time and help

 

[tweedle]

I'm not precisely familiar with that error.
The below example works for the functionality you're looking for.

Sub CopyNumberRows()
    Dim myRequestNumber As String
    Dim lr As Long
    myRequestNumber = InputBox("Enter Request Number")
    If IsRequestNumber(myRequestNumber) Then
        Application.ScreenUpdating = False
        ActiveSheet.Copy After:=ActiveSheet
        With ActiveSheet
            lr = .Range("A" & .Rows.Count).End(xlUp).Row
            With .Range("A1:[COLOR=blue]H[/COLOR]" & lr)
                [COLOR=blue]'Make sure this indicates the column that contains the RequestNumber within the data list[/COLOR]
[COLOR=blue]               'REQ NUM (2) in example[/COLOR]
                .AutoFilter Field:=[COLOR=blue]2[/COLOR], Criteria1:="<>" & myRequestNumber
                .Offset(1).EntireRow.Delete
                .AutoFilter
            End With
        End With
        Application.ScreenUpdating = True
    Else
        MsgBox "No valid request number entered"
    End If
End Sub

Excel Workbook
	A	B	C	D	E	F	G	H
1	DATE	REQ NUM	DATA 1	DATA 2	DATA 3	DATA 4	DATA 5	DATA 6
2	8/3/2011	1234	6	8	10	12	14	16
3	8/3/2011	1290	9	12	15	18	21	24
4	8/3/2011	1346	12	16	20	24	28	32
5	8/3/2011	1402	15	20	25	30	35	40
6	8/3/2011	1458	18	24	30	36	42	48
7	8/3/2011	1514	21	28	35	42	49	56
8	8/4/2011	1570	24	32	40	48	56	64
9	8/4/2011	1514	27	36	45	54	63	72
10	8/4/2011	1458	30	40	50	60	70	80
11	8/4/2011	1402	33	44	55	66	77	88
12	8/4/2011	1346	36	48	60	72	84	96
13	8/4/2011	1290	39	52	65	78	91	104
14	8/4/2011	1234	42	56	70	84	98	112
15	8/4/2011	1178	45	60	75	90	105	120
16	8/4/2011	1122	48	64	80	96	112	128
Before Code

Excel Workbook
	A	B	C	D	E	F	G	H
1	DATE	REQ NUM	DATA 1	DATA 2	DATA 3	DATA 4	DATA 5	DATA 6
2	8/3/2011	1290	9	12	15	18	21	24
3	8/4/2011	1290	39	52	65	78	91	104
4
After Code

This is my version of the IsRequestNumber function:

Function IsRequestNumber(ReqNum)
    Dim rng As Range
    IsRequestNumber = False
 
    Set rng = Cells.Find(What:=ReqNum, After:=Range("A1"), LookIn:=xlValues, LookAt _
    :=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlAll, MatchCase:= _
    False, SearchFormat:=False)
 
    If Not rng Is Nothing Then
        IsRequestNumber = True
    End If
End Function