Report

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

Ask a question kgan4 4Posts Sunday November 27, 2016Registration date November 29, 2016 Last seen - Last answered on 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 
Helpful
+0
moins plus
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 4Posts 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!
Reply
Add comment
Helpful
+0
moins plus
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
yg_be 842Posts lundi 9 juin 2008Registration date December 8, 2016 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).
Reply
kgan4 4Posts Sunday November 27, 2016Registration date November 29, 2016 Last seen - Nov 29, 2016 at 04:51 PM
Thanks for the tip!
Reply
kgan4 4Posts 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!
Reply
yg_be 842Posts lundi 9 juin 2008Registration date December 8, 2016 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?
Reply
Add comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!