Hi,

I am performing some simple collation. The code opens workbooks in a folder
in turn, counts the number of rows in the open book and then copies them
into the main book (with the macro) starting at the next row thus creating a
master list of names from all the other books.

Here's the puzzling problem (sure it's obvious but I've tried everything and
the solution eludes me).


Private Sub CommandButton2_Click()

'Opens Workbook to be tested placed in folder
Call OpenAllWorkbooks("C:\Cleaned\")

End Sub



Public Sub OpenAllWorkbooks(sFolder As String)
Dim sFile As String
Dim s As String
Dim a As String

Dim iLastRow As Integer
Dim iPasteRow As Integer

sFile = Dir(sFolder & "*.xls")

Do While sFile < ""

Workbooks.Open sFolder & sFile

'CountRowsInSourceSheet (newly opened book will always be no 2
since after running it is closed
iLastRow = Workbooks(2).Sheets(1).Cells(Rows.Count,
"A").End(xlUp).Row

'CountRowsInDestinationSheet
iPasteRow = Workbooks(1).Sheets("MasterList").Cells(Rows.Count ,
"A").End(xlUp).Row + 1


etc etc



The strange thing is that the second count works perfectly (iPasteRow is
correct) yet the first jut refuses to behave.

Any help much appreciated, regards, Mark