Pick directory and merge 2 files into 1 workbook

[Sandler]

Hi everyone,

I am looking for a VBA code that would allow me to pick a directory

(The files have the same name every month, but the folder changes every month)

then it would find the 2 files that have a specific string of text in the title (so wildcards are involved).

So for example, somewhere in the filename, it would have to have the following:

file 1: Cats-Are-Cute
file 2: Dogs-Are-Too

and merge those sheets into a new workbooks named analysis.

[Sandler]

OK, I think I got the first part, the option to pick the Directory.

Sub GetFolder()
Dim FolderName As String
 With Application.FileDialog(msoFileDialogFolderPicker)
   .AllowMultiSelect = False
   .Show
   On Error Resume Next
   FolderName = .SelectedItems(1)
   Err.Clear
   On Error GoTo 0
 End With
End Sub

[Sandler]

bump...

[mancubus]

something to play with:

Sub vbax_61347_select_folder_open_2wbs_merge_into_1wb()

    Dim fPath As String
    Dim wb1 As Workbook, wb2 As Workbook

    With Application.FileDialog(msoFileDialogFolderPicker)
        If .Show = -1 Then
            fPath = .SelectedItems(1) & "\"
        Else
            Exit Sub
        End If
    End With

    Set wb1 = Workbooks.Open(fPath & "*Cats-Are-Cute*.xls?")
    Set wb2 = Workbooks.Open(fPath & "*Dogs-Are-Too*.xls?")

    wb1.Worksheets("cat").Copy
    wb2.Worksheets("dog").Copy After:=ActiveWorkbook.Worksheets(1)
    'change cat and dog to suit

    ActiveWorkbook.SaveAs fPath & "analysis.xlsx", 51 'save as new file in the same folder
    '51 = xlsx, 52 = xlsm, 50 = xlsb, 56 = xls

    wb1.Close False
    wb2.Close False
    'close previously opened files without saving them

End Sub

[Sandler]

Thank you so much, Mancubus!

I will give it a try.