Results 1 to 14 of 14

Code for Merge Multi Sheets using user input

Threaded View

  1. #1
    Registered User
    Join Date
    12-19-2013
    Location
    London
    MS-Off Ver
    Excel 2010
    Posts
    53

    Code for Merge Multi Sheets using user input

    DEF.xlsxIJK.xlsxMaster File.xlsxABC.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:)
    Last edited by Liz_Biz; 12-19-2013 at 01:35 AM.

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Macros (fix) with criteria input from user then output with mail merge
    By Nicklebach in forum Excel Programming / VBA / Macros
    Replies: 4
    Last Post: 09-24-2012, 10:16 AM
  2. [SOLVED] Refreshing/Recalculating Sheets based on user input or switching between sheets
    By SonOfOdin in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-24-2012, 09:48 PM
  3. multi-user input spreadsheet, is it possible?
    By gavster in forum Excel General
    Replies: 1
    Last Post: 11-07-2011, 01:37 PM
  4. Multi User and readonly sheets
    By bugs78 in forum Excel General
    Replies: 6
    Last Post: 09-22-2011, 03:44 AM
  5. Pause macro for user input then search for user's answer across multiple sheets
    By sassy2 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 11-04-2009, 03:55 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1