Error 1004 very puzzling indeeed
 

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

Error 1004 very puzzling indeeed
 

First, I wouldn't use "As Integer", I'd use "as long". They hold bigger
numbers.

And I'm not sure what's going wrong, but I'd try:

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

dim wkbk as workbook
dim mstrWkbk as workbook

Dim iLastRow As Long
Dim iPasteRow As Long

set mstrwkbk = activeworkbook

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

Do While sFile < ""

set wkbk = Workbooks.Open(filename:=sFolder & sFile)

'CountRowsInSourceSheet
with wkbk.sheets(1)
iLastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
end with

'CountRowsInDestinationSheet
with mstrwkbk.sheets("masterlist")
iPasteRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
end with
...


wkbk.close savechanges:=false 'or true???






mark Stephens wrote:

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

--

Dave Peterson