Thanks, Greg. That's exactly what I was looking for.

Steve Mackay

"Greg Koppel" wrote in message
...
Use the following

Range("A65536").End(xlUp).Offset(1, 0).Select


"Steve Mackay" wrote in message
om...
Hi All

I am very novice at VBA. I only know how to record macros and then
modify them to do what I need. Using Excel 2002.

I am trying to combine data from multiple (about 100) workbooks into
one vertical list in a different workbook. Each workbook has a sheet
named "IO" with four values I want to copy (cells B2:E2) plus the name
of the workbook in cell A1. The resulting list would look like this:

A B C D E
1 WorkbookName1 Value1 Value2 Value3 Value4

2 WorkbookName2 Value1 Value2 Value3 Value4

3 etc...

I've copied someone's macro that will cycle through the workbooks in a
directory and copy the cells I need. The only thing that I can't do
is figure out how to paste it in the next empty row. I am sure this
is an easy question, just not sure of the code. Here is what I have
so far:

Sub RegionList()

Dim FileList() As String
Dim Counter As Long
Dim NextFile As String
Dim thisfile As String
Dim DirToSearch As String

DirToSearch = "C:\Documents and Settings\User1\My Documents\CBAs\"
Counter = 0

NextFile = Dir(DirToSearch & "\" & "*.xls")

Do Until NextFile = ""
ReDim Preserve FileList(Counter)
FileList(Counter) = DirToSearch & "\" & NextFile
Counter = Counter + 1
NextFile = Dir()
Loop
Application.Calculation = xlManual
On Error Resume Next
For Counter = LBound(FileList) To UBound(FileList)
'MsgBox FileList(Counter)
Workbooks.Open Filename:=FileList(Counter)
ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Co unt).Name = thisfile
Windows(thisfile).Activate
Sheets("IO").Select
Range("A1").Select
Selection.Copy

Windows("NewBook.xls").Activate 'this is open to the correct sheet
Range("A1").Select
'this is where I need it to select the next blank row in column A and
paste

Windows(thisfile).Activate
Sheets("IO").Select
Range("B2:E2").Select
Selection.Copy

Windows("NewBook.xls").Activate
Range("B1").Select
'this is where I need it to select the cell in column B next to the
workbook name that I just pasted

Windows(thisfile).Activate
ActiveWorkbook.Save
Workbooks(thisfile).Close
Next
Application.Calculation = xlAutomatic

End Sub