Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I added a row (row 6) to each of the worksheets in the workbook where the cells each contain a specific number of dashes. There are varying number of columns in each of the printable ranges in the worksheets and I want their column widths to always adjust to the widths specified by the dashes. I use Group to group the rows so I can hide/unhide as needed with the plus/minus button.

    The steps in the following macro work as expected when I recorded the macro, but when I run the macro only the column widths on one sheet (the Active one) are adjusted.

    -Why does this work manually but not as a recorded macro?
    -How can the code be modified so that the instructions work on all Selected sheets?

    Sub SetColWidth()
    '
    ' SetColWidth Macro
    '

    '
    ActiveSheet.Next.Select
    Sheets(Array("EXPENSE & FTE SUMMARY", "SALARY FTE SUMMARY", "SALARY FTE DETAIL", _
    "HOURLY FTE SUMMARY", "HOURLY FTE DETAIL", "HOURLY EXPENSE SUMMARY BY DEPT", _
    "HOURLY EXPENSE DETAIL", "OT SUMMARY", "OT DETAIL")).Select
    Sheets("OT DETAIL").Activate
    Application.Goto Reference:="R6C1"
    Selection.Columns.AutoFit
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Columns.AutoFit
    End Sub

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    How about this? It autofits all columns in all selected worksheets

    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Windows(1).SelectedSheets
    wks.Columns.AutoFit
    Next


    Steve


  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Steve,

    The AutoFit now has adjusted to all text/values in any column rather than the dashes I use in the cells on row 6. Column A is now as wide as the report title I entered in A1 on the first sheet. In fact it appears that all width attributes in the workbook are now based on the best fit for each column of the first sheet in the workbook.

    The cells in Row 6 on each of the worksheets varies in width, so the width of column A could be different on each worksheet. Done manually, I can select all of the worksheets, then select row 6 and then step through the Format-Cells-AutoFit.

    Can the code you provided be modified to recognize the row of cells containing dashes on each of the sheets rather than basing them on the contents of the first worksheet in the workbook?

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    I also tried to loop through worksheets with this code:

    Sub LoopThroughWorkheets()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets

    Application.Goto Reference:="R6C1"
    Range(Selection, Selection.End(xlToRight)).Select
    Range(Selection, Selection.End(xlToRight)).Select
    Selection.Columns.AutoFit

    Next ws
    End Sub

    But that also only works on the leading worksheet in the workbook. In fact, it doesn't appear to have worked on any other sheet at all.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts
    I misunderstood your request. How about changing the line to:

    wks.Rows(6).Columns.AutoFit

    Steve

  6. #6
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Las Vegas, Nevada, USA
    Posts
    334
    Thanks
    8
    Thanked 0 Times in 0 Posts
    That's it. Works great.

    =======================
    Sub AdjColWidthForAllWorksheets()
    Dim wks As Worksheet
    For Each wks In ActiveWorkbook.Windows(1).SelectedSheets
    wks.Rows(6).Columns.AutoFit
    Next

    End Sub
    =======================

    Thank you Steve

Posting Permissions

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