Formula for Copying Specific Data to a New Sheet

[RiRi ChickenPie]

Hello world,

I would like to create a formula that copies all the data from rows which contain "00" in the last two digits of column L, to a new sheet (such as the cells highlighted in yellow). I know I could sort column L by "00" in the last two digits and then copy/paste, but I am trying to learn some shortcuts to streamline the process across multiple products. I hope this description was concise enough. Thank you for any help!

-RiRi

 

[kevin9999]

Welcome to the Board!
It's always best if you provide a copy of your sheet using the XL2BB - Excel Range to BBCode otherwise we have to create your sheet to test any code. Also, the following information is missing from your image & description: Does the data start in row 2 (with headers in row 1); what's the name of the sheet; do you want to copy the filtered data to another sheet in the same workbook, and if so - to what row; how many columns do you want copied (the image only shows to column P - is that the last column); what about the second instance of 68B00 that you didn't highlight in yellow; do you want to exclude any values that end with 3 or more zeroes; is the data in any column the result of formulas or is it all constant values; etc?

Having said that, the following demonstrates one way of achieving what I think you want - just change the sheet names to suit. Try it on a copy of your data & let me know how you go

Option Explicit
Sub CopyLast2_00()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")          '<~~ *** Change to actual sheet names ***
    Set ws2 = Worksheets("Sheet2")
    Dim a, i As Long, r As Range
    a = ws1.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
    
    For i = LBound(a, 1) To UBound(a, 1)
        If Right(a(i, 1), 2) = "00" And Mid(a(i, 1), Len(a(i, 1)) - 2, 1) <> "0" Then
            If r Is Nothing Then
                Set r = ws1.Cells(i + 1, 1).EntireRow
            Else
                Set r = Union(r, ws1.Cells(i + 1, 1).EntireRow)
            End If
        End If
    Next i
    If Not r Is Nothing Then
        r.Copy ws2.Range("A" & ws2.Cells(Rows.Count, 1).End(xlUp).Row).Offset(1)
    Else
        MsgBox "No records meet the criteria"
    End If
End Sub

 

[Georgiboy]

Maybe with the filter formula:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	BO02A	A	40128	Yes	PMcB	F	23130	1316	23137	1440	169.4	11EPT	YWHG	cc	4221607160
2	BO02A	A	40128	Yes	NMCM	c	23130	1135	23130	1316	17	13KAO	YWHG	cc	231307711
3	BO02A	A	40128	Yes	PMcB	F	23121	1445	23130	1135	212.8	11EPT	YWHG	cc	4221607160
4	BO02A	A	40128	Yes	NMCM	c	23121	1040	23121	1445	41	68800	YWHG	cc	230958128
5	BO02A	A	40128	Yes	PMcB	F	23121	951	23121	1040	8	46K00	YWHG	cc	4221607160
6	BO02A	A	40328	Yes	NMCM	D	23135	118	23139	1133	106.3	11AA0	YWHG	cc	231340001
7	BO02A	A	40328	Yes	PMcM	G	23131	1932	23135	118	778	11FVC	YWHG	cc	4231317169
8
9	The below would be on another sheet
10	BO02A	A	40128	Yes	NMCM	c	23121	1040	23121	1445	41	68800	YWHG	cc	230958128
11	BO02A	A	40128	Yes	PMcB	F	23121	951	23121	1040	8	46K00	YWHG	cc	4221607160
Sheet1

Cell Formulas
Range	Formula
A10:O11	A10	=FILTER(Sheet1!A1:O7,RIGHT(Sheet1!L1:L7,2)="00")
Dynamic array formulas.

 

[RiRi ChickenPie]

Welcome to the Board!
It's always best if you provide a copy of your sheet using the XL2BB - Excel Range to BBCode otherwise we have to create your sheet to test any code. Also, the following information is missing from your image & description: Does the data start in row 2 (with headers in row 1); what's the name of the sheet; do you want to copy the filtered data to another sheet in the same workbook, and if so - to what row; how many columns do you want copied (the image only shows to column P - is that the last column); what about the second instance of 68B00 that you didn't highlight in yellow; do you want to exclude any values that end with 3 or more zeroes; is the data in any column the result of formulas or is it all constant values; etc?

Having said that, the following demonstrates one way of achieving what I think you want - just change the sheet names to suit. Try it on a copy of your data & let me know how you go

Option Explicit
Sub CopyLast2_00()
    Dim ws1 As Worksheet, ws2 As Worksheet
    Set ws1 = Worksheets("Sheet1")          '<~~ *** Change to actual sheet names ***
    Set ws2 = Worksheets("Sheet2")
    Dim a, i As Long, r As Range
    a = ws1.Range("L2:L" & Cells(Rows.Count, "L").End(xlUp).Row)
   
    For i = LBound(a, 1) To UBound(a, 1)
        If Right(a(i, 1), 2) = "00" And Mid(a(i, 1), Len(a(i, 1)) - 2, 1) <> "0" Then
            If r Is Nothing Then
                Set r = ws1.Cells(i + 1, 1).EntireRow
            Else
                Set r = Union(r, ws1.Cells(i + 1, 1).EntireRow)
            End If
        End If
    Next i
    If Not r Is Nothing Then
        r.Copy ws2.Range("A" & ws2.Cells(Rows.Count, 1).End(xlUp).Row).Offset(1)
    Else
        MsgBox "No records meet the criteria"
    End If
End Sub

Thanks a ton for your reply! Very informative. I'll be sure to improve my posts in the future. Spending a little time this morning seeing if your formula works!