Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all, but one, sheet?
I need my macro to select all sheets, no matter if they are charts or
worksheets, and no matter how they are named. I know: Sheets.Select But, I need my macro to select all, but one: If a sheet has the name "Periodebalancer", then it should NOT be selected, but still the rest of the sheets must still be selected. Sometimes the sheet "Periodebalancer" is present, and sometimes not. How to accomplish this??? TIA, |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all, but one, sheet?
Try something like this:
Dim sh As Worksheet For Each sh In ActiveWorkbook.Worksheets If (sh.Name) < "Periodebalancer" Then 'do something here End if Next Dim sh As Worksheet For Each sh In Worksheets If (sh.Name) < "Periodebalancer" Then End If Next sh HTH, Ryan--- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "Charlotte E" wrote: I need my macro to select all sheets, no matter if they are charts or worksheets, and no matter how they are named. I know: Sheets.Select But, I need my macro to select all, but one: If a sheet has the name "Periodebalancer", then it should NOT be selected, but still the rest of the sheets must still be selected. Sometimes the sheet "Periodebalancer" is present, and sometimes not. How to accomplish this??? TIA, |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all, but one, sheet?
here's one way
Sub select_Sheets() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name < "Periodebalancer" Then ws.Select False End If Next End Sub -- Gary Keramidas Excel 2003 "Charlotte E" <@ wrote in message ... I need my macro to select all sheets, no matter if they are charts or worksheets, and no matter how they are named. I know: Sheets.Select But, I need my macro to select all, but one: If a sheet has the name "Periodebalancer", then it should NOT be selected, but still the rest of the sheets must still be selected. Sometimes the sheet "Periodebalancer" is present, and sometimes not. How to accomplish this??? TIA, |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all, but one, sheet?
One way:
Option Explicit Sub testme() Dim myName As String Dim ShArr() As String Dim iCtr As Long Dim aCtr As Long Dim TestSh As Object myName = "Periodebalancer" With ActiveWorkbook ReDim ShArr(1 To .Sheets.Count) Set TestSh = Nothing On Error Resume Next Set TestSh = .Sheets(myName) On Error GoTo 0 If TestSh Is Nothing Then .Sheets.Select Else aCtr = 0 For iCtr = 1 To .Sheets.Count If .Sheets(iCtr).Visible = xlSheetVisible Then If .Sheets(iCtr).Name = TestSh.Name Then 'skip it Else aCtr = aCtr + 1 ShArr(aCtr) = .Sheets(iCtr).Name End If End If Next iCtr If aCtr = 0 Then MsgBox "Nothing to select!" Else ReDim Preserve ShArr(1 To aCtr) .Sheets(ShArr).Select End If End If End With End Sub Charlotte E wrote: I need my macro to select all sheets, no matter if they are charts or worksheets, and no matter how they are named. I know: Sheets.Select But, I need my macro to select all, but one: If a sheet has the name "Periodebalancer", then it should NOT be selected, but still the rest of the sheets must still be selected. Sometimes the sheet "Periodebalancer" is present, and sometimes not. How to accomplish this??? TIA, -- Dave Peterson |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all, but one, sheet?
Got it working - thanks :-)
Dave Peterson wrote: One way: Option Explicit Sub testme() Dim myName As String Dim ShArr() As String Dim iCtr As Long Dim aCtr As Long Dim TestSh As Object myName = "Periodebalancer" With ActiveWorkbook ReDim ShArr(1 To .Sheets.Count) Set TestSh = Nothing On Error Resume Next Set TestSh = .Sheets(myName) On Error GoTo 0 If TestSh Is Nothing Then .Sheets.Select Else aCtr = 0 For iCtr = 1 To .Sheets.Count If .Sheets(iCtr).Visible = xlSheetVisible Then If .Sheets(iCtr).Name = TestSh.Name Then 'skip it Else aCtr = aCtr + 1 ShArr(aCtr) = .Sheets(iCtr).Name End If End If Next iCtr If aCtr = 0 Then MsgBox "Nothing to select!" Else ReDim Preserve ShArr(1 To aCtr) .Sheets(ShArr).Select End If End If End With End Sub Charlotte E wrote: I need my macro to select all sheets, no matter if they are charts or worksheets, and no matter how they are named. I know: Sheets.Select But, I need my macro to select all, but one: If a sheet has the name "Periodebalancer", then it should NOT be selected, but still the rest of the sheets must still be selected. Sometimes the sheet "Periodebalancer" is present, and sometimes not. How to accomplish this??? TIA, |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all, but one, sheet?
Charlotte
Is there a reason you want/need to select the sheets? It generally isn't needed to do so, but without knowing exactly what you are trying to do that's hard to be sure about. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select all, but one, sheet?
Is there a reason you want/need to select the sheets?
Well, perhaps not, but it is just the only way, I know how to accomplish my goal: I have a workbook where all the sheets end up in a report that needs to be printed. I cannot print the sheets one by one, since the length of the sheets can vary each time the report is printed, and the only way I know to get the page numbers, in the page footer, to increase right, is by selecting all the sheets and then .PrintOut. However, the data, which generates the report is on the sheet, "Periodebalancer", which should not be in the report. So, in order to get pagenumbers right, I need to select all the sheets, except "Periodebalancer", before printing. But, I'm fully aware, that in VBA it is rarely, if not never, necessary to perform .Select, but in this special case, I simply don't know how else to accomplish correct incrementation of the page numbers upon .PrintOut. Hope this clearifies, and if anyone can tell me how to accomnplish my goal without selecting the sheets, please, let me know :-) Problem is that I need to have the 'First page number' on each sheet set to 'Auto' since I don't know how many pages the previous sheet had. But, now the entire macro, that generates and print the report, is working as a charm, and fully automatically, and I'm quite pleased with it, so thanks for your help, guys :-) BR, CE |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select a Sheet Issue in VBA on a very hidden sheet - fails | Excel Programming | |||
Alternate Row Select in Sheet 1 then Paste to Sheet 2 | Excel Worksheet Functions | |||
Select sheet, Pause till Enter pressed, return to previous sheet | Excel Programming | |||
How do I select price from sheet.b where sheet.a part no = sheet.b | Excel Worksheet Functions | |||
Select Sheet then Select Range | Excel Programming |