Consolidate data from sheet name "Route sheet" from 15 workbooks

[Vijaya Kanthi]

Hello,

Good day

I have 15-20 workbooks, where in in every workbook the 2nd sheet name would be " Route sheet ". I want a Macro which can consolidate all the data from these workbooks in the same formate.

All 15-20 workbook " route sheet " formate will be same.

can any one help me in this ???

Help would be much appriciated

Regads,
K

 

[ravishankar]

try the following codes

Sub general()
Dim z As Long, e As Long, G As Long, d As Long, h As Long
Dim f As String
d = 2
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(1, 0).Select
f = Dir()
Loop
z = Cells(Rows.Count, 1).End(xlUp).Row
For e = 2 To z
If Cells(e, 1) <> ActiveWorkbook.Name Then
Cells(1, 4) = "=COUNTA('" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!A:A)"
Cells(d, 2) = Cells(e, 1)
For h = 1 To Cells(1, 4)
For G = 1 To 10
Cells(1, 3) = "='" & Cells(1, 2) & "[" & Cells(e, 1) & "]route sheet'!" & Chr(G + 64) & h
Cells(d, G + 2) = Cells(1, 3)
Next G
d = d + 1
Next h
End If
d = d + 1
Next e
MsgBox "collating is complete."
End Sub

It may pull only one line of data. may be opening each file and say .usedrange.copy is a better choice
Ravi shankar

 

[Vijaya Kanthi]

Hello,

Thanks for the macro...

however i have few issues with output.....

all the 15 spread sheet are not consolidating in this... it is getting in few wrrokbooks and the indicatior is at
Cells(1, 4) = "=COUNTA('" & Cells(1, 2) & "[" & Cells(e, 1) & "]Sheet1'!A:A)"

also, the data is conslidating is onyl for 10 colums.. i have data till 15 colums

kindly advice

thanks,
K

 

[ravishankar]

change g=1 to 10 to 1 to 15.
I will work on the other problem
ravishankar