Results 1 to 3 of 3
  1. #1
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I wrote this some time ago, but only got to test it in a live environment Friday.
    Do not run this on a live copy of your workbook.
    Amongst other things, it will reset ALL your formatting!

    I am often handed end-user workbooks, tarted up beyond all description, which I am to use as a reference data set.
    I don't need the autofilter dropdown, white lettering on a dark red background, and frozen panes, nor any of the other stuff that users love.
    I just need to raw data. Values. Or formulae.
    I want to make the worksheets "plainer", so I can see the real stuff underneath.

    This toy macro removes autofilter (thanks Hans , UNfreezes panes, maximizes the window, sets zoom=100 (some users still don't know how to r/c on the desktop and choose Settings), Autofits each column, and sets the formatting style to Normal.
    [pre]Sub Plainer()
    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
    If wks.AutoFilterMode = True Then
    wks.AutoFilterMode = False
    End If
    wks.Activate
    ActiveWindow.FreezePanes = False
    ActiveWindow.WindowState = xlMaximized
    ActiveWindow.Zoom = 100
    Cells.Select
    Range("A1").Activate
    Cells.EntireColumn.AutoFit
    Cells.Select
    Selection.Style = "Normal"
    Next wks
    End Sub[/pre]

  2. #2
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post
    Should the second half of the code not be inside the loop, else all the other formatting is only applied to the last sheet?

    Like this:

    [pre]Sub Plainer()
    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Worksheets
    If wks.AutoFilterMode = True Then
    wks.AutoFilterMode = False
    End If
    wks.Activate
    ActiveWindow.FreezePanes = False
    ActiveWindow.WindowState = xlMaximized
    ActiveWindow.Zoom = 100
    Cells.Select
    Range("A1").Activate
    Cells.EntireColumn.AutoFit
    Cells.Select
    Selection.Style = "Normal"
    Next wks
    End Sub
    [/pre]
    Regards,
    Rudi

  3. #3
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='Rudi' post='762619' date='01-Mar-2009 15:46']Should the second half of the code not be inside the loop,[/quote]
    Bless you, Rudi.
    I'm glad you decided to hang around (grin)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •