asked on

How to Preserve Column Width Formatting After Data Refresh in Excel 2013

I'm stumped!

Using Excel 2013, I have the following Macro:

Sub Macro7()
'
    Sheets("Full DataSet").Select
    Cells.Select
    ActiveWorkbook.RefreshAll
    Range("A5").Select
    Sheets("Parish Council").Select
    Cells.Select
    Selection.Columns.AutoFit
    Range("A5").Select
End Sub

Open in new window

If I do the commands manually, that is:

Select the Worksheet

Refresh All the Data

Press <Alt> OCA (to do AutoWidth)

The commands work. BUT in a macro, zippo, nada, zilch, nothing. Refresh works but the columnwidth will not go back.

Any thoughts would be greatly appreciated.

Louis

ASKER CERTIFIED SOLUTION

Wilder1626

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Wilder1626

At the end, your full macro can look like:

ActiveWorkbook.RefreshAll
Worksheets("Parish Council").Columns("A:AA").AutoFit

Open in new window

This is if the columns you want to autofit are A to AA. You can adjust.

Also the Refreshall will be done.

Can you give it a try?

LCSLouisJJames

ASKER

Perfect. Excellent answer and it worked. Thanks.

Wilder1626

I'm glad i was able to help.

Have you also tried my last answer?

You can simplify the full macro like this:

ActiveWorkbook.RefreshAll
Worksheets("Parish Council").Columns("A:AA").AutoFit

Open in new window