Good Morning,
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