My merge multiple workbooks within a folder VBA needs some fixing

[RPMdragon]

Hi guys,

I have attached a code that I tried to make along with some online help that would take all the data from multiple workbooks, copy all range, and paste it into my active workbook on "Jan17" tab shifted 6 columns down. Good thing is that it works, bad thing is that I am sure that it could be done in a better way... Any advice?

Sub Merge()
Dim bookList As Workbook
Dim mergeObj As Object, dirObj As Object, filesObj As Object, everyObj As Object
Dim UsedRng As Range
 
Set mergeObj = CreateObject("Scripting.FileSystemObject")
Set UsedRng = ActiveSheet.UsedRange
         
Set dirObj = mergeObj.Getfolder("\\aecorpaespsvm1\Operations\PhysicalDistribution\Transport\Outbound\00 - Master Tracker\Internal Tracker\")
Set filesObj = dirObj.Files
For Each everyObj In filesObj
Set bookList = Workbooks.Open(everyObj)
 
'change value inside worksheets() to the tab you wish to paste (aka - month)
Range("A2:H" & Range("A65536").End(xlUp).Row).Copy
ThisWorkbook.Worksheets("Jan17").Activate
     
Range("A65536").End(xlUp).Offset(1, 0).PasteSpecial
Application.CutCopyMode = False
bookList.Close
Next
 
  Worksheets("Jan17").Range("A2:H65536").Cut Range("F2")
   
End Sub

 

[Monty]

Hello RPMdragon

I found your provided code is really good to go...but make where ever jan17 as dynamic...God bless.

 

[RPMdragon]

Hey, thanks for the feedback Monty however, the reason I ask is because for some odd reason, if I try running another macro using as reference the new output it will behave oddly. My guess is because I am cut and pasting the output instead of having it go straight to where it should be. Any tips on that?

 

[Marc L]

Hi !

Use Dir and a direct Copy like I did in your previous thread …
​