I have found code on the site that is similar to what I need, but have been unable to adapt any of it to make it work. Please help me find a way to:
(1) Search a single folder for .xls files
(2) Copy the first sheet of each file (there is only one sheet in each file) and paste them into individual sheets in a master workbook with the original formatting and sheet name on the tabs. They do not have to be pasted as values.
The closest thing I have found (that works for me) is the very basic code below, but this requires all of the workbooks to be open and the names of sheets and workbooks within the code. I'm working with 32 workbooks, so I had hoped I could find a way to automate the whole process. Finally, it needs to be compatible with Excel 2003. Thank you.
Sub CopySheetToOtherWbk()
Dim CopyFromBook As Workbook
Dim CopyToWbk As Workbook
Dim ShToCopy As Worksheet
Set CopyFromBook = Workbooks("WRDW.xls")
Set ShToCopy = CopyFromBook.Worksheets("WRDW")
Set CopyToWbk = Workbooks("NOP.xls")
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
Set CopyFromBook = Workbooks("WTOK.xls")
Set ShToCopy = CopyFromBook.Worksheets("WTOK")
Set CopyToWbk = Workbooks("NOP.xls")
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
Set CopyFromBook = Workbooks("WTVY.xls")
Set ShToCopy = CopyFromBook.Worksheets("WTVY")
Set CopyToWbk = Workbooks("NOP.xls")
ShToCopy.Copy After:=CopyToWbk.Sheets(CopyToWbk.Sheets.Count)
End Sub