Problems getting a column width changed in multiple sheets at the same time.

[steeler11111]

I have tried the following to change column "BL" in multiple sheets at the same time and it is the only thing that doesn't seem to happen. Every other action in the macro performs correctly except the column width. It appears to change Period 1 but then leaves the rest alone. Where am I going wrong? p.s no error comes up.

Sub unprotect_all_2()

For Each wsheet In Worksheets
wsheet.Unprotect
Next wsheet

Sheets(Array("Period 1", "Period 2", "Period 3", "Period 4", "Period 5", "Period 6", _
"Period 7", "Period 8", "Period 9", "Period 10", "Period 11", "Period 12", _
"Additional Checks")).Select

Range("BL2").Select

ActiveCell.FormulaR1C1 = "Has the appropriate contact been made and is the content of any correspondence sent clear and accurate whilst covering all relevant points/answering all questions (inc. spelling/grammar)"
Range("bw5").Select
ActiveCell.FormulaR1C1 = "Safeguarding"
Range("BW2").Select
ActiveCell.FormulaR1C1 = "1"
Range("BX2").Select
ActiveCell.FormulaR1C1 = "2"
Range("BY2").Select
ActiveCell.FormulaR1C1 = "3"
Range("BZ2").Select
ActiveCell.FormulaR1C1 = "4"
Range("CA2").Select
ActiveCell.FormulaR1C1 = "5"

Sheets(Array("Period 1", "Period 2", "Period 3", "Period 4", "Period 5", "Period 6", _
"Period 7", "Period 8", "Period 9", "Period 10", "Period 11", "Period 12", _
"Additional Checks")).Select
Sheets("period 1").activate
columns("bl:bl").columnwidth = 14.86

Sheets("Year to date").Select
Range("AA3").Value = "Safeguarding"
For Each wsheet In Worksheets
wsheet.Protect
Next wsheet

End Sub

In the end and in order to get it to work I've had to resort to the following script. Can anyone tell me what I'm doing wrong in the first script?

Sub unprotect_all()
For Each wsheet In Worksheets
wsheet.Unprotect
Next wsheet
Sheets(Array("Period 1", "Period 2", "Period 3", "Period 4", "Period 5", "Period 6", _
"Period 7", "Period 8", "Period 9", "Period 10", "Period 11", "Period 12", _
"Additional Checks")).Select

Range("BL2").Select

ActiveCell.FormulaR1C1 = "Has the appropriate contact been made and is the content of any correspondence sent clear and accurate whilst covering all relevant points/answering all questions (inc. spelling/grammar)"
Range("bw5").Select
ActiveCell.FormulaR1C1 = "Safeguarding"
Range("BW2").Select
ActiveCell.FormulaR1C1 = "1"
Range("BX2").Select
ActiveCell.FormulaR1C1 = "2"
Range("BY2").Select
ActiveCell.FormulaR1C1 = "3"
Range("BZ2").Select
ActiveCell.FormulaR1C1 = "4"
Range("CA2").Select
ActiveCell.FormulaR1C1 = "5"

Sheets("period 1").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 2").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 3").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 4").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 5").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 6").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 7").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 8").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 9").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 10").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 11").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("period 12").Select
Columns("bl:bl").ColumnWidth = 14.86
Sheets("additional checks").Select
Columns("bl:bl").ColumnWidth = 14.86


Sheets("Year to date").Select
Range("AA3").Value = "Safeguarding"
For Each wsheet In Worksheets
wsheet.Protect
Next wsheet

End Sub

[gmr4evr1]

It might be because there isn't a Next wsheet immediately after

Please Login or Register  to view this content.

Its after the wsheet.protect so it is only lopping the protect part of the code.

[steeler11111]

Hi,

No unfortunately that has not solved it. on a stand alone fix that came up with an error so I added in the following and still the same result, column width only changed in the "period 1" sheet.

Sheets(Array("Period 1", "Period 2", "Period 3", "Period 4", "Period 5", "Period 6", _
"Period 7", "Period 8", "Period 9", "Period 10", "Period 11", "Period 12", _
"Additional Checks")).Select
For Each wsheet In Selection
Sheets("period 1").Activate
Columns("bl:bl").ColumnWidth = 14.86
Next wsheet

[kadeo]

I checked your code against a wee test I did with the macro recorder. It looks pretty good except you have all of this:

Please Login or Register  to view this content.

repeated after you fill the cell values and before the columnwidth change but there is no need for it to be there, that was already done at the top of the code. This might be throwing things off maybe. If that's not it, I don't know what else it could be because it looks pretty good otherwise.

That said, I generally wouldn't use .select or .activate if there's another way. They can really slow down your code.

If you're up for a suggestion, the following code won't get "...a column width changed in multiple sheets at the same time", but it will achieve the same end result in a simple way by just looping through the sheets once and applying the columnwidth and the other changes to each sheet one after another. Your code loops through the sheets twice at the moment as it is. Oh, and there's no .select or .activate so it'll be quick too.

Please Login or Register  to view this content.

[steeler11111]

Thanks for the info kadeo, I'll give it a try!

[JOHN H. DAVIS]

Another FWIW:

Please Login or Register  to view this content.

The problem with the first code is in the red. On each sheet you still reactivate Sheet Period 1 to do the Column Width adjustment. Therefore it doesn't affect the other sheets in the Array.

Please Login or Register  to view this content.

[zbor]

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code.

Posting code between [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] tags makes your code much easier to read and copy for testing, it also maintains VBA formatting.

Highlight your code and click the # icon at the top of your post window. More information about these and other tags can be found here



(This thread should receive no further responses until this moderation request is fulfilled, as per Forum Rule 7)