Sub MergeElogNowFiles()
'
' Macro1 Macro
' Macro recorded 12/05/2004 by David Marks
'
Dim myDir As String
Dim myDestFileName As String
Dim myDestSheetName As String
Dim myDestFileList As String

Dim mySrcFileName As String
Dim mySrcSheetName As String
Dim mySheetCount As Integer

Dim myCounter As Integer
Dim myFileCount As Integer
Dim myFileIndex As Integer
Dim myUsedLines As Integer
Dim myInsertLine As Integer

myDir = "C:\Documents and Settings\dm310493\My Documents\Projects\Dell\Logistics\ELogNow\"
myDestFileName = "_ELogNowMerged.xls"            'must exist
myDestSheetName = "Data"                        'must exist
myDestFileList = "FileList"                     'list of files - tab in destination file

myCounter = 1                                   '?????

Windows(myDestFileName).Activate                'return to destination filename - assumes already open
Sheets(myDestFileList).Select                    'return to source file list
myFileCount = ActiveSheet.UsedRange.Rows.Count  'specify number of files in list

myFileIndex = 0
myUsedLines = 0
myInsertLine = 0

For myFileIndex = 1 To myFileCount              'cycle through each file

Windows(myDestFileName).Activate                'return to destination filename - assumes already open
Sheets(myDestFileList).Select                   'return to source file list
Range("A" & myFileIndex).Select                 'start at A1, A2, A3, ... An
mySrcFileName = ActiveCell.FormulaR1C1          'get source file name
    
'open source file
    Workbooks.Open Filename:=myDir & mySrcFileName
    mySheetCount = Worksheets.Count             'determine qty of sheets
    
        For mySheetIndex = 1 To mySheetCount
        
            Windows(mySrcFileName).Activate     'return to source filename
            Sheets(mySheetIndex).Select         'select sheet 1, 2, ... to end
            myUsedLines = ActiveSheet.UsedRange.Rows.Count
            Range("A1:Z" & myUsedLines).Select  'select source data
            Selection.Copy
            
            mySrcSheetName = Sheets(mySheetIndex).Name      'get the name of the sheet
            
'return to destination to paste data
            Windows(myDestFileName).Activate    'return to destination filename
            Sheets(myDestSheetName).Select      'return to destination sheet
            myInsertLine = ActiveSheet.UsedRange.Rows.Count + 5
            Range("C" & myInsertLine).Select    'paste from column C
            ActiveSheet.Paste                   'paste
            Application.CutCopyMode = False     '??? I think this deselects section
            
            Range("A" & myInsertLine).Select    'copy / paste file name to related rows in col A
'******************
'            ActiveCell.FormulaR1C1 = mySrcFileName
            ActiveCell.FormulaR1C1 = Left([mySrcFileName], 6) & "_" & Mid([mySrcFileName], 24, 4) & Mid([mySrcFileName], 29, 2) & Mid([mySrcFileName], 32, 2)
            
            Selection.Copy
            Range("A" & myInsertLine & ":A" & myInsertLine + myUsedLines).Select
            ActiveSheet.Paste
            Application.CutCopyMode = False
            
            'Range("B" & myInsertLine).Select    'copy / paste sheet name to related rows in col A
            'ActiveCell.FormulaR1C1 = mySrcSheetName
            'Selection.Copy
            'Range("B" & myInsertLine & ":B" & myInsertLine + myUsedLines).Select
            'ActiveSheet.Paste
            'Application.CutCopyMode = False

            myCounter = myCounter + 1           'increment counter
        
        Next mySheetIndex

    Windows(mySrcFileName).Activate             'close source
    ActiveWorkbook.Close

Next myFileIndex                                'next file

Dummy = MsgBox("finished", vbOKOnly)            'send message when finished

End Sub