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

kgan4 4 Posts Sunday November 27, 2016Registration date November 29, 2016 Last seen - Nov 27, 2016 at 08:38 PM - Latest reply: yg_be 5215 Posts Sunday June 8, 2008Registration dateContributorStatus April 18, 2018 Last seen
- Nov 29, 2016 at 05:05 PM
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!

See more 

7 replies

TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen - Nov 28, 2016 at 11:24 AM
0
Helpful
1
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
kgan4 4 Posts Sunday November 27, 2016Registration date November 29, 2016 Last seen - Nov 28, 2016 at 03:27 PM
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!
TrowaD 2340 Posts Sunday September 12, 2010Registration dateModeratorStatus April 19, 2018 Last seen - Updated by TrowaD on 29/11/16 at 12:04 PM
0
Helpful
4
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.
yg_be 5215 Posts Sunday June 8, 2008Registration dateContributorStatus April 18, 2018 Last seen - Nov 29, 2016 at 01:08 PM
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 (http://ccm.net/forum/affich-942730-vba-help-copying-rows-into-a-new-sheet-based-on-3-characters#1).
kgan4 4 Posts Sunday November 27, 2016Registration date November 29, 2016 Last seen > yg_be 5215 Posts Sunday June 8, 2008Registration dateContributorStatus April 18, 2018 Last seen - Nov 29, 2016 at 04:51 PM
Thanks for the tip!
kgan4 4 Posts Sunday November 27, 2016Registration date November 29, 2016 Last seen - Nov 29, 2016 at 04:50 PM
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!
yg_be 5215 Posts Sunday June 8, 2008Registration dateContributorStatus April 18, 2018 Last seen - Nov 29, 2016 at 05:05 PM
Are you looking to build something, or are you asking the forum to build it for you?