Autofit Columns in an Array-CodePudding

Is there a way to autofit the column widths of new workbooks being created in an array? I have tried the standard ws.columns.AutoFit, but it is not working on the new workbooks before they are saved. This is the final loop for splitting off workbooks from one master file.

'Loop through the array of unique field values, copy paste into new workbooks and save
For ArrayItem = 1 To UBound(ArrayOfUniqueValues)
    ws.ListObjects("Data").Range.AutoFilter Field:=ColumnHeadingInt, Criteria1:=ArrayOfUniqueValues(ArrayItem)
    ws.Range("Data[#All]").SpecialCells(xlCellTypeVisible).Copy
    ***ws.Columns.AutoFit***
    Workbooks.Add
    Range("A1").PasteSpecial xlPasteAll
    ws.Columns.AutoFit
    ActiveWorkbook.SaveAs SavePath & ArrayOfUniqueValues(ArrayItem) & Format(Now(), "text") & ".xlsx", 51
    ActiveWorkbook.Close False
    ws.ListObjects("Data").Range.AutoFilter Field:=ColumnHeadingInt
Next ArrayItem

CodePudding user response：

When you use the method Workbooks.Add, a new workbook is created (and it becomes the active workbook), however your variable 'ws' is still referring to the source worksheet (not to the new worksheet on the new workbook), therefore ws.columns.autofit just autofits your source columns.

You can invoke the newly created worksheet's autofit:

   Range("a1").Worksheet.Columns.AutoFit

This should work because the newly added workbook (and the worksheet) has now the active/current worksheet, and using the range object without a qualifier (like you do) refers to the active worksheet.

It is arguably better practice to assign new variables to different worksheets/workbooks you create and use, to avoid confusion, like this:

   Dim wbTarget As Workbook, wsTarget As Worksheet
   Set wbTarget = Workbooks.Add
   Set wsTarget = wbTarget.Sheets(1)
   wsTarget.Range("a1") = "The value you want to set (or use PasteSpecial here)"
   wsTarget.Columns.AutoFit
   ... etc...
   set wsTarget=Nothing
   set wbTarget=Nothing

But, experiment to find what works best for you!