#1   Report Post  
RC
 
Posts: n/a
Default 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   Report Post  
Frank Kabel
 
Posts: n/a
Default

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   Report Post  
Bob Phillips
 
Posts: n/a
Default

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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How do i remove workbooks no longer required ABE Excel Discussion (Misc queries) 4 December 15th 04 08:48 PM
Workbooks...I'll try this again... Markster Excel Discussion (Misc queries) 10 December 7th 04 11:12 PM
Workbooks... Markster Excel Discussion (Misc queries) 8 December 7th 04 12:24 AM
References to open/hidden workbooks become hard-coded in formulas - 2003 L Mehl Excel Discussion (Misc queries) 2 November 27th 04 10:28 PM
Summing Values from different workbooks Harlan Grove Excel Discussion (Misc queries) 0 November 26th 04 08:38 PM


All times are GMT +1. The time now is 05:29 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"