| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
number of workbooks less 1
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
Please omit that last part about changing the workbook name... -- Jim Cone |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
The procedure stops at the following line.
Workbooks("info.xls").Worksheets(info).Range("a1") .End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues -- l-hawk "Jim Cone" wrote: 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) |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
And what is the error you get? Jim Cone "hawki" wrote in message The procedure stops at the following line. Workbooks("info.xls").Worksheets(info).Range("a1") .End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues -- l-hawk |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
I get the following error. Run time error "9". Subscript out of range.
-- l-hawk "Jim Cone" wrote: And what is the error you get? Jim Cone "hawki" wrote in message The procedure stops at the following line. Workbooks("info.xls").Worksheets(info).Range("a1") .End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues -- l-hawk |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
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" wrote in message I get the following error. Run time error "9". Subscript out of range. -- l-hawk "Jim Cone" wrote: And what is the error you get? Jim Cone "hawki" wrote in message The procedure stops at the following line. Workbooks("info.xls").Worksheets(info).Range("a1") .End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues -- l-hawk |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
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").E nd(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").C opy Workbooks("info").Worksheets("info").Range("a1").E nd(xlDown).Offset(1, 0).PasteSpecial xlPasteValues Workbooks(lngCount).Close savechanges:=False End If Next lngCount Workbooks("info.xls").Close savechanges:=True End Sub -- l-hawk "Jim Cone" wrote: 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" wrote in message I get the following error. Run time error "9". Subscript out of range. -- l-hawk "Jim Cone" wrote: And what is the error you get? Jim Cone "hawki" wrote in message The procedure stops at the following line. Workbooks("info.xls").Worksheets(info).Range("a1") .End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues -- l-hawk |
|
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
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" 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").E nd(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").C opy Workbooks("info").Worksheets("info").Range("a1").E nd(xlDown).Offset(1, 0).PasteSpecial xlPasteValues Workbooks(lngCount).Close savechanges:=False End If Next lngCount Workbooks("info.xls").Close savechanges:=True End Sub -- l-hawk "Jim Cone" wrote: 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" wrote in message I get the following error. Run time error "9". Subscript out of range. -- l-hawk "Jim Cone" wrote: And what is the error you get? Jim Cone "hawki" wrote in message The procedure stops at the following line. Workbooks("info.xls").Worksheets(info).Range("a1") .End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues -- l-hawk |
|
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
number of workbooks less 1
Thanks so much for your assitance. You have been a jewel.
-- l-hawk |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| How to tell the number of current open workbooks... | Excel Worksheet Functions | |||
| Totals across increasing number of Workbooks | Excel Programming | |||
| Different Number of Workbooks each week | Excel Worksheet Functions | |||
| count number of workbooks open | Excel Programming | |||
| Number of workbooks open | Excel Programming | |||
Linear Mode
