I did some digging and kept finding code where the user chooses files to merge then it loops through and merges them. Thought I'd throw this question out there.
I currently have code that allows the user to choose which files (workbooks) to merge into one sheet in a master workbook, which further manipulates the data. There are 8 individual source files that get merged together, minus the header in each. These files reside in the same file path each week and always have the same file names, they just get overwritten each week. For example C:\Users\MergeIL.csv, C:\Users\MergeNC.csv, C:\Users\MergeTN.csv etc.......
Since these files are always in the same place and always have the same name, how could I hard code that in the macro, to remove the step of having the user choose the files?
Here's the current code I'm using. Thank you in advance.
Sub UJMergePivot() Dim wsMaster As Workbook, xlsFiles As Workbook Dim Filename As String Dim File As Integer Dim r As Long Dim StartTime As Double Dim MinutesElapsed As String StartTime = Timer 'Section above combines multiple markets on one sheet MsgBox "Select the MERGE files" r = 0 With Application .ScreenUpdating = False .EnableEvents = False End With ' below checks if theres leftover data and clears it before continuing Worksheets("Sheet1").Activate If Application.ActiveSheet.UsedRange.Rows.Count > 1 Then ActiveSheet.Rows("2:" & Application.ActiveSheet.UsedRange.Rows.Count).ClearContents End If ' Below continues with selecting and merging files With Application.FileDialog(msoFileDialogOpen) .AllowMultiSelect = True .Title = "Select files to process" .Show If .SelectedItems.Count = 0 Then Exit Sub Set wsMaster = ActiveWorkbook For File = 1 To .SelectedItems.Count Filename = .SelectedItems.Item(File) If Right(Filename, 4) = ".csv" Or Right(Filename, 5) = ".xlsx" Then Workbooks.Open Filename, 0, True Set xlsFiles = ActiveWorkbook r = wsMaster.Sheets("Sheet1").UsedRange.Rows.Count xlsFiles.ActiveSheet.Range("A3:E2000").Copy Destination:=wsMaster.Sheets("Sheet1").Range("A" & r).Offset(1, 0) 'xlsfiles.Sheets("Sheet1").Range and so on xlsFiles.Close SaveChanges:=False 'close without saving End If Next File 'go to the next file and repeat the process End With Set wsMaster = Nothing Set xlsFiles = Nothing With Application .ScreenUpdating = True .EnableEvents = True End With