It needs a sheet specified...
Workbooks(lngCount).Worksheets(1).Range("b1:b5").C opy

Same for...
Workbooks("info.xls").Worksheets(1).Range("a1").En d(xlDown).Offset(1, 0).PasteSpecial

Also...
Workbooks("info.xls") should probably be Workbooks("info") ?
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"hawki"
wrote in message
The procedure stops at the line below.
Workbooks(lngCount).Range("b1:b5").Copy
--
l-hawk


"Jim Cone" wrote:
Untested but should be close to what you want...
'--
Sub test()
Dim lngCount As Long
Dim Number As Long
Number = Workbooks.Count
For lngCount = 1 To Number
If Not Workbooks(lngCount).Name = "info" Then
Workbooks(lngCount).Range("b1:b5").Copy
Workbooks("info.xls").Range("a1").End(xlDown).Offs et(1, 0).PasteSpecial xlPasteValues
Workbooks(lngCount).Close savechanges:=False
End If
Next lngCount
Workbooks("info.xls").Close savechanges:=True
End Sub
--
Jim Cone
San Francisco, USA
http://www.realezsites.com/bus/primitivesoftware


(Excel Add-ins / Excel Programming)
"hawki"
wrote in message
I have opened a number of workbooks, "wk01" through "wkXX". I have opened
another workbook, "info" , to collect information from "wk01" to "wkXX", copy
the information, paste to workbook "info" one at a time, and close each of
the "wkXX's" after the information is collected.
I must perform the procedure a number of times based on the number of
workbooks open less (subtract) 1. My workbook "info" where the informaton is
collected is closed when the routine is complete. The workbook "info" is the
last to close.
The following code does not work for me.
How can I perform the loop based on the number of workbooks open less 1?

Sub test()
Number = Workbooks.Count - 1
ActiveWindow.ActivateNext
For Count = 1 To Number
Range("b1:b5").Copy
Workbooks("info.xls").Activate
Range("a1").Select
ActiveCell.End(xlDown).Offset(1, 0).Select
ActiveCell.PasteSpecial xlPasteValues
ActiveWindow.ActivateNext
ActiveWindow.Close savechanges:=False
Next Count
End Sub

l-hawk