troubleshooting Question

Merge wbs to Master wb - Control the startup message about updating linked workbooks and copy only values not formulas

Avatar of Adam Elsheimer
Adam ElsheimerFlag for Germany asked on
Microsoft OfficeMicrosoft ExcelVB ScriptVBA
7 Comments1 Solution154 ViewsLast Modified:
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.
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial


Sub simpleXlsMerger()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Application.ScreenUpdating = False
Set mergeObj = CreateObject("Scripting.FileSystemObject")
 
'change folder path of excel files here
Set dirObj = mergeObj.Getfolder("C:\Users\Adam\Desktop\Verbundaktion\")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
Range("A3:IV" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets(1).Activate
 
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub

Thank you.

Regards,

Adam

Source
ASKER CERTIFIED SOLUTION
Shums
Excel VBA Developer
Join our community to see this answer!
Unlock 1 Answer and 7 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 7 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros