Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Just out of curiousity, why didn't this work (a changeperpetuated to all sheets)?
Hi
You are missing a single dot before cells.select to set the reference to ws, ie it should read: ..Cells.Select I would also skip the select statement as it slows down your code. Sub Change_for_ALL_Worksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets With ws '----------------------------------------------------------------------- '.With Statement code STARTS here below: With .Cells .VerticalAlignment = xlCenter End With '.With Statement code ENDS here above. '----------------------------------------------------------------------- End With Next ws End Sub Hopes this helps ..... Per On 20 Aug., 06:20, StargateFan wrote: I found the following script, with my line of coding in the middle. *I was just trying to make all the sheets have horizontal alignment in them. *After running this, the sheets remained with the alignment they had. *I'm just curious as to how you'd script something that would run through all the sheets. I'm hoping/guessing that this could be a permanent, re-usable script in our Personal.xls. *Once we find code that works on one sheet, hopefully we could plug it into the middle, below, and the action would be repeated on all sheets (?). I searched and searched and this is about the only script that came up that _seemed_ to be a good base to have to re-do, as needed. Anyway, thanks! Sub Change_for_ALL_Worksheets() * * Dim ws As Worksheet * * For Each ws In Worksheets * * * * With ws '----------------------------------------------------------------------- * * * * * * *'.With Statement code STARTS here below: * * * * * * *Cells.Select * * * * * * *With Selection * * * * * * * * *.VerticalAlignment = xlCenter * * * * * * *End With * * * * * * *'.With Statement code ENDS here above. '----------------------------------------------------------------------- * * * * End With * * Next ws End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Just out of curiousity, why didn't this work (a change perpetuated to all sheets)?
I found the following script, with my line of coding in the middle. I
was just trying to make all the sheets have horizontal alignment in them. After running this, the sheets remained with the alignment they had. I'm just curious as to how you'd script something that would run through all the sheets. I'm hoping/guessing that this could be a permanent, re-usable script in our Personal.xls. Once we find code that works on one sheet, hopefully we could plug it into the middle, below, and the action would be repeated on all sheets (?). I searched and searched and this is about the only script that came up that _seemed_ to be a good base to have to re-do, as needed. Anyway, thanks! Sub Change_for_ALL_Worksheets() Dim ws As Worksheet For Each ws In Worksheets With ws '----------------------------------------------------------------------- '.With Statement code STARTS here below: Cells.Select With Selection .VerticalAlignment = xlCenter End With '.With Statement code ENDS here above. '----------------------------------------------------------------------- End With Next ws End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
work sheets | Excel Discussion (Misc queries) | |||
Work Sheets | Excel Discussion (Misc queries) | |||
change code to work over multiple sheets | Excel Programming | |||
Counting dates in multiple work sheets and work books | Excel Discussion (Misc queries) | |||
I wish to save my Excell work in my work sheets | Excel Worksheet Functions |