Good day!
I searched from the net for the VBA that helps to copy data from a number of files (xls, xlsx, htm, txt, etc) saved in the same folder to another workbook and found the following one. It worked well except a message says "There is a large of amount of information on the Clipboard. Do you want to be able to paste this information into another program later?" kept popping up and I needed to click "Yes", "No" or "Cancel" multiple times so that the macro could go on. I found out from the net that the second codes below could serve that purpose. How does it fit in the first code code to turn off the pop-up message? If the second code is not applicable, how do I code to bypass the pop up?
Sub ProcessAllFiles()
Dim sPath As String
Dim Wb As Workbook
Dim sFile As String
sPath = "C:\Temp\"
sFile = Dir(sPath & "*.xlsx")
Application.ScreenUpdating = False
Do While sFile <> ""
Set Wb = Workbooks.Open(sPath & sFile)
Sheets(1).Activate
ActiveSheet.UsedRange.Copy
Wb.Close SaveChanges:=False
Range("A" & Rows.Count).End(xlUp).Offset(1, 0).PasteSpecial Paste:=xlPasteAll
Application.CutCopyMode = False
sFile = Dir
Loop
Rows("1:1").Delete Shift:=xlUp
Application.ScreenUpdating = True
End Sub
application.displayalerts = false
ActiveWorkbook.Close savechanges:=false 'or true
application.displayalerts = true
Bookmarks