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

Question

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!
Configuration: Windows / Chrome 54.0.2840.99

TrowaD · Answer

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

TrowaD · Answer

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.

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

2 responses

Similar discussions

Subscribe To Our Newsletter!