Help editing macro

[jdev33]

Sub Test3()
    Dim LSearchRow As Integer
    Dim LCopyToRow As Integer
    On Error GoTo Err_Execute
    'Start search in row 5
    LSearchRow = 5
    'Start copying data to row 2 in Sheet3 (row counter variable)
    LCopyToRow = 2
    While Len(Range("Y" & CStr(LSearchRow)).Value) > 0
        'If value in column Y = "84312570", copy entire row to Sheet3
        If Range("Y" & CStr(LSearchRow)).Value = "84312570" Then
            'Select row in MasterSheet to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            'Paste row into Sheet3 in next row
            Sheets("Sheet3").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
            'Go back to MasterSheet to continue searching
            Sheets("MasterSheet").Select
        End If
        LSearchRow = LSearchRow + 1
    Wend
    'Position on cell A5
    Application.CutCopyMode = False
    Range("A5").Select
    MsgBox "All matching data has been copied."
    Exit Sub
Err_Execute:
    MsgBox "An error occurred."
End Sub

This macro is in relation to a question I have posted earlier about pulling rows of information based off of an entry in one column. Those posts are located:
http://www.mrexcel.com/forum/showthread.php?t=577162
http://www.mrexcel.com/forum/showthread.php?t=577132

So this macro is doing basically what I want, but I would like to refine it if possible.

First, I would like to find a way to pull from Column Y based off of the last 4 numbers for the value (in the macro above pull everything ending in 2570, ignoring the first 4 digits); can I change the format in the existing language to accomplish this or is there a way in excel to automatically "x" out the first four numbers for the values in Column Y?

Second, is there a way to alter the macro so that instead of returning an entire row that matches the Column Y value I can pull specific columns from the rows matching the Column Y figure?


Thanks in advance for any advice.

 

[texasalynn]

to your first question I made a few changes that should work

Sub Test3()
    Dim LSearchRow As Long
    Dim LCopyToRow As Long
    On Error GoTo Err_Execute
    'Start search in row 5
    LSearchRow = 5
    'Start copying data to row 2 in Sheet3 (row counter variable)
    LCopyToRow = 2
    While Len(Range("Y" & CStr(LSearchRow)).Value) > 0
        'If value in column Y ends with = "2570", copy entire row to Sheet3
        If Right(Range("Y" & CStr(LSearchRow)).Value, 4) = "2570" Then
            'Select row in MasterSheet to copy
            Rows(CStr(LSearchRow) & ":" & CStr(LSearchRow)).Select
            Selection.Copy
            'Paste row into Sheet3 in next row
            Sheets("Sheet3").Select
            Rows(CStr(LCopyToRow) & ":" & CStr(LCopyToRow)).Select
            ActiveSheet.Paste
            'Move counter to next row
            LCopyToRow = LCopyToRow + 1
            'Go back to MasterSheet to continue searching
            Sheets("MasterSheet").Select
        End If
        LSearchRow = LSearchRow + 1
    Wend
    'Position on cell A5
    Application.CutCopyMode = False
    Range("A5").Select
    MsgBox "All matching data has been copied."
    Exit Sub
Err_Execute:
    MsgBox "An error occurred."
End Sub