This is perfect - thank you so much! I appreciate your time and help.
I did run into a runtime error when executing the macro:
"Excel cannot insert the sheets into the destination workbook, because it contains fewer rows and column than the source workbook. To move or copy the data to the destination workbook, you can select the data, and then use the Copy and Paste commands to insert it into the sheets of another workbook."
My source workbooks have ~4400 rows and go into column DR.
Here is my code:
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
'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)
'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
Bookmarks