Immediate Window statements

[K. Georgiadis]

Could you help me with the syntax for Immediate Window statements to return the following results:


Remove Split in all worksheets
Return the number (position) of the worksheet named "Sales Summary"
Unlock cell B140 in worksheets 1 to 14
Thanks!

[mvidas]

Hello,

1 - For Each WS In ActiveWorkbook.Sheets: WS.Activate: ActiveWindow.Split = False: Next WS

2 - Debug.Print Sheets("sales summary").Index

3 - For i = 1 To 14: Sheets(i).Range("B140").Locked = False: Next i

Should do it for you!
Matt

[K. Georgiadis]

Thanks! I'll give these a try

[mvidas]

I never really use the immediate window for things like this, I always just make a sub and type all those in, then F5 to run. But either way, they should take care of what you need!
Let me know if you want any more

[K. Georgiadis]

I tested them and they work great. Thanks again. Marking "solved"

[K. Georgiadis]

Hey Matt, one more question if I may:

what would the instructions look like if I actually wanted to insert a window split on column A Row 13:

a) all worksheets in the book
b) worksheets 1 to 14

Thanks!

[mvidas]

You may ask as many questions as you'd like. These are a little different (and a bit longer!), but:
a)
For Each WS In ActiveWorkbook.Sheets: WS.Activate: ActiveWindow.SplitColumn = 0: ActiveWindow.SplitRow = 12: Next WS

b)
For i = 1 To 14: Sheets(i).Activate: ActiveWindow.SplitColumn = 0: ActiveWindow.SplitRow = 12: Next i

[K. Georgiadis]

that's terrific. Thank you very much.

I still have so much to learn :-(

[mvidas]

If you're ever unsure of how to word something in vba, record a macro in excel then perform that task. You would see the format of .SplitColumn and .SplitRow that way.
Just takes practice is all :)

[K. Georgiadis]

In the next few days I will need to create two macros whereby the click of a button will change a certain cell value in 20+ sheets to either "1" or "2" (this cell happens to be the link cell for a combo box that activates one of 2 available scenarios). I recorded this type of macro before and I will do it again for this next exercise, knowing that I will end up with a ton of unnecessary code, tracing all of my navigation steps. I will probably come here for help to streamline it a little bit. But you are absolutely right: the recorder is a great starting point for these type of macros and I will need to use it a little more consistently

[mdmackillop]

Not just a starting point. I still use it regularly, and then edit to suit. You'll soon learn how to cut out the unnecessary Select and other verbiage.