VBA Copy certain data to specific columns

[Yugo101]

Currently on my workbook, i have a VBA set up so i press a button opens up file explorer i click an excel file and copies all the data, but next step i want to do is only copy rows where one column has a certain text.

Code	Type	Name	Date	Time	Rubbish	Comments	Rubbish
123314	AB	John	01/01/01	12134568	asdasdasd	a a a a a a	etc
411354	CD	Julie	01/01/01	12134568	asdasdasd	a aa a	etc
1464988	AB	Jack	01/01/01	12134568	asdasdasd	bbbbb	etc

Above is the sort of lay out of the sheet i would select when doing file explorer. So Ideal is only copy rows where type AB is present and Copy Code (A) to A on new sheet, Type (B) to B, but Name(c) to E, Date (D) to F, Time (f) to G and Comments (G) to I. Best example since not at work. So basically need to copy rows where only AB is present then Copy the data required to Certain columns.

 

[mohadin]

Hi
Try this as a sample VBA to copy for the data you showed form sheet1 to sheet2 as a start

Sub test()
    Dim a
    Dim i&, k&
    a = Sheets("sheet1").Cells(1).CurrentRegion
    With Sheets("sheet2")
        For i = 2 To UBound(a)
            If a(i, 2) = "AB" Then
                .Cells(k + 1, 1).Resize(, 9) = Application.IfError(Application.Index(a, i, Array(1, 2, "", "", 3, 4, 5, "", 7)), "")
                k = k + 1
            End If
        Next
    End With
End Sub