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
Pls try.
Microsoft documentation: