Previous Topic Next Topic

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

Closed
kgan4 Posts 4 Registration date Sunday November 27, 2016 Status Member Last seen November 29, 2016 - Nov 27, 2016 at 08:38 PM
yg_be Posts 23440 Registration date Sunday June 8, 2008 Status Contributor Last seen January 24, 2025 - 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!

Related:

2 responses

Answer 1 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Nov 28, 2016 at 11:24 AM
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 Posts 4 Registration date Sunday November 27, 2016 Status Member Last seen November 29, 2016
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!
Answer 2 / 2
TrowaD Posts 2921 Registration date Sunday September 12, 2010 Status Moderator Last seen December 27, 2022 555
Updated by TrowaD on 29/11/16 at 12:04 PM
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 Posts 23440 Registration date Sunday June 8, 2008 Status Contributor Last seen January 24, 2025 5
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 (https://ccm.net/forum/affich-942730-vba-help-copying-rows-into-a-new-sheet-based-on-3-characters#1).
kgan4 Posts 4 Registration date Sunday November 27, 2016 Status Member Last seen November 29, 2016 > yg_be Posts 23440 Registration date Sunday June 8, 2008 Status Contributor Last seen January 24, 2025
Nov 29, 2016 at 04:51 PM
Thanks for the tip!
kgan4 Posts 4 Registration date Sunday November 27, 2016 Status Member Last seen November 29, 2016
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 Posts 23440 Registration date Sunday June 8, 2008 Status Contributor Last seen January 24, 2025 5
Nov 29, 2016 at 05:05 PM
Are you looking to build something, or are you asking the forum to build it for you?