+ Reply to Thread
Results 1 to 17 of 17

Need macro to merge data from external workbooks into one master

Hybrid View

  1. #1
    Registered User
    Join Date
    06-25-2013
    Location
    Jacksonville, FL
    MS-Off Ver
    Excel 2007
    Posts
    30

    Re: Need macro to merge data from external workbooks into one master

    This is perfect - thank you so much! I appreciate your time and help.

    I did run into a runtime error when executing the macro:

    "Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and column than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook."

    My source workbooks have ~4400 rows and go into column DR.

    Here is my code:

    Option Explicit
    
    Sub ConsolidateWBsToSheets2()
    'Author:     Jerry Beaucaire'
    'Date:       6/23/2010     (2007 compatible)
    'Summary:    Open all Excel files in a specific folder and copy
    '            one sheet from the source files into this master workbook
    '            naming sheets for the names of the source workbooks
    '            Move imported files into another folder
    
    Dim fName As String, fPath As String, fPathDone As String
    Dim LR As Long, NR As Long, shtAdd As String, ShtName As Worksheet
    Dim wbData As Workbook, wbkNew As Workbook
    
    'Setup
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        Application.DisplayAlerts = False
      
        Set wbkNew = ThisWorkbook
        wbkNew.Activate
      
    'Path and filename (edit this section to suit)
        fPath = ThisWorkbook.Path & "\Test\" 'remember final \ in this string
        fPathDone = fPath & "Imported\"     'remember final \ in this string
        On Error Resume Next
            MkDir fPathDone                 'creates the completed folder if missing
        On Error GoTo 0
    
        fName = Dir(fPath & "*.xlsm")                'listing of desired files, edit filter as desired
    
    'Import data from each found file
        Do While Len(fName) > 0
        'make sure THIS file isn't accidentally reopened
            If fName <> wbkNew.Name Then
            
            'This is the section to customize, what to copy and to where
            'Get name of workbook without extension
                shtAdd = Left(fName, InStr(fName, ".") - 1)
            'Open file
                Set wbData = Workbooks.Open(fPath & fName)
                
            'Rename sheet and copy to target workbook
                wbData.Sheets(2).Name = shtAdd
                wbData.Sheets(2).Copy After:=wbkNew.Sheets(wbkNew.Sheets.Count)
               
            'close source file
                wbData.Close False
            'move file to IMPORTED folder
                Name fPath & fName As fPathDone & fName
            'ready next filename, reassert the list since a file was moved
                fName = Dir(fPath & "*.xlsm")
            End If
        Loop
    
    ErrorExit:    'Cleanup
        Application.DisplayAlerts = True         'turn system alerts back on
        Application.EnableEvents = True          'turn other macros back on
        Application.ScreenUpdating = True        'refreshes the screen
    End Sub
    Last edited by sirhacksalot; 07-02-2013 at 09:15 AM.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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