VBA Help - Copying Rows into a new sheet, based on 3 Characters [Closed]

Report
Posts
4
Registration date
Sunday November 27, 2016
Status
Member
Last seen
November 29, 2016
-
Posts
15063
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
April 10, 2021
-
Hello Friends,

I am looking to build a VBA Macro that extracts the entire row of information based on the first three letters of Column C (Postal). For instance, if the postal code begins with "V6Y" or "V7A", row 2 will be Copied/Extracted into Sheet2, if the postal code begins with "V3A" or "V3E" row 3 will be copied/extracted into Sheet3 and so forth.



Any help would be greatly appreciated,

Thank you kindly!

2 replies

Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
458
Hi Kgan4,

Not entirely sure. Do you only want row 2 to be copied to sheet 2 when column C starts with "V6Y" and "V7A" or do you want all rows to be copied to sheet 2 which start with "V6Y" and "V7A".

I assumed the latter, since that is the most common.

Consider the following code:
Sub RunMe()
For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    If Left(cell.Value, 3) = "V6Y" Or Left(cell.Value, 3) = "V7A" Then
        cell.EntireRow.Copy _
        Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    ElseIf Left(cell.Value, 3) = "V3A" Or Left(cell.Value, 3) = "V3E" Then
        cell.EntireRow.Copy _
        Sheets("Sheet3").Range("A" & Rows.Count).End(xlUp).Offset(1, 0)
    '... and so forth
    End If
Next cell
End Sub


Best regards,
Trowa
Posts
4
Registration date
Sunday November 27, 2016
Status
Member
Last seen
November 29, 2016

Thanks Trowa!

Currently I have a table with 300 rows and Im basically looking to copy all rows that contain specified postal codes. For instance, all rows with the Postal Code (in column C) beginning with "V6Y" and "V7A" will be copied into Sheet2.

Based on the code you provided only one row in my data set is copied into the Sheet 2.

I've attempted to manipulate the code but have been pretty unsuccessful. Apologies for the lack of clarity in my earlier post.

Thanks for your feedback in advance!
Posts
2717
Registration date
Sunday September 12, 2010
Status
Moderator
Last seen
April 1, 2021
458
Hi Kgan4,

That would mean column A isn't always filled with data (contrary to your sample data).

Try this one:
Sub RunMe()
For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)
    If Left(cell.Value, 3) = "V6Y" Or Left(cell.Value, 3) = "V7A" Then
        cell.EntireRow.Copy _
        Sheets("Sheet2").Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
    ElseIf Left(cell.Value, 3) = "V3A" Or Left(cell.Value, 3) = "V3E" Then
        cell.EntireRow.Copy _
        Sheets("Sheet3").Range("C" & Rows.Count).End(xlUp).Offset(1, -2)
    '... and so forth
    End If
Next cell
End Sub


The code now looks at column C to determine the last available row, but still pastes the row in column A.

Does this work for you?

Best regards,
Trowa
Monday, Tuesday and Thursday are usually the days I'll respond. Bear this in mind when awaiting a reply.
Posts
15063
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
April 10, 2021
4
I suggest changing
For Each cell In Range("C2:C" & Range("C" & Rows.Count).End(xlUp).Row)

into
For Each cell In Range("C2:C" & Range("A" & Rows.Count).End(xlUp).Row)

in the first code provided by Trowa (https://ccm.net/forum/affich-942730-vba-help-copying-rows-into-a-new-sheet-based-on-3-characters#1).
Posts
4
Registration date
Sunday November 27, 2016
Status
Member
Last seen
November 29, 2016
>
Posts
15063
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
April 10, 2021

Thanks for the tip!
Posts
4
Registration date
Sunday November 27, 2016
Status
Member
Last seen
November 29, 2016

This is great, thanks!

Alternatively, how would I create a code to copy a row if it does not meet the criteria. For instance, if Column C does not start with "V6Y" or "V7A" the entire row will be copied to Sheet4?

My other question is how would I create a code to create a new worksheet and name it "W1" then, having all rows that start with "V6Y" in Column C copied into this newly created worksheet.

Your response is greatly appreciated!
Posts
15063
Registration date
Sunday June 8, 2008
Status
Contributor
Last seen
April 10, 2021
4
Are you looking to build something, or are you asking the forum to build it for you?

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!