VBA for all sheets

[briana]

I need help creating a code that does this below function but runs for all sheets in a workbook. I do not want to choose by selecting names for each sheet, it needs to run regardless of sheet names.

Range("F9").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
ActiveWindow.FreezePanes = True

I also need help writing a code to go along with the above; it needs to end all sheets at the last colum that has data. There are hidden blank columns after the last column with data. (i.e. I have sheets that end at different columns, I need it to show the last column on each sheet instead of the first column.)

Any help is greatly appreciated.

 

[Ruddles]

Try this:-

[FONT=Fixedsys]Dim ws As Worksheet[/FONT]
 
[FONT=Fixedsys]  For Each ws In Worksheets[/FONT]
[FONT=Fixedsys]    ws.Activate[/FONT]
[FONT=Fixedsys]    ws.Range("F9").Select[/FONT]
[FONT=Fixedsys]    With ActiveWindow[/FONT]
[FONT=Fixedsys]      .SplitColumn = 0[/FONT]
[FONT=Fixedsys]      .SplitRow = 0[/FONT]
[FONT=Fixedsys]    End With[/FONT]
[FONT=Fixedsys]    ActiveWindow.FreezePanes = True[/FONT]
[FONT=Fixedsys]    ActiveWindow.ScrollColumn = ws.UsedRange.Columns.Count[/FONT]
[FONT=Fixedsys]  Next ws[/FONT]

You might need to tweak the ScrollColumn command.

 

[Trevor G]

Try this.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> wsFreeze()<br><SPAN style="color:#00007F">Dim</SPAN> ws <SPAN style="color:#00007F">As</SPAN> Worksheet<br> <br> <SPAN style="color:#00007F">For</SPAN> <SPAN style="color:#00007F">Each</SPAN> ws <SPAN style="color:#00007F">In</SPAN> Worksheets<br>   ws.Activate<br>   ws.Range("F9").Select<br>   <SPAN style="color:#00007F">With</SPAN> ActiveWindow<br>     .SplitColumn = 0<br>     .SplitRow = 0<br>   <SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">With</SPAN><br>   ActiveWindow.FreezePanes = <SPAN style="color:#00007F">True</SPAN><br>   ws.Range("F9").End(xlToRight).Select<br> <SPAN style="color:#00007F">Next</SPAN> ws<br><br><br><SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>

 

[briana]

Both codes worked for the getting the code to work across all sheets. Do you have any suggestions on creating a code to have each sheet end on the last column with data?

 

[Trevor G]

Can you state which version of excel you are using and also which row, assume you where wanting this on row 9 as this is where the freeze payne was added. if the Headings are above change to F8, otherwise let me know which version and also which column/row has the consistent data in it.

 

[briana]

I am using Excel 2007.

Row 8 will always have consistent data but the column will always change depending on the sheet.

 

[Trevor G]

Change the range in the last line to F8 then so it should end up like this

[FONT=Courier][COLOR=#00007f]Sub[/COLOR] wsFreeze()
[COLOR=#00007f]Dim[/COLOR] ws [COLOR=#00007f]As[/COLOR] Worksheet

[COLOR=#00007f]For[/COLOR] [COLOR=#00007f]Each[/COLOR] ws [COLOR=#00007f]In[/COLOR] Worksheets
   ws.Activate
   ws.Range("F9").Select
   [COLOR=#00007f]With[/COLOR] ActiveWindow
     .SplitColumn = 0
     .SplitRow = 0
   [COLOR=#00007f]End[/COLOR] [COLOR=#00007f]With[/COLOR]
   ActiveWindow.FreezePanes = [COLOR=#00007f]True[/COLOR]
   ws.Range("[COLOR=red]F8[/COLOR]").End(xlToRight).Select
[COLOR=#00007f]Next[/COLOR] ws


[COLOR=#00007f]End[/COLOR] [COLOR=#00007f]Sub[/COLOR][/FONT]

 

[briana]

It didnt work for having each sheet end on the last column with data. There are columns after the last one with data that are hidden. <!-- / message -->

 

[Trevor G]

Ok try this then. What it does is select the last cell in Row 8 so that makes it XFD8 then it is moving back to left until it find something in the cell to the left. If you are saving the workbook to a previous version then you will have to change XFD to IV

Sub wsFreeze()
Dim ws As Worksheet

For Each ws In Worksheets
ws.Activate
ws.Range("F9").Select
With ActiveWindow
.SplitColumn = 0
.SplitRow = 0
End With
ActiveWindow.FreezePanes = True
ws.Range("XFD8").End(xlToLeft).Select
Next ws


End Sub

 

[briana]

Thank you very much, it worked.