Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Hi everyone
I recorded a macro to "Select All Sheets" in a workbook, then make a few column formatting changes and then "Ungroup Sheets". But when run as a Sub Procedure only the ActiveSheet is changed. It appears that selecting all sheets and making any changes manually will work but not when run as a sub Procedure. Is this a default condition of Excel, if so is there a work around? TIA -- Thank you Regards Bob C Using Windows XP Home + Office 2003 Pro |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Hi Bob
You're right. I never noticed this. Anyway, you'll have better control looping the sheets like this: Sub test() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'sample actions: S.Columns(2).ColumnWidth = 12 S.Range("B2").Value = Time Next End Sub In general macros should not select or activate, they run faster without it and the user isn't annoyed by the changing sheets or moving cursors. HTH. Best wishes Harald "Robert Christie" skrev i melding ... Hi everyone I recorded a macro to "Select All Sheets" in a workbook, then make a few column formatting changes and then "Ungroup Sheets". But when run as a Sub Procedure only the ActiveSheet is changed. It appears that selecting all sheets and making any changes manually will work but not when run as a sub Procedure. Is this a default condition of Excel, if so is there a work around? TIA -- Thank you Regards Bob C Using Windows XP Home + Office 2003 Pro |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Thank you Harald
For once it wasnt something I had typed incorrectly. Regards Bob C. "Harald Staff" wrote: Hi Bob You're right. I never noticed this. Anyway, you'll have better control looping the sheets like this: Sub test() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'sample actions: S.Columns(2).ColumnWidth = 12 S.Range("B2").Value = Time Next End Sub In general macros should not select or activate, they run faster without it and the user isn't annoyed by the changing sheets or moving cursors. HTH. Best wishes Harald "Robert Christie" skrev i melding ... Hi everyone I recorded a macro to "Select All Sheets" in a workbook, then make a few column formatting changes and then "Ungroup Sheets". But when run as a Sub Procedure only the ActiveSheet is changed. It appears that selecting all sheets and making any changes manually will work but not when run as a sub Procedure. Is this a default condition of Excel, if so is there a work around? TIA -- Thank you Regards Bob C Using Windows XP Home + Office 2003 Pro |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Grouping is not really supported in VBA. You can do some things by
selecting a range of cells and using the selection object (setting the selection.interior.colorindex works for example), but the best is to loop as Harald has indicated. -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Hi everyone I recorded a macro to "Select All Sheets" in a workbook, then make a few column formatting changes and then "Ungroup Sheets". But when run as a Sub Procedure only the ActiveSheet is changed. It appears that selecting all sheets and making any changes manually will work but not when run as a sub Procedure. Is this a default condition of Excel, if so is there a work around? TIA -- Thank you Regards Bob C Using Windows XP Home + Office 2003 Pro |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Applied Harald's suggested loop and it runs very fast on six formatting cell
on 21 sheets. Regards Bob C. "Tom Ogilvy" wrote: Grouping is not really supported in VBA. You can do some things by selecting a range of cells and using the selection object (setting the selection.interior.colorindex works for example), but the best is to loop as Harald has indicated. -- Regards, Tom Ogilvy "Robert Christie" wrote in message ... Hi everyone I recorded a macro to "Select All Sheets" in a workbook, then make a few column formatting changes and then "Ungroup Sheets". But when run as a Sub Procedure only the ActiveSheet is changed. It appears that selecting all sheets and making any changes manually will work but not when run as a sub Procedure. Is this a default condition of Excel, if so is there a work around? TIA -- Thank you Regards Bob C Using Windows XP Home + Office 2003 Pro |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Harald
How or can the freeze panes be included in the loop operation? Ws.Range("A3").Select Ws.ActiveWindow.FreezePanes = True The above code and others I tried wouldn't work. TIA Regards Bob C. "Harald Staff" wrote: Hi Bob You're right. I never noticed this. Anyway, you'll have better control looping the sheets like this: Sub test() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'sample actions: S.Columns(2).ColumnWidth = 12 S.Range("B2").Value = Time Next End Sub In general macros should not select or activate, they run faster without it and the user isn't annoyed by the changing sheets or moving cursors. HTH. Best wishes Harald "Robert Christie" skrev i melding ... Hi everyone I recorded a macro to "Select All Sheets" in a workbook, then make a few column formatting changes and then "Ungroup Sheets". But when run as a Sub Procedure only the ActiveSheet is changed. It appears that selecting all sheets and making any changes manually will work but not when run as a sub Procedure. Is this a default condition of Excel, if so is there a work around? TIA -- Thank you Regards Bob C Using Windows XP Home + Office 2003 Pro |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Put
Ws.Activate above Ws.Range("A3").Select I'm not too happy with activation/selection in code, but this is a Window property and I think you may have to. HTH. Best wishes Harald "Robert Christie" skrev i melding ... Harald How or can the freeze panes be included in the loop operation? Ws.Range("A3").Select Ws.ActiveWindow.FreezePanes = True |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Thanks Harald
Im a self taught novice at programming, mostly rely on asking and viewing posts in this newsgroup for ideas, along with a couple of books. Im trying to modify and upgrade all my code to speed up and fully automate many processes I feel along with other users Im trying or misusing Excel as a database program. Due to this misuse, many of my difficulties are not covered in Excels built in help. But Access is one program that really stumps me. Thanks again Harald Regards Bob C. "Harald Staff" wrote: Put Ws.Activate above Ws.Range("A3").Select I'm not too happy with activation/selection in code, but this is a Window property and I think you may have to. HTH. Best wishes Harald "Robert Christie" skrev i melding ... Harald How or can the freeze panes be included in the loop operation? Ws.Range("A3").Select Ws.ActiveWindow.FreezePanes = True |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
I am trying your loop that you listed in this post so I can format all sheets
to print better. Here is what I am trying to use, but it isn't working for some reason. Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'makes it so all pages print nicely With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = 65 End With Next Thanks for your help. Brian "Harald Staff" wrote: Hi Bob You're right. I never noticed this. Anyway, you'll have better control looping the sheets like this: Sub test() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'sample actions: S.Columns(2).ColumnWidth = 12 S.Range("B2").Value = Time Next End Sub In general macros should not select or activate, they run faster without it and the user isn't annoyed by the changing sheets or moving cursors. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Brian
Amended code that works on all sheets, not just the Active one. Public Sub gonl() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'makes it so all pages print nicely With S.PageSetup .PrintTitleRows = "$1:$1" .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = 65 End With Next End Sub Gord Dibben Excel MVP On Tue, 20 Dec 2005 13:33:02 -0800, "bttreadwell" wrote: I am trying your loop that you listed in this post so I can format all sheets to print better. Here is what I am trying to use, but it isn't working for some reason. Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'makes it so all pages print nicely With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = 65 End With Next Thanks for your help. Brian "Harald Staff" wrote: Hi Bob You're right. I never noticed this. Anyway, you'll have better control looping the sheets like this: Sub test() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'sample actions: S.Columns(2).ColumnWidth = 12 S.Range("B2").Value = Time Next End Sub In general macros should not select or activate, they run faster without it and the user isn't annoyed by the changing sheets or moving cursors. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Great, that makes sense. I will try it tomorrow. Thanks for your help.
"Gord Dibben" wrote: Brian Amended code that works on all sheets, not just the Active one. Public Sub gonl() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'makes it so all pages print nicely With S.PageSetup .PrintTitleRows = "$1:$1" .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = 65 End With Next End Sub Gord Dibben Excel MVP On Tue, 20 Dec 2005 13:33:02 -0800, "bttreadwell" wrote: I am trying your loop that you listed in this post so I can format all sheets to print better. Here is what I am trying to use, but it isn't working for some reason. Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'makes it so all pages print nicely With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = 65 End With Next Thanks for your help. Brian "Harald Staff" wrote: Hi Bob You're right. I never noticed this. Anyway, you'll have better control looping the sheets like this: Sub test() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'sample actions: S.Columns(2).ColumnWidth = 12 S.Range("B2").Value = Time Next End Sub In general macros should not select or activate, they run faster without it and the user isn't annoyed by the changing sheets or moving cursors. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Select All Sheets in Workbook
Worked perfectly, thank you.
"Gord Dibben" wrote: Brian Amended code that works on all sheets, not just the Active one. Public Sub gonl() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'makes it so all pages print nicely With S.PageSetup .PrintTitleRows = "$1:$1" .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = 65 End With Next End Sub Gord Dibben Excel MVP On Tue, 20 Dec 2005 13:33:02 -0800, "bttreadwell" wrote: I am trying your loop that you listed in this post so I can format all sheets to print better. Here is what I am trying to use, but it isn't working for some reason. Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'makes it so all pages print nicely With ActiveSheet.PageSetup .PrintTitleRows = "$1:$1" .CenterHorizontally = True .CenterVertically = False .Orientation = xlPortrait .PaperSize = xlPaperLetter .Zoom = 65 End With Next Thanks for your help. Brian "Harald Staff" wrote: Hi Bob You're right. I never noticed this. Anyway, you'll have better control looping the sheets like this: Sub test() Dim S As Worksheet For Each S In ActiveWorkbook.Worksheets 'sample actions: S.Columns(2).ColumnWidth = 12 S.Range("B2").Value = Time Next End Sub In general macros should not select or activate, they run faster without it and the user isn't annoyed by the changing sheets or moving cursors. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Select sheets ina workbook based on radio buttons and move them | Excel Worksheet Functions | |||
How can I print only select sheets in my workbook? | New Users to Excel | |||
macro to select sheets/page in a workbook and print them | Excel Programming | |||
Sheets select method fails when workbook is opened by another workbook | Excel Programming | |||
Copy select sheets to another Workbook | Excel Programming |