Copy/Paste Account Names Starting With "A"

[wayneshirley]

Hi There,

I need to add some code to my macro that will recognise account names in column C (sheet 1) that start with the letter "A" and copy those rows into sheet 2 (starting at cell A2.)

Can anyone help me please?

Thank you.

 

[SteveO59L]

Why do you need code ?

 

[SteveO59L]

Application.ScreenUpdating = False
Dim c As Range, rng
Dim Acc_Name As String
Set rng = Range("c3:c10")
For Each c In rng
If Left(c, 1) = "A" Then
Acc_Name = c
Sheets("Sheet2").Select

LastRow = Range("a" & Rows.Count).End(xlUp).Row

Range("a" & LastRow).Select

Selection.Offset(1, 0).Select

Selection = Acc_Name
Acc_Name = ""
Sheets("Sheet1").Activate
End If
Next c

 

[Peter_SSs]

Hi There,

I need to add some code to my macro that will recognise account names in column C (sheet 1) that start with the letter "A" and copy those rows into sheet 2 (starting at cell A2.)

Can anyone help me please?

Thank you.

Unless your dataset is fairly large, you may not see the difference, but this should be a bit faster than looping through each row.

Also, I may be wrong but I think you are saying that you want the whole row copied, not just the account code? If not, just remove .EntireRow from the code below.

<font face=Courier New><br><SPAN style="color:#00007F">Sub</SPAN> FilterAndCopy()<br>    Application.ScreenUpdating = <SPAN style="color:#00007F">False</SPAN><br>    <SPAN style="color:#00007F">With</SPAN> Sheets("sheet 1")<br>        <SPAN style="color:#00007F">With</SPAN> .Range("C1", .Range("C" & Rows.Count).End(xlUp))<br>            .AutoFilter Field:=1, Criteria1:="=A*"<br>                .Offset(1).SpecialCells(xlCellTypeVisible).EntireRow.Copy _<br>                    Destination:=Sheets("sheet 2").Range("A2")<br>            .AutoFilter<br>        <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>    Application.ScreenUpdating = <SPAN style="color:#00007F">True</SPAN><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN><br></FONT>