Well, I managed to get almost all the way through this with a lot of reading and even more trial and error. I've managed to put together code that will open the files, copy the rows, then paste the rows.

I just need to figure out how to filter out the rows I need, based on a start date and end date that I manually enter prior to running the macro. The only problem is, while it seems I should be able to run a simple For-Next loop to delete any rows that fall out of my date range, I can't get my head around how to express dates in an Autofilter statement (or any statement, for that matter). Is it done as mm/dd/yyyy, three separate variables, or am I just making it too hard? Seems like it shouldn't be that difficult, but I'm stuck.

Any help is greatly appreciated. Thanks! Code below


Sub Copy_Files()
' Macro to open a set of Excel files, copy rows within a specified range,
' paste each file's rows into a corresponding sheet in a different workbook,
' and lastly, delete all rows that do not fall within a specific date range.
'
'
Dim RowNum As Integer
Dim SurveySheetName As String
Dim SurveyFileName As String
RowNum = 2
'
' RowNum is the row number from the Survey_Names worksheet page, which lists the filename prefixes
' and each survey's individual Worksheet name in the current workbook.
'
    For RowNum = 2 To 49
    SurveyFileName = Range("Survey_Names!C(RowNum)")
    SurveySheetName = Range("Survey_Names!B(RowNum)")
    Workbooks.Open Filename:="C:\Users\GBishop3\Documents\KPI\" & SurveyFileName & ".xls"
    Rows("3:703").Select
    Selection.Copy
    Windows("Level_1_KPI_Base_Guy.xlsm").Activate
    Sheets("A10_LBs").Select
    Rows("12:712").Select
    Rows("12:712").Delete
    Rows("12:12").Select
    ActiveSheet.Paste
    Next RowNum
End Sub