Merging a Range from Multiple Workbooks by Column based on sheet name

[louisjeorge75]

hi ,

i have several workbook in a folder and i want to merge the column a:b from all the workbook with the sheet name as "Acclass".

 

[ravishankar]

Hi
Paste the following codes in the macro window (Alt F11). and save the workbook in the same folder you wish to extract data from.

Sub pull_data()
Dim z  As Long, e As Long, g As Long
Dim f As String
Sheets("Sheet1").Select
Cells(1, 1) = "=cell(""filename"")"
Cells(1, 2) = "=left(A1,find(""["",A1)-1)"
Cells(2, 1).Select
f = Dir(Cells(1, 2) & "*.xls")
Do While Len(f) > 0
ActiveCell.Formula = f
ActiveCell.Offset(2, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z Step 2
Cells(e, 2) = "column A"
Cells(e + 1, 2) = "column B"
For g = 1 To 25
If Cells(e, 1) <> ActiveWorkbook.Name Then
Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Acclass'!A" & g
Cells(e, g + 2) = Cells(1, 3)
Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]Acclass'!B" & g
Cells(e + 1, g + 2) = Cells(1, 3)
End If
Next g
Next e
MsgBox "collating is complete."
End Sub

run the macro.it pulls data from closed workbook and extracts upto 25 rows.
Ravi