Looping through an array and editing worksheets

Question

After importing some csv files into my Excel workbook as individual sheets, I now need to do a little clean up on them (auto-sizing the columns and rows). There can be be up to a total of 20 different reports that get imported, but not all of them will be imported, simply because the report did not get generated.

Checking the site, I found a function that I think will work well, and it was part of a piece of code that was close to what I was looking for.

Sub Cleanup()
      
    Dim AllReportsArray As Variant
    
    AllReportsArray = Array("SiteDepartmentReport", "SiteCAGroupsReport", _
    "SiteInstantAlertFieldsGroupsReport", "SiteConfigurationParametersReport", "SiteEventSpecsReport", _
    "SiteGatewaysReport", "SiteCustomPropertiesReport", "SiteHealthcareZonesReport", "SiteCategoriesReport", _
    "SiteStructureReport", "SiteCustomizedValuesReport", "SiteInstantAlertFieldsReport", _
    "SiteBusinessStatusReport", "SiteUsersReport", "SiteRolesReport", "SiteExcitersReport", "SiteZonesReport", _
    "SitePatientListViewsReport")
    
    Dim report As Variant
        
    For Each report In Sheets(AllReportsArray)
      report.Activate
      report.Cells.Select
      report.Cells.EntireRow.AutoFit
      report.Cells.EntireColumn.AutoFit
    Next report

End Sub

As always, any help given is always appreciated. Thanks

Answer 1

Pls try.

Option Explicit
Sub Cleanup()
    Dim AllReportsArray As Variant
    AllReportsArray = Array("SiteDepartmentReport", "SiteCAGroupsReport", _
    "SiteInstantAlertFieldsGroupsReport", "SiteConfigurationParametersReport", "SiteEventSpecsReport", _
    "SiteGatewaysReport", "SiteCustomPropertiesReport", "SiteHealthcareZonesReport", "SiteCategoriesReport", _
    "SiteStructureReport", "SiteCustomizedValuesReport", "SiteInstantAlertFieldsReport", _
    "SiteBusinessStatusReport", "SiteUsersReport", "SiteRolesReport", "SiteExcitersReport", "SiteZonesReport", _
    "SitePatientListViewsReport")
    Dim report As Worksheet, i As Long
    For i = LBound(AllReportsArray) To UBound(AllReportsArray)
        Set report = Nothing        
        On Error Resume Next
        Set report = Sheets(AllReportsArray(i))
        On Error GoTo 0
        If Not report Is Nothing Then
            With report.UsedRange
                .EntireRow.AutoFit
                .EntireColumn.AutoFit
            End With
        End If
    Next
End Sub

---

• If you are going to cleanup all worksheets.

Sub Cleanup2()
    Dim report As Worksheet
    For Each report In ActiveWorkbook.Worksheets
        With report.UsedRange
            .EntireRow.AutoFit
            .EntireColumn.AutoFit
        End With
    Next
End Sub

---

• If you only want to cleanup some sheets, eg. all sheets' name starts with "Site". ( Same idea as @Siddharth Rout comment)

Sub Cleanup3()
    Dim report As Worksheet
    Const KEYWORD = "Site"
    For Each report In ActiveWorkbook.Worksheets
        If StrComp(Left(report.Name, Len(KEYWORD)), KEYWORD, vbTextCompare) = 0 Then
            With report.UsedRange
                .EntireRow.AutoFit
                .EntireColumn.AutoFit
            End With
        End If
    Next
End Sub