| Home |
| Search |
| Today's Posts |
|
|
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Loop all Sheets not working.
I have the following macro which I want executed for all worksheets within a
book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() €˜The following hides columns A,C to E, G to M, and O to AL. €˜It then sets the column width for Columns B, F and N as well as the €˜row height for row 1. €˜Lastly, columns B, F and N are selected and formatting done to €˜ensure that they are not the Text within the columns is not €˜wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
Put this around your code and it will run on all sheets
For Each wksht In ActiveWorkbook.Worksheets your code Next wksht "Pank" wrote: I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() €˜The following hides columns A,C to E, G to M, and O to AL. €˜It then sets the column width for Columns B, F and N as well as the €˜row height for row 1. €˜Lastly, columns B, F and N are selected and formatting done to €˜ensure that they are not the Text within the columns is not €˜wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
Mike,
Firstly, thank U for the prompt response. I put my code round the code you supplied and unfortunately, the result was the same as previously, in that the code only works on the sheet that is selected, to run against all other sheets, I have to select them individually and run. Any ideas? "Mike" wrote: Put this around your code and it will run on all sheets For Each wksht In ActiveWorkbook.Worksheets your code Next wksht "Pank" wrote: I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() €˜The following hides columns A,C to E, G to M, and O to AL. €˜It then sets the column width for Columns B, F and N as well as the €˜row height for row 1. €˜Lastly, columns B, F and N are selected and formatting done to €˜ensure that they are not the Text within the columns is not €˜wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
Hi Pank,
I'm at a bit of a loss to explain this because it works perfectly in my Excel 2003. Where is the code? it should be a module. Alt + F11 and insert new module and paste the code in to that. You may get the problem you describe if yo have right-clicked a sheet tab and pasted the code into that. Mike "Pank" wrote: Mike, Firstly, thank U for the prompt response. I put my code round the code you supplied and unfortunately, the result was the same as previously, in that the code only works on the sheet that is selected, to run against all other sheets, I have to select them individually and run. Any ideas? "Mike" wrote: Put this around your code and it will run on all sheets For Each wksht In ActiveWorkbook.Worksheets your code Next wksht "Pank" wrote: I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() €˜The following hides columns A,C to E, G to M, and O to AL. €˜It then sets the column width for Columns B, F and N as well as the €˜row height for row 1. €˜Lastly, columns B, F and N are selected and formatting done to €˜ensure that they are not the Text within the columns is not €˜wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#5
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
Hi Mike,
The code is in Module 5, I also have another macro which is in Module 4. I note that your version is 2003, my version of Excel is 2002, would that make any difference? Once again thank U for your help. "Mike" wrote: Hi Pank, I'm at a bit of a loss to explain this because it works perfectly in my Excel 2003. Where is the code? it should be a module. Alt + F11 and insert new module and paste the code in to that. You may get the problem you describe if yo have right-clicked a sheet tab and pasted the code into that. Mike "Pank" wrote: Mike, Firstly, thank U for the prompt response. I put my code round the code you supplied and unfortunately, the result was the same as previously, in that the code only works on the sheet that is selected, to run against all other sheets, I have to select them individually and run. Any ideas? "Mike" wrote: Put this around your code and it will run on all sheets For Each wksht In ActiveWorkbook.Worksheets your code Next wksht "Pank" wrote: I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() €˜The following hides columns A,C to E, G to M, and O to AL. €˜It then sets the column width for Columns B, F and N as well as the €˜row height for row 1. €˜Lastly, columns B, F and N are selected and formatting done to €˜ensure that they are not the Text within the columns is not €˜wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#6
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
Pank,
Yet another effort. Please try this, tested on Excel 2002. Sub dontblameme() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets Name = ws.Name Worksheets(Name).Select Put your code here Next ws End Sub "Pank" wrote: Hi Mike, The code is in Module 5, I also have another macro which is in Module 4. I note that your version is 2003, my version of Excel is 2002, would that make any difference? Once again thank U for your help. "Mike" wrote: Hi Pank, I'm at a bit of a loss to explain this because it works perfectly in my Excel 2003. Where is the code? it should be a module. Alt + F11 and insert new module and paste the code in to that. You may get the problem you describe if yo have right-clicked a sheet tab and pasted the code into that. Mike "Pank" wrote: Mike, Firstly, thank U for the prompt response. I put my code round the code you supplied and unfortunately, the result was the same as previously, in that the code only works on the sheet that is selected, to run against all other sheets, I have to select them individually and run. Any ideas? "Mike" wrote: Put this around your code and it will run on all sheets For Each wksht In ActiveWorkbook.Worksheets your code Next wksht "Pank" wrote: I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() €˜The following hides columns A,C to E, G to M, and O to AL. €˜It then sets the column width for Columns B, F and N as well as the €˜row height for row 1. €˜Lastly, columns B, F and N are selected and formatting done to €˜ensure that they are not the Text within the columns is not €˜wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#7
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
Hi
The problem is that you are not selecting the worksheet after the For statement. Also, you can condense your code by carrying out the operations in one stage as follows, and eliminating many of the Select statements will make the code run a lot faster. Sub test() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Select wks.Range("A1, C1:E1, G1:M1, O1:AL1").EntireColumn.Hidden = True wks.Range("B1").ColumnWidth = 35 wks.Range("F1").ColumnWidth = 12 wks.Range("N1").ColumnWidth = 20 wks.Rows("1:1").RowHeight = 20 wks.Range("B1, F1, N1").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub -- Regards Roger Govier "Pank" wrote in message ... I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() 'The following hides columns A,C to E, G to M, and O to AL. 'It then sets the column width for Columns B, F and N as well as the 'row height for row 1. 'Lastly, columns B, F and N are selected and formatting done to 'ensure that they are not the Text within the columns is not 'wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#8
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
My apologies
the line wks.Range("B1, F1, N1").Select should of course read wks.Range("B1, F1, N1").EntireColumn.Select -- Regards Roger Govier "Roger Govier" wrote in message ... Hi The problem is that you are not selecting the worksheet after the For statement. Also, you can condense your code by carrying out the operations in one stage as follows, and eliminating many of the Select statements will make the code run a lot faster. Sub test() Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets wks.Select wks.Range("A1, C1:E1, G1:M1, O1:AL1").EntireColumn.Hidden = True wks.Range("B1").ColumnWidth = 35 wks.Range("F1").ColumnWidth = 12 wks.Range("N1").ColumnWidth = 20 wks.Rows("1:1").RowHeight = 20 wks.Range("B1, F1, N1").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub -- Regards Roger Govier "Pank" wrote in message ... I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() 'The following hides columns A,C to E, G to M, and O to AL. 'It then sets the column width for Columns B, F and N as well as the 'row height for row 1. 'Lastly, columns B, F and N are selected and formatting done to 'ensure that they are not the Text within the columns is not 'wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#10
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
Columns("A,c:e,g:m,o:al").Hidden = True
should be range("a1,c1:e1,g1:m1,o1:al1).entirecolumn.hidden= true with .Columns("F,N") with .range("f1,n1").entirecolumn -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... untested but try this without any selections. Your clue could have come from the with statements in your code. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets with ws .Columns("A,c:e,g:m,o:al").Hidden = True .Columns("B").ColumnWidth = 35 .Columns("F").ColumnWidth = 12 .Columns("N").ColumnWidth = 20 .Rows("1:1").RowHeight = 20 with .Columns("B") .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With with .Columns("F,N") .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With end with Next wks End Sub -- Don Guillett SalesAid Software "Pank" wrote in message ... I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() 'The following hides columns A,C to E, G to M, and O to AL. 'It then sets the column width for Columns B, F and N as well as the 'row height for row 1. 'Lastly, columns B, F and N are selected and formatting done to 'ensure that they are not the Text within the columns is not 'wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
|
#11
Posted to microsoft.public.excel.newusers
|
|||
|
|||
|
Loop all Sheets not working.
Don, Roger,
Thank you for you input, I will try it out. "Don Guillett" wrote: Columns("A,c:e,g:m,o:al").Hidden = True should be range("a1,c1:e1,g1:m1,o1:al1).entirecolumn.hidden= true with .Columns("F,N") with .range("f1,n1").entirecolumn -- Don Guillett SalesAid Software "Don Guillett" wrote in message ... untested but try this without any selections. Your clue could have come from the with statements in your code. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets with ws .Columns("A,c:e,g:m,o:al").Hidden = True .Columns("B").ColumnWidth = 35 .Columns("F").ColumnWidth = 12 .Columns("N").ColumnWidth = 20 .Rows("1:1").RowHeight = 20 with .Columns("B") .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With with .Columns("F,N") .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With end with Next wks End Sub -- Don Guillett SalesAid Software "Pank" wrote in message ... I have the following macro which I want executed for all worksheets within a book. I have tried it out and it only runs in the sheet that is selected, if I choose another sheet and run it, it runs. Can someone advise why it is not running against all sheets or what changes are required to make it run against all sheets? Sub Formatting() 'The following hides columns A,C to E, G to M, and O to AL. 'It then sets the column width for Columns B, F and N as well as the 'row height for row 1. 'Lastly, columns B, F and N are selected and formatting done to 'ensure that they are not the Text within the columns is not 'wrapped. Dim wks As Worksheet For Each wks In ActiveWorkbook.Worksheets Columns("A:A").Select Selection.EntireColumn.Hidden = True Columns("C:E").Select Selection.EntireColumn.Hidden = True Columns("G:M").Select Selection.EntireColumn.Hidden = True Columns("O:AL").Select Selection.EntireColumn.Hidden = True Columns("B:B").Select Selection.ColumnWidth = 35 Columns("F:F").Select Selection.ColumnWidth = 12 Columns("N:N").Select Selection.ColumnWidth = 20 Rows("1:1").Select Range("B1").Activate Selection.RowHeight = 20 Columns("B:B").Select With Selection .WrapText = False .Orientation = 0 .AddIndent = False .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With With Selection .HorizontalAlignment = xlLeft .WrapText = False .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("F:F").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Columns("N:N").Select With Selection .HorizontalAlignment = xlLeft .Orientation = 0 .AddIndent = False .IndentLevel = 0 .ShrinkToFit = False .ReadingOrder = xlContext .MergeCells = False End With Next wks End Sub Any assistance offered is appreciated. |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Loop through varible list of sheets | Excel Discussion (Misc queries) | |||
| Working with multiple sheets in a workbook | Excel Discussion (Misc queries) | |||
| Working with sheets in Excel 2003 | Excel Worksheet Functions | |||
| On Error GoTo Label in a loop only working once. | Charts and Charting in Excel | |||
| creating macro working across multiple sheets | Excel Worksheet Functions | |||
Hybrid Mode
