How to remove alerts

[mmertt900]

Hi Everybody,

I've created a code that allows me to select a excel file and open it (with application.getopenfilename) and copy some of the datas to my main workbook which has the code. I wanted to after necessary copying ends,it would automatically close the opened excel file but it shows an alert and I couldn't find how to remove it with a code. Can anybody tell me how to remove an alert automatically ? (I've tried Application.DisplayAlerts = False but it didn't work)

 

[davesexcel]

There should be a code to clear the clipboard.

What is the code you are using?

 

[Dossfm0q]

I don't sure

Application.DisplayAlerts = False

'Code hear

Application.DisplayAlerts = True

 

[Fluff]

Add this to your code, after you have pasted the data.

Application.CutCopyMode = False

 

[mmertt900]

There should be a code to clear the clipboard.

What is the code you are using?

Here code is below for your easy references.

Dim MainWorbook As String
Dim FilePath As String
Dim FileName As String

MainWorbook = ActiveWorkbook.Name

ChDir ActiveWorkbook.Path

FilePath = Application.GetOpenFilename(Title:="Please select TDR file.", FileFilter:="(*.xls),*.xls")

    If FilePath = "False" Then
    MsgBox "You did not select a file!", vbExclamation
    Exit Sub
    End If

'+++Opening select file

Workbooks.Open FilePath
FileName = ActiveWorkbook.Name
Workbooks(FileName).Activate

'+++ Checking file format

If Workbooks(FileName).Sheets(1).Range("AA7") <> "KUMPORT" And _
Workbooks(FileName).Sheets(2).Range("B14") <> "Container No" And _
Workbooks(FileName).Sheets(2).Range("B14") <> "Container No" Then
MsgBox "Selected TDR file's format is not correct. Please check again."
Workbooks(FileName).Close
Exit Sub
End If

'+++ Copying data from opened excel file to main excel

Workbooks(MainWorbook).Activate

Sheets(1).Range("B1").Value = Workbooks(FileName).Sheets(1).Range("AA10").Value & " " & _
Workbooks(FileName).Sheets(1).Range("BL10").Value

Sheets(1).Range("B4").Value = Workbooks(FileName).Sheets(1).Range("AC21").Value

Workbooks(FileName).Activate
Sheets(2).Select
Range(Range("B15"), Range("B15").End(xlDown)).Select
Selection.Copy
Workbooks(MainWorbook).Activate
Sheets(5).Select
Range("A3").PasteSpecial xlPasteValues

Workbooks(FileName).Activate
Sheets(2).Select
Range(Range("D15"), Range("D15").End(xlDown)).Select
Selection.Copy
Workbooks(MainWorbook).Activate
Sheets(5).Select
Range("B3").PasteSpecial xlPasteValues

Workbooks(FileName).Activate
Sheets(3).Select
Range(Range("B15"), Range("B15").End(xlDown)).Select
Selection.Copy
Workbooks(MainWorbook).Activate
Sheets(5).Select
Range("A2").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues

Workbooks(FileName).Activate
Sheets(3).Select
Range(Range("E15"), Range("E15").End(xlDown)).Select
Selection.Copy
Workbooks(MainWorbook).Activate
Sheets(5).Select
Range("B2").End(xlDown).Offset(1, 0).Select
Selection.PasteSpecial xlPasteValues

Workbooks(FileName).Close SaveChanges:=False 'Closing opened excel file

 

[mmertt900]

Add this to your code, after you have pasted the data.

Application.CutCopyMode = False

I've tried but still gives the same alert.

 

[Fluff]

Where did you put it?

 

[mmertt900]

Where did you put it?

Thank you. I realized that I put it in wrong place. Now it works without an alert.

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[davesexcel]

Here's a version that reduces the need to select before copy/paste

Sub Button1_Click()
    Dim MainWorbook As String
    Dim FilePath As String
    Dim FileName As String
    Dim wb As Workbook, sh1 As Worksheet, sh2 As Worksheet, sh3 As Worksheet
    Dim MnWB As Workbook, ws1 As Worksheet, ws5 As Worksheet
    
    Set MnWB = ActiveWorkbook
    Set ws1 = MnWB.Sheets(1)
    Set ws5 = MnWB.Sheets(5)

    ChDir MnWB.Path

    FilePath = Application.GetOpenFilename(Title:="Please select TDR file.", FileFilter:="(*.xls),*.xls")

    If FilePath = "False" Then
        MsgBox "You did not select a file!", vbExclamation
        Exit Sub
    End If

    '+++Opening select file
    Application.ScreenUpdating = False
    Set wb = Workbooks.Open(FilePath)
    Set sh1 = wb.Sheets(1)
    Set sh2 = wb.Sheets(2)
    Set sh3 = wb.Sheets(3)


    '+++ Checking file format

    If sh1.Range("AA7") <> "KUMPORT" And sh2.Range("B14") <> "Container No" Then
        MsgBox "Selected TDR file's format is not correct. Please check again."
        wb.Close
        Exit Sub
    End If

    '+++ Copying data from opened excel file to main excel



    ws1.Range("B1").Value = sh1.Range("AA10").Value & " " & sh1.Range("BL10").Value
    ws1.Range("B4").Value = sh1.Range("AC21").Value
 
 
    With sh2
        .Range(.Range("B15"), .Range("B15").End(xlDown)).Copy
        ws5.Range("A3").PasteSpecial xlPasteValues
        .Range(.Range("D15"), .Range("D15").End(xlDown)).Copy
        ws5.Range("B3").PasteSpecial xlPasteValues
    End With
        
    With sh3
        .Range(.Range("B15"), .Range("B15").End(xlDown)).Copy
        ws5.Range("A3").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
        .Range(.Range("E15"), .Range("E15").End(xlDown)).Copy
        ws5.Range("B3").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues
    End With
    
    Application.CutCopyMode = False
    wb.Close SaveChanges:=False                  'Closing opened excel file
End Sub