MISSION IMPOSSIBLE ---> Multiple File Import

Just before the end of your macro:


This will take all sheets and consolodate them into one by cut and paste.

So, out of my entire code (see below), I just have to add your code to the end, right? If this is not correct, please assist...thanks

Option Explicit
Sub CopyWorksheets2()
Dim filenames As Variant
Dim strActiveBook As String
Dim strSourceDataFile As String
Dim wSht As Worksheet, wSht2 As Worksheet
Dim allwShts As Sheets, allwShts2 As String
Dim Sheetname As String, response As String
Dim counter As Integer, intResponse As Integer

Application.DisplayAlerts = False
intResponse = MsgBox("This macro will copy all worksheets from selected files to the current workbook. Continue?", vbOKCancel, "Copy Worksheets to Current File")

If intResponse = vbOK Then
strActiveBook = ActiveWorkbook.Name

filenames = Application.GetOpenFilename("Excel Files (*.xls),*.xls", , , , True)
On Error GoTo 1000
If filenames = False Then Exit Sub
1000
On Error GoTo 0

counter = 1

On Error GoTo quit

response = MsgBox("Retain Original Worksheet Names? (If No, then each copied worksheet will be tiven the name of the Excel file from which it came.", vbYesNo, "Copy Sheets")
Application.ScreenUpdating = False
If response = vbNo Then
While counter <= UBound(filenames)


'Opens the selected files
Workbooks.Open filenames(counter)
strSourceDataFile = ActiveWorkbook.Name
If strSourceDataFile <> strActiveBook Then
Set allwShts = Worksheets
For Each wSht In allwShts
Workbooks(strSourceDataFile).Activate

If wSht.Visible = True Then
Sheets(wSht.Name).Select
Sheets(wSht.Name).Copy before:= _
Workbooks(strActiveBook).Sheets(1)
Sheetname = Left(strSourceDataFile, Len(strSourceDataFile) - 4)
Workbooks(strActiveBook).Activate
'Check to see if a Sheet already has the name
If SheetExists(Sheetname) = True Then
ActiveSheet.Name = Left(strSourceDataFile, Len(strSourceDataFile) - 4) & "(" & wSht.Index & ")"
Else
ActiveSheet.Name = Left(strSourceDataFile, Len(strSourceDataFile) - 4)
End If
End If

Next wSht


Workbooks(strSourceDataFile).Activate
ActiveWorkbook.Close

'displays file name in a message box
MsgBox strSourceDataFile & " Has Been Processed", vbOKOnly + vbInformation, "File Processed"
'End If
'increment counter
counter = counter + 1
Wend
Else
While counter <= UBound(filenames)
'Opens the selected files
Workbooks.Open filenames(counter)
strSourceDataFile = ActiveWorkbook.Name
If strSourceDataFile <> strActiveBook Then
'Copy all worksheets except "Specifications"
Set allwShts = Worksheets
For Each wSht In allwShts
If wSht.Visible = True Then
Workbooks(strSourceDataFile).Activate
Sheets(wSht.Name).Select
Sheets(wSht.Name).Copy before:= _
Workbooks(strActiveBook).Sheets(1)
End If
Next wSht
Workbooks(strSourceDataFile).Activate
ActiveWorkbook.Close

'displays file name in a message box
MsgBox strSourceDataFile & " Has Been Processed", vbOKOnly + vbInformation, "File Processed"
End If
'increment counter
counter = counter + 1
Wend
End If

quit:
If Err <> 0 Then
MsgBox "An Error Occurred Trying to open the File. Please close any open Excel files and try again", vbOKOnly + vbExclamation, "File Open Error"
On Error GoTo 0
End If
End If
Set allwShts = Nothing
Application.ScreenUpdating = True
Application.DisplayAlerts = True

End Sub
Private Function SheetExists(sname) As Boolean
'Returns TRUE if sheet exists in the active workbook
Dim x As Object
On Error Resume Next
Set x = ActiveWorkbook.Sheets(sname)
If Err = 0 Then SheetExists = True _
Else SheetExists = False
On Error GoTo 0
End Function
End Function

You can really help yourself and everone else if you do not make duplicate posts, which this is of this:
http://www.mrexcel.com/board2/viewtopic.php?t=86702

And if you answer the questions asked of you on that original post so everyone, including you, can be on the same page. You did not answer my question #3 or #4, and you did not explain if your Admin Assistants will select which sheets they want to consolidate, or if it would be all sheets in that workbook.

Please stick to the same thread.