PDA

View Full Version : Combining multiple excel workbooks in to a single sheet



sundarsree
05-03-2010, 07:17 AM
Dear ol...
I need a requirement in excel VBA in which i need to combine n number of workbooks(having only one sheet each) in a particular location or folder in to a single MASTER workbook that too in an single sheet. The data should be appended in the file name order.. like 1.xls,2.xls,....etc

Is there any VBA code available for this scenario....?
Pls Help at the earlist.

lynnnow
05-03-2010, 08:10 AM
How big is the data in each sheet (rows x columns)? Which version of Excel are you using? More details buddy.

sundarsree
05-03-2010, 08:38 AM
How big is the data in each sheet (rows x columns)? Which version of Excel are you using? More details buddy.


it will come around sme 3000 lines in each workbook and i am using 2003 excel sheet format

mdmackillop
05-03-2010, 09:29 AM
If this doesn't give the correct order, let us know.

Option Explicit
Sub GetAllSheets()
Dim ws As Worksheet
Dim wb As Workbook
Dim Pth As String
Dim rng As Range
Dim Rws As Long
Dim MyFile As String

Pth = "C:\Test\"
Set ws = ActiveSheet
Rws = 1
MyFile = Dir(Pth & "*.xls")
Do Until MyFile = ""
Set wb = Workbooks.Open(Pth & MyFile)
Set rng = wb.Sheets(1).UsedRange
rng.Copy ws.Cells(Rws, 1)
Rws = Rws + rng.Rows.Count + 2
wb.Close False
ws.Cells(Rws - 1, 1).Resize(, 10).Interior.ColorIndex = 6
MyFile = Dir
Loop
End Sub