| 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 |
| 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 | |||
Hybrid Mode
