This is what I have so far, which does all the copying to the new worksheet and works well:
Sub AddWB()

Dim MyActiveSheet As String

'grabs the active sheet's name in Support
MyActiveSheet = ActiveSheet.Name

'adds a new workbook
'when you add a workbook it becomes the new active workbook
Workbooks.Add

'saves the new workbook as Summary.xls in the C drive
ActiveWorkbook.SaveAs Filename:="C:\csv\output\Nper123.csv", _
FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
ReadOnlyRecommended:=False, CreateBackup:=False

'copies columns from per123.csv and pastes them
Workbooks("per123.csv"). _
Sheets(MyActiveSheet).Range _
("A:A").Copy _
Sheets(1).Range("A1")

Workbooks("per123.csv"). _
Sheets(MyActiveSheet).Range _
("C:C").Copy _
Sheets(1).Range("B1")

Workbooks("per123.csv"). _
Sheets(MyActiveSheet).Range _
("E:E").Copy _
Sheets(1).Range("L1")

Workbooks("per123.csv"). _
Sheets(MyActiveSheet).Range _
("F:F").Copy _
Sheets(1).Range("P1")

Workbooks("per123.csv"). _
Sheets(MyActiveSheet).Range _
("A1:Z6").Copy _
Sheets(1).Range("A1")

End Sub

My main problem is how to make it generic/dynamic. i.e. in the above code i have to specify both the file i am looking up and the new files name. As i will need this in bulk I need it to work dynamically, maybe using cell B1 to create a new name.
Once that is solved I need to figure out how to batch it and run en masse.

Any tips or tricks would be very helpful.

Thanks in advance