Autofit columns in multiple worksheets

[munchen]

I have an excel workbook that has multiple worksheets. When I open the workbook I always want a range to be cleared for example:

Range("A6:E300").Select
Selection.Clear

Also once I click on Refresh All to refresh each worksheet (each worksheet runs a query) I want each column in each worksheet to autofit.

Is there a simple way to do this in VBA?

 

[PHV]

1) use the Workbook_Open event procedure
2) use the AdjustColumnWidth property of each of your QueryTable (NO VBA needed)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[munchen]

PHV,

Thanks for your reply. Bear with me I'm new to VBA.

As I mentioned I've got multiple worksheets.

In the 1st sheet (the sheet that is displayed when opened in excel) should I use the workbook_open_event procedure?

Private Sub Workbook_Open()

Sheets.Select
Columns("A:J").EntireColumn.AutoFit

End Sub

What do you mean by using the AdjustColumnWidthProperty for each Query? Do you mean I have to amend the adjustcolumnwidth property on each sheet (there are quite a lot)?

 

[SkipVought]

In the QueryTable for each worksheet, the Data Range Properties has a checkbox for Auto adjusting columns.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]