Results 1 to 8 of 8
  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

    Dynamic Formatting Of Worksheets (Excel 2000+)

    I had reason to provide a client with a hands-off approach to formatting data; in essence I wanted them to be in control of adjusting the appearance of worksheets long after I was gone.
    The accompanying code demonstrates a way of achieving this.
    Attached Files Attached Files

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Formatting Of Worksheets (Excel 2000+)

    It works nicely.

    But... (isn't there always a but), it would be even nicer if the macro also adjusted the column widths. You only need to add a line

    wksTarget.UsedRange.Columns.AutoFit

    near the end of CopyFormulae.

  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

    Re: Dynamic Formatting Of Worksheets (Excel 2000+)

    > it would be even nicer if the macro also adjusted the column widths.
    Well, it's funny you should mensh ....
    I have another glob of code that not only best-column-fits, but also auto-adjusts page sizes to maximize the printed data on a minimal number of sheets.
    I recall that I offered parameters:
    <UL><LI>Portrait/Landscape/Both
    <LI>Minimum contraction (e.g. no smaller than 80% original size)
    <LI>Maximum expansion (e.g. no larger than 125% original size)
    <LI>Increment (e.g 2% at a time).[/list]The code determined how many physical sheets of paper at each applixcable setting and then set the appropriate zoom/fit factors.
    Ta da!
    Perhaps I should post it, too.

  4. #4
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Formatting Of Worksheets (Excel 2000+)

    Here's the "Best Fit"<pre>Public Sub BestFitColumns()
    Dim wksOriginal As Worksheet
    Set wksOriginal = ActiveSheet
    Dim rngOriginal As Range
    Set rngOriginal = Selection
    Dim wks As Worksheet
    For Each wks In ThisWorkbook.Worksheets
    wks.Activate
    Call BestFitColumnsWKS(wks)
    Next wks
    wksOriginal.Activate
    rngOriginal.Select
    End Sub
    Public Function BestFitColumnsWKS(wks As Worksheet)
    wks.Activate
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    'Sub TESTBestFitColumns()
    ' Dim wks As Worksheet
    ' For Each wks In ThisWorkbook.Worksheets
    ' Call BestFitColumns(wks)
    ' Next wks
    'End Sub
    End Function
    </pre>


  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Formatting Of Worksheets (Excel 2000+)

    Thanks.

    In general, Excel VBA code is more efficient if you avoid activating worksheets and selecting cells. If you change

    Public Function BestFitColumnsWKS(wks As Worksheet)
    wks.Activate
    Cells.Select
    Selection.Columns.AutoFit
    Range("A1").Select
    End Function

    to

    Public Function BestFitColumnsWKS(wks As Worksheet)
    wks.Columns.AutoFit
    End Function

    there is no need to save and restore the original worksheet and selection in BestFitColumns, you only need to loop through the sheets.

  6. #6
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Formatting Of Worksheets (Excel 2000+)

    > wks.Columns.AutoFit
    Thanks.
    I have prostuituited my art to give pleasure to clients who like to see flashing lights.
    noone looks at the Application.StatusBar anyway.

    When I was your age, (ahem!) we had glass walls so that managers could see flashing lights AND whirring tapes .....

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Dynamic Formatting Of Worksheets (Excel 2000+)

    See the thread starting at <post:=664,839>post 664,839</post:> for another way to provide pleasure to clients <img src=/S/evilgrin.gif border=0 alt=evilgrin width=15 height=15>

  8. #8
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Dynamic Formatting Of Worksheets (Excel 2000+)

    Hmmmm. I have 23 GB of MP3 on my Big Beige Box .....

Posting Permissions

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