Merging multiple workbooks that contain identical tabs:Animal

I am looking to merge multiple workbooks that contain the same three tabs.

Tabs:

Animal
Food
Place

Hi,

Have a look here?

http://www.excelforum.com/excel-prog...g-headers.html

'WORKBOOKS W/MULTIPLE SHEETS MERGED INTO SHEETS IN CONSOLIDATION WORKBOOK

Here's a macro for collecting data from all sheets in all files in a specific folder merging into matching sheets.

Workbooks to Sheets - Part 4

The parts of the code that need to be edited are colored to draw your attention.

I'm pretty sure all you need to edit is the the fPath string to properly point to where your source files are located. Then edit the fName code to .xlsx.

Hi Heather,

Are you using this code? Can you post your changes to this code

Option Explicit
 
Sub ConsolidateSheetsFromWorkbooks()
'Author:    Jerry Beaucaire, ExcelForum.com
'Date:      1/5/2011
'Summary:   Open all files in a folder and merge data (stacked) on all
'           sheets into main wb matching the sheet names.
'           Assumes all sheets with titles exist in main book and
'           data sheets data starts at row 2
Dim wbData As Workbook, wbMain As Workbook
Dim wsMain As Worksheet, wsData As Worksheet
Dim LR As Long, NR As Long
Dim fPath As String, fName As String
 
Set wbMain = ThisWorkbook
 
                                   'if files are stored in separate directory edit fPath
fPath = ThisWorkbook.Path & "\"     'don't forget the final \
 
fName = Dir(fPath & "*.xls")        'start looping through files one at a time
Application.ScreenUpdating = False
 
    Do While Len(fName) > 0
        If fName <> ThisWorkbook.Name Then
            Set wbData = Workbooks.Open(fPath & fName)
            For Each wsData In wbData.Worksheets
                Set wsMain = wbMain.Sheets(wsData.Name)
                NR = wsMain.Range("A" & Rows.Count).End(xlUp).Row + 1
                With wsData
                    LR = .Range("A" & .Rows.Count).End(xlUp).Row
                    .Range("A2:A" & LR).EntireRow.Copy wsMain.Range("A" & NR)
                End With
            Next wsData
 
            wbData.Close False
        End If
 
        fName = Dir                 'queue up next filename
    Loop
 
Application.ScreenUpdating = True
End Sub

Yes I am.

 
Set wbMain = ThisWorkbook
 
                                   'if files are stored in separate directory edit fPath
fPath =  "U:\2010\TEST\"     'don't forget the final \
 
fName = Dir(fPath & "*.xlsx")        'start looping through files one at a time
Application.ScreenUpdating = False

I'm no networking guru, but you changed to drive U: which sounds like a network drive to me. Perhaps that could be giving you issues. Do you know the network address of the same drive? Not the locally mapped version? That might work better.

Look at my signature, it shows the proper way to wrap your code in tags. Please edit your post above to use the proper tags. You could also edit out all the code you didn't change since it's already posted in this thread...keep things tidy.

When it stops and you DEBUG, hang your mouse over the wsData.Name variable and tell me what the current value is that appears.

So, the problem is occurring when the macro tries to set the wsMain sheet variable to the sheet Sheets("Advance - Datacomm") in the workbook that is your wbMain, there is something not matching exactly. This is usually hidden spaces or something like that...

"nevermind"

"nevermind"

Just edit the post above and delete the content, replace it with a "nevermind" comment.

Then go to post #1... click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.