How do I pull data from multiple files and copy into one file

I used the macro copy function and created the following:

Workbooks.Open ("C:\2001.xls")
Range("A2").Select
Range(Selection, Selection.End(xlDown)).Select
Range(Selection, Selection.End(xlToRight)).Copy
Windows("Macro_try2").Activate
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveCell.Offset(columnOffset:=-1).Activate
ActiveCell.FormulaR1C1 = "'2001"
ActiveCell.Offset(columnOffset:=1).Activate
Range(Selection, Selection.End(xlDown)).Select
Windows("2001.XLS").Activate
ActiveWorkbook.Close
Windows("Macro_try2.XLS").Activate
Selection.End(xlDown).Select
ActiveCell.Offset(rowOffset:=1).Activate

This works great for one file, but I have a directory with over 200 files
that need the same action.

How do I select the next file in the directory after finishing with one?

As you can see the file name is used in 3 different lines in the macro. How
do I go from one file to the next and update the macro with the current file
name?

I would like to be able to copy the file name down the column it is in for
all rows out of that file, but even the relative copy function pastes data
into a specific range. What code would I use to set the paste range equal to
the number of rows copied?

Any guidance is appreciated.

Bob Philips recently posted this in response to a similar question. Perhaps
you can adapt it to your situation:

Sub ProcessFiles()
Dim oFSO As Object
Dim i As Long
Dim sFolder As String
Dim fldr As Object
Dim oFolder As Object
Dim oFile As Object
Dim oFiles As Object
Dim this As Workbook
Dim iRow As Long
Dim oSh As Worksheet
Dim rng As Range

Set oFSO = CreateObject("Scripting.FileSystemObject")

Set this = ActiveWorkbook
Set oSh = ActiveSheet
sFolder = "C:\MyTest"
If sFolder <> "" Then
Set oFolder = oFSO.GetFolder(sFolder)
Set oFiles = oFolder.Files
For Each oFile In oFiles
If oFile.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open FileName:=oFile.Path
With ActiveWorkbook
Set rng = _
.Worksheets(1).Range("A337:A383") _
.SpecialCells(xlConstants)
iRow = oSh.Cells(Rows.Count, 2).End(xlUp)
If iRow <> 1 Then iRow = iRow + 1
rng.Copy Destination:=oSh.Cells(iRow, 2)
.Close SaveChanges:=False
End With
End If
Next oFile

End If ' sFolder <> ""

End Sub



--
Regards,
Tom Ogilvy

"JT" <[email protected]> wrote in message
news:[email protected]...
> I used the macro copy function and created the following:
>
> Workbooks.Open ("C:\2001.xls")
> Range("A2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Copy
> Windows("Macro_try2").Activate
> ActiveSheet.Paste
> Application.CutCopyMode = False
> ActiveCell.Offset(columnOffset:=-1).Activate
> ActiveCell.FormulaR1C1 = "'2001"
> ActiveCell.Offset(columnOffset:=1).Activate
> Range(Selection, Selection.End(xlDown)).Select
> Windows("2001.XLS").Activate
> ActiveWorkbook.Close
> Windows("Macro_try2.XLS").Activate
> Selection.End(xlDown).Select
> ActiveCell.Offset(rowOffset:=1).Activate
>
> This works great for one file, but I have a directory with over 200 files
> that need the same action.
>
> How do I select the next file in the directory after finishing with one?
>
> As you can see the file name is used in 3 different lines in the macro.
How
> do I go from one file to the next and update the macro with the current
file
> name?
>
> I would like to be able to copy the file name down the column it is in for
> all rows out of that file, but even the relative copy function pastes data
> into a specific range. What code would I use to set the paste range equal
to
> the number of rows copied?
>
> Any guidance is appreciated.
>
>

The Dir() function will list files in a directory. You use it by specifying
the path (the first time used) and then just using Dir with no argument until
it returns a zero-length string ("") - Example:

Dim MyFileName as String

MyFileName = Dir("C:\*.xls")
While MyFileName <>""
' Put your code here; use "C:\" & MyFileName wherever you need to use the
file path
Dir
Wend

As for the copy/paste, not sure if I understand what you are trying to do,
but it sounds like you want the paste range to be the same size as the copied
range??? If you copy a range and then paste into a specific cell, the size
of the pasted range should match the size of the copied range.

"JT" wrote:

> I used the macro copy function and created the following:
>
> Workbooks.Open ("C:\2001.xls")
> Range("A2").Select
> Range(Selection, Selection.End(xlDown)).Select
> Range(Selection, Selection.End(xlToRight)).Copy
> Windows("Macro_try2").Activate
> ActiveSheet.Paste
> Application.CutCopyMode = False
> ActiveCell.Offset(columnOffset:=-1).Activate
> ActiveCell.FormulaR1C1 = "'2001"
> ActiveCell.Offset(columnOffset:=1).Activate
> Range(Selection, Selection.End(xlDown)).Select
> Windows("2001.XLS").Activate
> ActiveWorkbook.Close
> Windows("Macro_try2.XLS").Activate
> Selection.End(xlDown).Select
> ActiveCell.Offset(rowOffset:=1).Activate
>
> This works great for one file, but I have a directory with over 200 files
> that need the same action.
>
> How do I select the next file in the directory after finishing with one?
>
> As you can see the file name is used in 3 different lines in the macro. How
> do I go from one file to the next and update the macro with the current file
> name?
>
> I would like to be able to copy the file name down the column it is in for
> all rows out of that file, but even the relative copy function pastes data
> into a specific range. What code would I use to set the paste range equal to
> the number of rows copied?
>
> Any guidance is appreciated.
>
>