Code for Merge Multi Sheets using user input

**Liz_Biz** · 12-19-2013, 01:22 AM

DEF.xlsx IJK.xlsx Master File.xlsx ABC.xlsxHeello,:)

I have used below code to merge multiple excel workbooks to one. I want to modify this code so that code will ask user to select file instead of folder path with a msg box "Browse XXX file" . This should be for 3 times as I have three files to import from diffrent locations. I also want sheet name should be on browsed workbook name.

Please let me know if more info required.

Sub Merge2MultiSheets()
Dim wbDst As Workbook
Dim wbSrc As Workbook
Dim wsSrc As Worksheet
Dim MyPath As String
Dim strFilename As String
    
    Application.DisplayAlerts = False
    Application.EnableEvents = False
    Application.ScreenUpdating = False
    MyPath = "C:\MyPath" ' change to suit
    Set wbDst = Workbooks.Add(xlWBATWorksheet)
    strFilename = Dir(MyPath & "\*.xls", vbNormal)
    
    If Len(strFilename) = 0 Then Exit Sub
    
    Do Until strFilename = ""
        
            Set wbSrc = Workbooks.Open(Filename:=MyPath & "\" & strFilename)
            
            Set wsSrc = wbSrc.Worksheets(1)
            
            wsSrc.Copy After:=wbDst.Worksheets(wbDst.Worksheets.Count)
            
            wbSrc.Close False
        
        strFilename = Dir()
        
    Loop
    wbDst.Worksheets(1).Delete
    
    Application.DisplayAlerts = True
    Application.EnableEvents = True
    Application.ScreenUpdating = True
    
End Sub

Thanks,
Liz:)

**xlbiznes** · 12-19-2013, 02:23 AM

Add to you code from the line :

 MyPath = "C:\MyPath" ' change to suit

you will end up with this

 
MyPath = "C:\MyPath" ' change to suit
my_path = InputBox("Please Enter The Folder Path ", "User Input", "C:\MyPath")
'you might need to check if this is a valid path or else you will end up encountering an error.

**Liz_Biz** · 12-19-2013, 09:18 PM

Thanks for reply,

I want to use something like below code.

MyPath = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xlsx),*.xlsx", Title:="Select Current Week's Report to import", MultiSelect:=False)

This should appear 3 times as , I want to upload 3 reports in Master file.

One more thing, Code given in my first query giving me best sheet names as Sheet1 , Sheet2 etc. but I want them on files uploaded. As attached.

**Liz_Biz** · 12-22-2013, 09:14 PM

Please don't leave me in middle of the river. Help Help !!

**xlbiznes** · 12-23-2013, 03:32 AM

Here you go. Hope we have crossed the river now.

**Liz_Biz** · 12-25-2013, 09:41 PM

We are very close to cross. Now I have confidence that I am in good hands .

This code is superb. I have tried to do some add on in this but failed.

Could you add msg box before select each file so that user know that what file need to select please?
e.g. if x=1 then MsgBox "Select file ABC", vbExclamation etc

Also, If possible master file should be separate from code file.

Thanks in advance!!

Liz.

**fredlo2010** · 12-25-2013, 11:16 PM

Hi perhaps you can add it to the title for the file picker window. I added this to xlbiznes's amazing code.

    For x = 1 To 3 'loop through 3 times
    
    'Case for the File name
    Select Case x
        Case 1
            sFileName = "ABC"
        Case 2
            sFileName = "DEF"
        Case 3
            sFileName = "UK"
    End Select
    MyPath = Application.GetOpenFilename(FileFilter:="Excel workbooks (*.xlsx),*.xlsx", Title:="Select File " & _
                                         Chr(34) & sFileName & Chr(34) & " to import", MultiSelect:=False)
    
    If MyPath = False Then ' exit sub if file not selected
        MsgBox "File Not Selected - Exiting System", vbExclamation
        Exit Sub
    End If

Thanks

**xlbiznes** · 12-26-2013, 04:14 AM

@Liz_Biz,

A quick question ?

Every time this process happens, do we need to create a new master file and over write the existing one ?

**Liz_Biz** · 12-26-2013, 09:35 AM

Originally Posted by xlbiznes

@Liz_Biz,

A quick question ?

Every time this process happens, do we need to create a new master file and over write the existing one ?

I want to create this and want to do some further data extraction on each sheets. Yes this will be new master file each time.

**xlbiznes** · 12-29-2013, 05:46 AM

@Liz_Biz,

here is the updated file.

**Liz_Biz** · 12-29-2013, 06:54 AM

@ xlbiznes. You are superb and have excellent VBA skills.

@ fredlo. Thank you for looking into this.

**xlbiznes** · 12-29-2013, 07:08 AM

Glad i could help and thank you for the reps.

**fredlo2010** · 12-29-2013, 10:46 AM

Hi guys,

One thing I just noticed in this section

   If MyPath = "" Then ' exit sub if file not selected
    MsgBox "File Not Selected - Exiting System", vbExclamation
    Exit Sub

Its giving me an error if no file is selected ( Cancel Button pressed). I changed it to "False" instead of ""

   If MyPath = False Then ' exit sub if file not selected
    MsgBox "File Not Selected - Exiting System", vbExclamation
    Exit Sub

Thanks

**xlbiznes** · 12-29-2013, 03:18 PM

@fredlo2010,

you are right, this will take care if the user clicks cancel.

Code for Merge Multi Sheets using user input

LinkBack

Thread Tools

Rate This Thread

Display

Hybrid View

Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Re: Code for Merge Multi Sheets using user input

Thread Information

Users Browsing this Thread

Similar Threads

Macros (fix) with criteria input from user then output with mail merge

[SOLVED] Refreshing/Recalculating Sheets based on user input or switching between sheets

multi-user input spreadsheet, is it possible?

Multi User and readonly sheets

Pause macro for user input then search for user's answer across multiple sheets

Bookmarks

Bookmarks

Posting Permissions