[RESOLVED] [MS Excel 2010] Consolidating all 55 workbooks into 1 new workbook

[westconn1]

does it matter what order the workbook data is entered into the consolidated book?
the order, would be the same as the 55 workbooks are opened
if so some change would be required to sort the order of opening the workbooks

Is it possible to develop a macro to do these tasks?

entry level

Dim shts(2)
Dim rw(2) As Long
mypath = "C:\Consolidation\"
Set newb = Workbooks.Add
shts = Array("Total Consolidation", "State level Consolidation", "District Level Consolidation")
'assumes new Workbook will contain 3 sheets by default, if not some sheets will have to be added
newb.Sheets(1).Name = shts(0)
newb.Sheets(2).Name = shts(1)
newb.Sheets(3).Name = shts(2)
rw(0) = 1
rw(1) = 1
rw(2) = 1
fname = Dir(mypath & "*.xls")
Do While Len(fname) > 0
  Set Wb = Workbooks.Open(mypath & fname)
  For s = 0 To 3
    rws = Wb.Sheets(shts(s)).UsedRange.Rows.Count
    cols = Wb.Sheets(shts(s)).UsedRange.Columns.Count
    newb.Sheets(shts(s)).Range("a" & rw(s)).Resize(rws, cols).value = Wb.Sheets(s).UsedRange.value
    rw(s) = rw(s) + rws
  Next
  Wb.Close False
  fname = Dir
Loop
newb.SaveAs mypath & "COUNTRY LEVEL CONSOLIDATION.xls"
newb.Close  ' or omit if you want left open

this is untested and may contain errors

nobody is going to do it for you, your going to have to learn it.

it is sunday and i am bored, but i hope you will at least study the code and make whatever modifications are necessary