I have tried to write code to autofit Cols A to D of all csv files in folder C:\Journals, but cannot get this to work
Sub AutofitColumnsCSVFiles()
Dim MyFolder As String
Dim MyFile As String
Dim wb As Workbook
Dim ws As Worksheet
' Define the folder path
MyFolder = "C:\Journals\"
' Disable screen updating to speed up the process
Application.ScreenUpdating = False
' Check if the folder exists
If Dir(MyFolder, vbDirectory) = "" Then
MsgBox "Folder not found!", vbExclamation
Exit Sub
End If
' Loop through CSV files in the folder
MyFile = Dir(MyFolder & "*.csv")
Do While MyFile <> ""
' Open the CSV file
Set wb = Workbooks.Open(MyFolder & MyFile)
' Loop through all worksheets in the workbook
For Each ws In wb.Worksheets
' Set the width of columns A to D to autofit
ws.Columns("A:D").AutoFit
Next ws
' Save and close the workbook
wb.Close SaveChanges:=True
' Get the next CSV file
MyFile = Dir
Loop
' Enable screen updating
Application.ScreenUpdating = True
MsgBox "Columns A to D autofitted for all CSV files in the folder.", vbInformation
End Sub
It would be appreciated if someone could kindly amend my code
Bookmarks