Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in VBA - more info
I'm somewhat new at message boards so forgive me if adding a new post
is not the way this should be done. My original post is below. What I fail to mention is that there are more than just 3 sheets in the workbook. It is more like this: sheet1, sheet2,....up to sheet6. I want to first select and print sheets1 through 3, then select and print sheets 4 through 6 (To start the page numbering over). I can't just select sheets by number, say array(1,2,3), because if I add say sheet1b, I will then have four sheets to print and would have to modify the macro. thanks again. below is original post. If I select the first sheet in a workbook, hold shift and go to the last sheet, all are selected for printing. However, when I record this function, the VBA code lists all sheets by name. For instance, If the workbook contains 'sheet1' 'sheet2' and 'sheet3' the macro code recorded = Sheets(Array("sheet1", "sheet2", "sheet3")).Select I would like to know if there is a way to select from sheet1 to sheet3 without explicitly naming them. The problem is that in the actual workbook, sometimes I add or delete sheets, and sometimes rename them for clarity. I don't want to have to modify the macro everytime I do that. (the first and last sheets can always remain the same). _________________ Thank you in advance for any help. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in VBA - more info
I think this was pointed out before, but try
Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name)).Select where the 1, 2, 3 are the relative positions of the sheets in the workbook. Change to 4, 5, 6 to select those sheets. Add to the array if you want more sheets selected at a time. Just watch the "(" & ")" signs. -- sb "gfalc3194" wrote in message om... I'm somewhat new at message boards so forgive me if adding a new post is not the way this should be done. My original post is below. What I fail to mention is that there are more than just 3 sheets in the workbook. It is more like this: sheet1, sheet2,....up to sheet6. I want to first select and print sheets1 through 3, then select and print sheets 4 through 6 (To start the page numbering over). I can't just select sheets by number, say array(1,2,3), because if I add say sheet1b, I will then have four sheets to print and would have to modify the macro. thanks again. below is original post. If I select the first sheet in a workbook, hold shift and go to the last sheet, all are selected for printing. However, when I record this function, the VBA code lists all sheets by name. For instance, If the workbook contains 'sheet1' 'sheet2' and 'sheet3' the macro code recorded = Sheets(Array("sheet1", "sheet2", "sheet3")).Select I would like to know if there is a way to select from sheet1 to sheet3 without explicitly naming them. The problem is that in the actual workbook, sometimes I add or delete sheets, and sometimes rename them for clarity. I don't want to have to modify the macro everytime I do that. (the first and last sheets can always remain the same). _________________ Thank you in advance for any help. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in VBA - more info
No, if you don't know the name of the sheets and you don't know the position
of the sheets (because in both cased, they might change), then you can't identify which sheets you want to print. You might be able to go to the codenames of the sheets and get the current name of the sheet from that. then you would have a list of names, but codenames can be changed as well (although less likely). If the sheets have some unique identifying information on them, then you could loop through the sheets and identify which sheet is which - just as you are doing visually. -- Regards, Tom Ogilvy gfalc3194 wrote in message om... I'm somewhat new at message boards so forgive me if adding a new post is not the way this should be done. My original post is below. What I fail to mention is that there are more than just 3 sheets in the workbook. It is more like this: sheet1, sheet2,....up to sheet6. I want to first select and print sheets1 through 3, then select and print sheets 4 through 6 (To start the page numbering over). I can't just select sheets by number, say array(1,2,3), because if I add say sheet1b, I will then have four sheets to print and would have to modify the macro. thanks again. below is original post. If I select the first sheet in a workbook, hold shift and go to the last sheet, all are selected for printing. However, when I record this function, the VBA code lists all sheets by name. For instance, If the workbook contains 'sheet1' 'sheet2' and 'sheet3' the macro code recorded = Sheets(Array("sheet1", "sheet2", "sheet3")).Select I would like to know if there is a way to select from sheet1 to sheet3 without explicitly naming them. The problem is that in the actual workbook, sometimes I add or delete sheets, and sometimes rename them for clarity. I don't want to have to modify the macro everytime I do that. (the first and last sheets can always remain the same). _________________ Thank you in advance for any help. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
selecting sheets in VBA - more info
Again, if this was apropos (which the OP has stated it isn't), then
Sheets(Array(1,2,3)).Select would be the cleaner method. -- Regards, Tom Ogilvy steve wrote in message ... I think this was pointed out before, but try Sheets(Array(Sheets(1).Name, Sheets(2).Name, Sheets(3).Name)).Select where the 1, 2, 3 are the relative positions of the sheets in the workbook. Change to 4, 5, 6 to select those sheets. Add to the array if you want more sheets selected at a time. Just watch the "(" & ")" signs. -- sb "gfalc3194" wrote in message om... I'm somewhat new at message boards so forgive me if adding a new post is not the way this should be done. My original post is below. What I fail to mention is that there are more than just 3 sheets in the workbook. It is more like this: sheet1, sheet2,....up to sheet6. I want to first select and print sheets1 through 3, then select and print sheets 4 through 6 (To start the page numbering over). I can't just select sheets by number, say array(1,2,3), because if I add say sheet1b, I will then have four sheets to print and would have to modify the macro. thanks again. below is original post. If I select the first sheet in a workbook, hold shift and go to the last sheet, all are selected for printing. However, when I record this function, the VBA code lists all sheets by name. For instance, If the workbook contains 'sheet1' 'sheet2' and 'sheet3' the macro code recorded = Sheets(Array("sheet1", "sheet2", "sheet3")).Select I would like to know if there is a way to select from sheet1 to sheet3 without explicitly naming them. The problem is that in the actual workbook, sometimes I add or delete sheets, and sometimes rename them for clarity. I don't want to have to modify the macro everytime I do that. (the first and last sheets can always remain the same). _________________ Thank you in advance for any help. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Selecting only specific rows for a Sort - additional bit of info.. | Excel Discussion (Misc queries) | |||
copy cell info to other sheets, other sheets dont contain all row. | Excel Worksheet Functions | |||
selecting from dropdown--have related info auto carry over | Excel Worksheet Functions | |||
consolodating info from different sheets with different info | Excel Worksheet Functions | |||
selecting sheets | Excel Programming |