I need to extract a range of cells from 16 excel files and paste the
"values" into 1 file.
My extraction range is J26..L80 on a tab called "Linked Data".
My data files are labeled "anything Load 1.xls" where the name stays
the same but I increment the value 1 thru 16.

So I was hoping to have some way of opening the file open window,
picking the first file in the series and the macro would then loop thru
all 16 files and paste the data in to a separate temporary file.

My temp file would have these 3 columns of data then an empty column
then the next file would be opened and the "values" inserted next to it
similar to the following:

x x x x x x x x x x x x
x x x x x x x x x x x x
x x x x x x x x x x x x
x x x x x x x x x x x x

gtslabs shared this with us in microsoft.public.excel.programming:

I need to extract a range of cells from 16 excel files and paste the
"values" into 1 file.
My extraction range is J26..L80 on a tab called "Linked Data".
My data files are labeled "anything Load 1.xls" where the name stays
the same but I increment the value 1 thru 16.

So I was hoping to have some way of opening the file open window,
picking the first file in the series and the macro would then loop
thru all 16 files and paste the data in to a separate temporary file.

My temp file would have these 3 columns of data then an empty column
then the next file would be opened and the "values" inserted next to
it similar to the following:

x x x x x x x x x x x x
x x x x x x x x x x x x
x x x x x x x x x x x x
x x x x x x x x x x x x

Extracting data from Excel files can even be done without opening them!
http://www.rondebruin.nl/ado.htm#files

--
Amedee Van Gasse using XanaNews 1.17.4.1

This is very close to what you want. Code was pieced together from this
newsgroup. What's different is a) you multi-select all your files at once
and the code will loop through the Array of filenames. b) It will append
each file to the end of the previous file, down a single sheet. You will
need to modify it to count columns instead of rows to put the data across
the sheet. This is a good start with the basics of what you want.

Sub DAC_Report()
Dim basebook As Workbook
Dim mybook As Workbook
Dim sourceRange As Range
Dim destrange As Range
Dim SourceRcount As Long
Dim N As Long
Dim rnum As Long
Dim MyPath As String
Dim SaveDriveDir As String
Dim FName As Variant

SaveDriveDir = CurDir
'MyPath = "C:\Data"
'ChDrive MyPath
'ChDir MyPath
FName = Application.GetOpenFilename(filefilter:="Excel Files (*.xls),
*.xls", _
MultiSelect:=True)
If IsArray(FName) Then
Application.ScreenUpdating = False
Set basebook = ThisWorkbook
rnum = 1
basebook.Worksheets(1).Cells.Clear
'clear all cells on the first sheet

For N = LBound(FName) To UBound(FName)
Set mybook = Workbooks.Open(FName(N))
Set sourceRange = mybook.Worksheets(1).Range("A3:F53")
SourceRcount = sourceRange.Rows.Count
Set destrange = basebook.Worksheets(1).Cells(rnum, "A")

basebook.Worksheets(1).Cells(rnum, "G").Value = mybook.Name
' This will add the workbook name in column D if you want

sourceRange.Copy destrange
' Instead of this line you can use the code below to copy only
the values

' With sourceRange
' Set destrange = basebook.Worksheets(1).Cells(rnum,
"A"). _
' Resize(.Rows.Count, .Columns.Count)
' End With
' destrange.Value = sourceRange.Value

mybook.Close False
rnum = rnum + SourceRcount
Next
End If
Columns("G:G").Font.Size = 8
Columns("G:G").Font.Bold = True
' ChDrive SaveDriveDir
' ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

Good Luck,
Mike F

"gtslabs" wrote in message
oups.com...
I need to extract a range of cells from 16 excel files and paste the
"values" into 1 file.
My extraction range is J26..L80 on a tab called "Linked Data".
My data files are labeled "anything Load 1.xls" where the name stays
the same but I increment the value 1 thru 16.

So I was hoping to have some way of opening the file open window,
picking the first file in the series and the macro would then loop thru
all 16 files and paste the data in to a separate temporary file.

My temp file would have these 3 columns of data then an empty column
then the next file would be opened and the "values" inserted next to it
similar to the following:

x x x x x x x x x x x x
x x x x x x x x x x x x
x x x x x x x x x x x x
x x x x x x x x x x x x

More examples here
http://www.rondebruin.nl/tips.htm


--
Regards Ron de Bruin
http://www.rondebruin.nl



"Amedee Van Gasse" wrote in message ...
gtslabs shared this with us in microsoft.public.excel.programming:

I need to extract a range of cells from 16 excel files and paste the
"values" into 1 file.
My extraction range is J26..L80 on a tab called "Linked Data".
My data files are labeled "anything Load 1.xls" where the name stays
the same but I increment the value 1 thru 16.

So I was hoping to have some way of opening the file open window,
picking the first file in the series and the macro would then loop
thru all 16 files and paste the data in to a separate temporary file.

My temp file would have these 3 columns of data then an empty column
then the next file would be opened and the "values" inserted next to
it similar to the following:

x x x x x x x x x x x x
x x x x x x x x x x x x
x x x x x x x x x x x x
x x x x x x x x x x x x

Extracting data from Excel files can even be done without opening them!
http://www.rondebruin.nl/ado.htm#files

--
Amedee Van Gasse using XanaNews 1.17.4.1