Error 1004 very puzzling indeeed

[mark Stephens]

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]

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???