MXCG2016
10-24-2019, 06:04 AM
Hi All,
I have been researching online and found a piece of VBA code which I have edited to match my needs (working). The code is setup to look at a folder location and merge all excel .xlsx files into a master sheet. However, I am having difficulty amending the code to only read and copy a specific sheet from the source files. I only need to copy one specific sheet ("Sheet2") from each file :-). Appreciate the help:
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")
Dim wrksht As String
wrksht = "Test"
Set dirObj = mergeObj.Getfolder("\C:\D (file://\\C$\D)")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("D2:Z" & Range("D65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Master").Activate
Range("A85536").End(xlUp).Offset(2, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
Thanks
I have been researching online and found a piece of VBA code which I have edited to match my needs (working). The code is setup to look at a folder location and merge all excel .xlsx files into a master sheet. However, I am having difficulty amending the code to only read and copy a specific sheet from the source files. I only need to copy one specific sheet ("Sheet2") from each file :-). Appreciate the help:
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")
Dim wrksht As String
wrksht = "Test"
Set dirObj = mergeObj.Getfolder("\C:\D (file://\\C$\D)")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
Range("D2:Z" & Range("D65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Master").Activate
Range("A85536").End(xlUp).Offset(2, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
End Sub
Thanks