Merge wbs to Master wb - Control the startup message about updating linked workbooks and copy only values not formulas
I have to copy data wbs to a master wb. The simple macro below works fine.
Q1: First, in the Data wbs there are links to another wbs. I don't know how to control the startup message about updating linked workbooks in Excel when I run the macro. I have changed the Excel Options to avoid the message but no better result. How can I change the macro that it copies from closed data wbs?
Q2: Second, I would like copy only the values, no formulars to the master wb.
Sub simpleXlsMerger()Dim bookList As WorkbookDim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As ObjectApplication.ScreenUpdating = FalseSet mergeObj = CreateObject("Scripting.FileSystemObject")'change folder path of excel files hereSet dirObj = mergeObj.Getfolder("C:\Users\Adam\Desktop\Verbundaktion\")Set filesObj = dirObj.FilesFor Each everyObj In filesObjSet bookList = Workbooks.Open(everyObj)Range("A3:IV" & Range("A65536").End(xlUp).Row).CopyThisWorkbook.Worksheets(1).ActivateRange("A65536").End(xlUp).Offset(1, 0).PasteSpecialApplication.CutCopyMode = FalsebookList.CloseNextEnd Sub
Please try below:
Open in new window