Clear the file formatting before importing another workbook

Question

I have code that selects Excel files according to the amount of work.

I want to clear various formats, such as hyperlinks and fonts, before importing another workbook.
Because there was a problem with importing, the program couldn't notify me.
Too many formats.

Sub add()
    
    'On Error Resume Next

    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Dim FNames As Variant
    Dim Cnt As Long
    Dim MstWbk As Workbook
    Dim ws As Worksheet

    Set MstWbk = ThisWorkbook

    FNames = Application.GetOpenFilename(fileFilter:="Excel files (*.xls*), *.xls*", MultiSelect:=True, Title:="choose Files")
    If Not IsArray(FNames) Then Exit Sub
    
    ThisWorkbook.ActiveSheet.Cells.ClearHyperlinks

    For Cnt = 1 To UBound(FNames)
        Set ws = Workbooks.Open(FNames(Cnt)).Sheets(1)
        ws.Copy After:=MstWbk.Sheets(MstWbk.Sheets.Count) 
        MstWbk.Sheets(MstWbk.Sheets.Count).Name = Left(ws.Parent.Name, InStr(2, ws.Parent.Name, ".") - 1)
        ws.Parent.Close False
    Next Cnt

    MsgBox "Import complete!", vbInformation
    
    Application.ScreenUpdating = True
    Application.DisplayAlerts = True

End Sub

Answer 1

Pls try.

    ' ** clear all formats on master workbook
    ThisWorkbook.ActiveSheet.Cells.ClearFormats  
    For Cnt = 1 To UBound(FNames)
        Set ws = Workbooks.Open(FNames(Cnt)).Sheets(1)
        ' OR
        ' Workbooks.Open FNames(Cnt) 
        ' Set ws = ActiveWorkbook.Sheets(1)

        ' ** clear all formats on the imported workbook
        ' ws.Cells.ClearFormats  
        ws.Copy After:=MstWbk.Sheets(MstWbk.Sheets.Count)
        MstWbk.Sheets(MstWbk.Sheets.Count).Name = Left(ws.Parent.Name, InStr(2, ws.Parent.Name, ".") - 1)
        ws.Parent.Close False
    Next Cnt

Microsoft documentation:

Range.ClearFormats method (Excel)