VBA copy Row range loop

[ojhawkins]

Hi all,

Basically what I am trying to do is.

What ever account in entered into "B1" find in column A where this appears and copy the Row from (A:G) and paste to "I1" next "I2"

Any help is greatly appreciated.

 

[VBAdude]

Hi, Try this:

Sub CopyAcctInfo()
 
Dim FinalRow As Long
Dim DestRow As Long
Dim MyAccount As Long
Dim i As Long
 
FinalRow = Cells(Rows.Count, 1).End(xlUp).Row
DestRow = Cells(Rows.Count, 9).End(xlUp).Row
MyAccount = Range("B1").Value
 
For i = 3 To FinalRow
 
    If Cells(i, 1).Value = MyAccount Then
 
        Range("A" & i, "G" & i).Copy Destination:=Range("I" & DestRow)
        DestRow = DestRow + 1
 
    End If
 
Next i
 
End Sub

 

[VBAdude]

This version is a little different...this code would run the same as above except, it would perform on the ActiveSheet....so if you had multiple sheets in the same workbook...this is better.

Sub CopyAcctInfo()
 
Dim FinalRow As Long
Dim DestRow As Long
Dim MyAccount As Long
Dim i As Long
Dim Ws As Worksheet
 
Set Ws = ActiveSheet
FinalRow = Ws.Cells(Rows.Count, 1).End(xlUp).Row
DestRow = Ws.Cells(Rows.Count, 9).End(xlUp).Row
MyAccount = Ws.Range("B1").Value
 
   For i = 3 To FinalRow
       If Cells(i, 1).Value = MyAccount Then
           Ws.Range("A" & i, "G" & i).Copy Destination:=Ws.Range("I" & DestRow)
           DestRow = DestRow + 1
 
        End If
 
    Next i
 
End Sub

 

[ojhawkins]

Thank you so much!!! Great help !!!!!!!!

 

[VBAdude]

Thank you so much!!! Great help !!!!!!!!

You're welcome!