How to auto-adjust column widths on all sheets using VBA?

To auto-adjust the column widths on all sheets of an Excel workbook using VBA, you can create a macro that loops through each worksheet and uses the `AutoFit` method. Below is an example of how you could write this macro:

Sub AutoFitAllSheets()

    Dim ws As Worksheet

    ' Loop through each worksheet in the active workbook
    For Each ws In ThisWorkbook.Worksheets
        ' Autofit columns in the current worksheet
        ws.Cells.EntireColumn.AutoFit
    Next ws

    ' Optional: Alert the user that the task is complete
    MsgBox "All columns in every sheet have been auto-fitted.", vbInformation

End Sub

Steps to Add and Run the VBA Code:

Open the Excel Workbook in which you want to adjust the column widths.
Press `ALT + F11` to open the VBA editor.
Go to `Insert > Module` to create a new module.
Copy and Paste the Code into the code window of the module you just created.
Close the VBA Editor and return to Excel.
To Run the Macro:

Press `ALT + F8`, select `AutoFitAllSheets`, and click `Run`.
Alternatively, you can assign this macro to a button or execute it from the developer tab if you have it enabled.

This macro will loop through all the worksheets in the active workbook, adjusting all column widths to fit their contents. Remember to save your workbook with macros enabled (`.xlsm`) to retain the macro for future use.

facebooklinkedin