Merge multiple workbooks to one workbook if file are in same folder

[Muhammad Hussaan]

Hello, I have multiple workbooks in a folder along with main file Name MOM.xlsm to merge data (find code below) I am having some problem with vba code below, it works fine in my computer as path is drive E and folder is "MOM" , i want to make this macro working independent of drive E. So that i can keep in USB storage and make this file workable. Kind suggest what change i should do to make it workable. Below is the codeSub Mergemom()Dim wbk As WorkbookDim sht As WorksheetDim shtt As WorksheetDim sheetfirst As WorksheetDim sheetsecond As WorksheetDim sheetthird As WorksheetDim wbk2 As WorkbookSet wbk2 = ThisWorkbookDim Filename As StringDim Path As StringPath = "E:\MOM" 'CHANGE Folder PATHFilename = Dir(Path & "*.xlsx")'OPEN EXCEL FILESDo While Len(Filename) > 0 'IF NEXT FILE EXISTS THENSet wbk = Workbooks.Open(Path & Filename)wbk.ActivateFor Each shtt In wbk.Worksheetswbk.ActivateVar = shtt.Nameshtt.SelectRange("A2").SelectRange(Selection, Selection.End(xlToRight)).SelectRange(Selection, Selection.End(xlDown)).SelectSelection.CopyWindows("MOM.xlsm").ActivateDim lr As Integer'Var = sht.NameSheets(Var).Selectlr = wbk2.Sheets(Var).Cells(Rows.Count,1).End(xlUp).RowCells(lr + 1, 1).SelectActiveSheet.PasteNextwbk.Close TrueFilename = DirLoopEnd Sub

 

[Macropod]

This post is a duplicate of: https://www.mrexcel.com/forum/excel...tiple-workbooks-one-workbook-same-folder.html

Kindly read Mr Excel's policy in Rule 12 against asking questions about the same topic in multiple threads: http://www.mrexcel.com/forum/board-announcements/99490-forum-rules.html

Thread closed. You may continue the discussion in your other thread.

Also, when posting code, please use the code tags, indicated by the # button on the posting menu. Without them, your code loses much of whatever structure it had. In the case of your posts on this topic, all the code is run together with no apparent structure whatsoever.