number of workbooks less 1

[hawki]

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

 

[Jim Cone]

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).Offset(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" <[email protected]>
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

 

[hawki]

The procedure stops at the line below.

Workbooks(lngCount).Range("b1:b5").Copy

 

[Jim Cone]

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

Same for...
Workbooks("info.xls").Worksheets(1).Range("a1").End(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" <[email protected]>
wrote in message
The procedure stops at the line below.
Workbooks(lngCount).Range("b1:b5").Copy

 

[Jim Cone]

Please omit that last part about changing the workbook name...

 

[hawki]

The procedure stops at the following line.

Workbooks("info.xls").Worksheets(info).Range("a1").End(xlDown).Offset(1,
0).PasteSpecial xlPasteValues

 

[Jim Cone]

And what is the error you get?
Jim Cone

"hawki" <[email protected]>
wrote in message
The procedure stops at the following line.

Workbooks("info.xls").Worksheets(info).Range("a1").End(xlDown).Offset(1,
0).PasteSpecial xlPasteValues

 

[hawki]

I get the following error. Run time error "9". Subscript out of range.

 

[Jim Cone]

If you do not use an index number to identify a specific workbook or
worksheet then you must use the name of the workbook or worksheet.
All names are strings except for those referring to range objects,
so enclose info in quotation marks...
Worksheets("info")
'--
Jim Cone


"hawki" <[email protected]>
wrote in message
I get the following error. Run time error "9". Subscript out of range.

 

[hawki]

Followng is the complete code which stops at the line below with an error
code of "9" subscript out of range.

Workbooks("info").Worksheets("info").Range("a1").End(xlDown).Offset(1,
0).PasteSpecial xlPasteValues


Sub test2()
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).Worksheets(1).Range("b1:b5").Copy
Workbooks("info").Worksheets("info").Range("a1").End(xlDown).Offset(1,
0).PasteSpecial xlPasteValues
Workbooks(lngCount).Close savechanges:=False
End If
Next lngCount
Workbooks("info.xls").Close savechanges:=True
End Sub

 

[Jim Cone]

The error message indicates that a name is not correct.
I suspect the Workbooks("info") should be Workbooks("info.xls")

Also, If Not Workbooks(lngCount).Name = "info" should be
If Not Workbooks(lngCount).Name = "info.xls"

The worksheet name should be what is shown on the sheet tab.

Good luck with it - I have a project that just came in that I have to complete.

Jim Cone



"hawki" <[email protected]>
wrote in message
Followng is the complete code which stops at the line below with an error
code of "9" subscript out of range.

Workbooks("info").Worksheets("info").Range("a1").End(xlDown).Offset(1,
0).PasteSpecial xlPasteValues


Sub test2()
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).Worksheets(1).Range("b1:b5").Copy
Workbooks("info").Worksheets("info").Range("a1").End(xlDown).Offset(1,
0).PasteSpecial xlPasteValues
Workbooks(lngCount).Close savechanges:=False
End If
Next lngCount
Workbooks("info.xls").Close savechanges:=True
End Sub

 

[hawki]

Thanks so much for your assitance. You have been a jewel.