Excel VBA to Pull Non-Null rows from another workbooks named range

Question

New to the site with weak VBA skills. Hoping I can find some assistance with something I have been struggling with for days. I have found many examples that are close, and cant seem to marry them together. I am using Excel 2007. I have a "Summary_Reports" WB, as well as several other workbooks named by employee (eg. "Jim.xls", "bob.xls", etc). Each of the employee workbooks has a named range "caps" sourced from sheet "Tasks". This named range in each employee wb is the same width (number of columns) but can vary in height (number of rows), and some of the rows may be empty. Trying to setup a macro in "Summary_Reports" wb that will open each of the employees wb, copy the named range "caps", and insert/paste only the rows of that range containing data in the first column, to the "Report" sheet in the "Summary_Reports" wb. I assumed the easiest paste method would just be to pick a cell at the top and always insert those rows there, that way each employee would just get inserted above the previous one starting at the same spot. That way no counting or looking for the last populated row on the sheet. I attempted at first to open "Jim.xls" and copy the named range directly from the workbook, but had little success and a lot of trouble with syntax. So I ended up with the code below that pulls the employee sheet into "Summery_Reports" and then copies the named range from itself instead of another wb. Would probably end up deleting those sheets at the end.

What I have started below kinda works, but the data validation I know isn't correct. Correct me if I am wrong but it is only checking the top left cell of "caps" right; if there are contents, it pastes all of "caps", and if that single cell is empty, it pastes nothing. How do I correct the validation to check the first column of every row and also how do I get it to just give me the rows with data?

Also, I know there is a better way to get the "caps" data directly from each of the employee wb, without importing the sheet first. If that can be done easily, I would be very interested in any advice in that regard.

If you are kind enough to help me, please dumb it down as much as possible as I am really interested in actually knowing what the code does and not just copying and pasting. Thank you in advance.

Sub Import_Sheets()
Application.Workbooks.Open ("jim.xls")
Workbooks("jim.xls").Activate
Sheets("Tasks").Copy After:=Workbooks("Summary_Report.xlsm").Sheets("Report")
Application.Workbooks("Jim.xls").Close

'Go to newly copied sheet and name it.
ActiveSheet.Name = "jim"

'Copy the "caps" named range.
With Range("Caps")
    If .Cells(1, 1).Value = "" Then
    Else
        Range("Caps").Select
        Selection.Copy
        Sheets("Report").Select
        Range("B2").Select
        Selection.Insert Shift:=xlDown
    End If
End With
End Sub

Answer 1

Commented code:

Sub Import_Sheets()

    'Declare variables
    Dim wsDest As Worksheet 'This is the sheet that data will be pasted to
    Dim rngCaps As Range    'This is used to determine if there is a named range "Caps"
    Dim rngFound As Range   'This is used to loop through the first column in the named range "Caps"
    Dim rngSearch As Range  'This is used to determine where to search
    Dim rngCopy As Range    'This is used to store the rows with data that will be copied
    Dim strFirst As String  'This is used to store the first cell address to prevent an infinite loop
    Dim i As Long           'This is used to loop through the selected workbooks

    'Create an "Open File" dialogue for the user to choose which files to import
    With Application.FileDialog(msoFileDialogFilePicker)
        .Filters.Clear                          'Clear existing filters (if any)
        .Filters.Add "Excel Files", "*.xls*"    'Filter for Excel files
        .AllowMultiSelect = True                'Allow user to select multiple files at a time with Shift or Ctrl

        If .Show = False Then Exit Sub  'Pressed cancel, exit macro

        'The destination is this workbook, sheet 'Report'
        Set wsDest = ActiveWorkbook.Sheets("Report")

        'Turn off screenupdating.  This prevents "Screen Flickering" and allows the code to run faster
        Application.ScreenUpdating = False

        'Begin loop through selected files
        For i = 1 To .SelectedItems.Count

            'Open a selected file
            With Workbooks.Open(.SelectedItems(i))

                'Attempt to find a sheet named 'TimeEntry' with a named range "Caps"
                On Error Resume Next
                Set rngCaps = .Sheets("TimeEntry").Range("Caps")
                On Error GoTo 0 'Remove the On Error Resume Next condition

                'Was it able to set rngCaps successfully?
                If Not rngCaps Is Nothing Then
                    'Yes, proceed to find rows with data
                    'Define rngSearch which will be used to find rows with data
                    Set rngSearch = Intersect(rngCaps, rngCaps.Cells(1).MergeArea.EntireColumn)

                    'Use a find loop to only get rows with data
                    'We can do this by utilizing the wildcard *
                    'The .Resize(, 1) will make sure we are only looking in the first column of rngCaps
                    Set rngFound = rngSearch.Find("*", rngSearch.Cells(rngSearch.Cells.Count), xlValues, xlWhole)

                    'Was there a cell found with data?
                    If Not rngFound Is Nothing Then
                        'Yes, record this first cell's address to prevent infinite loop
                        strFirst = rngFound.Address

                        'Also start storing the rows where data was found
                        Set rngCopy = rngFound

                        'Begin the find loop
                        Do
                            'Add found rows to the rngCopy variable
                            Set rngCopy = Union(rngCopy, rngFound)

                            'Advance loop to the next cell that contains data
                            Set rngFound = rngSearch.Find("*", rngFound, xlValues, xlWhole)

                        'Exit the loop when we are back to the first cell
                        Loop While rngFound.Address <> strFirst

                        'Copy the rows with data and paste them into the next available row in the destination worksheet
                        Intersect(rngCaps, rngCopy.EntireRow).Copy wsDest.Cells(wsDest.Rows.Count, "B").End(xlUp).Offset(1)

                        'Clear rngFound and rngCopy to get ready for next workbook
                        Set rngFound = Nothing
                        Set rngCopy = Nothing
                    End If

                    'Clear rngCaps to get ready for next workbook
                    Set rngCaps = Nothing
                End If

                'Close this opened workbook and don't save changes
                .Close False
            End With

        'Advance to the next workbook that was selected
        Next i

        'Re-enable screen updating
        Application.ScreenUpdating = True

        'Object variable cleanup
        Set wsDest = Nothing

    End With

End Sub