Combining workbooks

[Guest]

I have approx 100 workbooks. All contain the standard 3 worksheets but only
the first worksheet has the data I require (other two sheets are empty). Each
worksheet is of the same format. i.e. column headings are the same. There are
7 columns of data in each worksheet/workbook.
The data always starts at cell A13 (cells/rows above are just headings and
unwanted information)

I want to find a simple way to combine the data into one master
workbook/worksheet without me having to open each one and cut n paste the
data manually to one worksheet.

I do not understand visual basic so if thats the answer I'm stuffed - but
otherwise - is there a simple solution.

Thanks

Ron

 

[Guest]

Hi
the solution would be VBA. You may check:
http://www.rondebruin.nl/copy3.htm

 

[Bob Phillips]

Ron,

Here is some VBA but it is straightforward. Just copy this code to a general
code module and run it

Sub SubGetMyData()

Dim objFSO As Object
Dim objFolder As Object
Dim objSubfolder As Object
Dim objFile As Object
Dim oWb As Workbook
Dim oWs As Worksheet
Dim cLastRow As Long
Dim iRow As Long

iRow = 1
Set oWb = Workbooks.Add
Set oWs = oWb.ActiveSheet
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFolder = objFSO.GetFolder("c:\MyTest\")
For Each objFile In objFolder.Files
If objFile.Type = "Microsoft Excel Worksheet" Then

Workbooks.Open Filename:=objFolder.Path & "\" & objFile.Name
With ActiveWorkbook.ActiveSheet
cLastRow = .Cells(Rows.Count, "A").End(xlUp).Row
.Range("A1").Resize(cLastRow, 99).Copy _
Destination:=oWs.Cells(iRow, "A")
End With
ActiveWorkbook.Close savechanges:=False
iRow = iRow + cLastRow
End If
Next
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)