Home |
Search |
Today's Posts |
#1
|
|||
|
|||
Combining workbooks
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 |
#2
|
|||
|
|||
Hi
the solution would be VBA. You may check: http://www.rondebruin.nl/copy3.htm "RC" wrote: 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 |
#3
|
|||
|
|||
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) "RC" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do i remove workbooks no longer required | Excel Discussion (Misc queries) | |||
Workbooks...I'll try this again... | Excel Discussion (Misc queries) | |||
Workbooks... | Excel Discussion (Misc queries) | |||
References to open/hidden workbooks become hard-coded in formulas - 2003 | Excel Discussion (Misc queries) | |||
Summing Values from different workbooks | Excel Discussion (Misc queries) |