Need macro to merge data from external workbooks into one master

I have been looking for a solution to a project I am working on. I want to copy all data contained on the second worksheet in each workbook located in the same directory into one master workbook. Each external workbook's data would be pasted into its own sheet inside the master workbook. I am then planning on having a master sheet inside the master workbook that will reference this data and manipulate into a common template I utilize.

A problem I have been running into trying out other solutions in this forum is that the external worksheets I am cycling through have their own macros inside them. So I am assuming I will need a solution that either turns off macros when opening the external worksheets or does not open the external worksheets at all.

Thoughts?

I came across an issue that I cannot seem to figure out. The macro has been working beautifully for only one of the types of files I need it to pull in to the master workbook. All I have added to the macro from the above are nested case statements for correctly identifying the type of spreadsheet I have and the manager it has been submitted for. I have gone through the macro step by step with the files that are not being copied in and no errors are displayed but the target sheet is never copied into the master workbook. However, it is being moved into the "Imported" folder as specified in the macro.

    Case InStr(1, shtAdd, name7Str, vbTextCompare) <> 0

        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name 7 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name 7 HC"

        End Select

End Select
            
        '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

The case for searching for "BOW" in the name works for those spreadsheets. The case for "HC" evaluates to true for those type of spreadsheets and shtAdd is renamed as specified (verified during debug). The macro opens the target file, goes to the second worksheet, seemingly copies the data and pastes it in the master workbook and then closes the file. After I finish all the steps though, there aren't any new worksheets in the master workbook as there should be. Help!

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
Dim bowStr As String
    bowStr = "BOW"
Dim hcStr As String
    hcStr = "HC"
Dim name1Str As String
    name1Str = "Name1"
Dim name2Str As String
    name2Str = "Name2"
Dim name3Str As String
    name3Str = "Name3"
Dim name4Str As String
    name4Str = "Name4"
Dim name5Str As String
    name5Str = "Name5"
Dim name6Str As String
    name6Str = "Name6"
Dim name7Str As String
    name7Str = "Name7"
Dim name8Str As String
    name8Str = "Name8"

Dim pos As Integer

'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)
                
Select Case True

    Case InStr(1, shtAdd, name1Str, vbTextCompare) <> 0
    
        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name1 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name1 HC"

        End Select
        
    Case InStr(1, shtAdd, name2Str, vbTextCompare) <> 0

        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name2 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name2 HC"

        End Select

    Case InStr(1, shtAdd, name3Str, vbTextCompare) <> 0

        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name3 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name3 HC"

        End Select

    Case InStr(1, shtAdd, name4Str, vbTextCompare) <> 0

        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name4 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name4 HC"

        End Select

    Case InStr(1, shtAdd, name5Str, vbTextCompare) <> 0

        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name5 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name5 HC"

        End Select

    Case InStr(1, shtAdd, name6Str, vbTextCompare) <> 0

        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name6 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name6 HC"

        End Select

    Case InStr(1, shtAdd, name7Str, vbTextCompare) <> 0

        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name7 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name7 HC"

        End Select

    Case InStr(1, shtAdd, name8Str, vbTextCompare) <> 0

        Select Case True
            Case InStr(1, shtAdd, bowStr, vbTextCompare) <> 0
                shtAdd = "Name8 BOW"
            
            Case InStr(1, shtAdd, hcStr, vbTextCompare) <> 0
                shtAdd = "Name8 HC"

        End Select

End Select
            
        '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

Thanks for the help.

I am working on trying to get you example files. The ones I will be (and am) working with are protected workbooks and I am not able to edit them.

However, I tried to manually copy the content from the problem workbook into the master workbook and the attached picture is the error I got. Do you know of any way to bypass or fix this error during runtime since these will all be protected workbooks I will be working with? My initial thought on the matter is that I will need to have the owner update the template being used so this error doesn't happen.

macro-error.JPG

OK, latest update (sorry I'm sort of live blogging this)...

1) I realize you already have

Application.DisplayAlerts = False

so that was a stupid question in my last post, sorry

2) I added the destination of the named range in the source workbook to the master file (a hidden tab) and went to hide it in the master file when I realized - the macro has been successfully adding the HC data in to the master file but hiding the worksheets! Dah! Why is it doing that though?

I can't thank you enough for your help and the use of your macro! It has been invaluable.

I did have one more question though - is it possible for the macro to find the worksheet with a specified name instead of an index of 2 from the slave workbook? I'm running into some inconsistencies from the submitted files I'm copying from so I need to set up an if statement and have it search by worksheet name for one of the file types.

This is the Parr I'm talking about changing:

   'Rename sheet and copy to target workbook
            wbData.Sheets(2).Name = shtAdd
            wbData.Sheets(2).Copy After:=wbkNew.Sheets(wbkNew.Sheets.Count)