Last row variable not incrementing because For Loop is not populating the table.

[Mars84]

Why does my code run without errors and loop through the files in the selected folder, as planned, but the values from the source workbooks don't display in the summary table (table remains blank). Because no text is displayed in the summary table, the "find last row" variable does not increment and move to the next line.

I have included an image of the summary table.
Below is the code I have compiled.

Sub GenerateReportsSummary()

Application.ScreenUpdating = False

Dim DiaFoldr As FileDialog
    Set DiaFoldr = Application.FileDialog(msoFileDialogFolderPicker)
        DiaFoldr.AllowMultiSelect = False
        DiaFoldr.Title = "Select source folder containing all Reports"
        DiaFoldr.Show

        SrcRepPath = DiaFoldr.SelectedItems(1) ' Variable used to store source reports folder path.
                
    Set DiaFolder = Nothing

Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder(SrcRepPath) ' fldr represents folder path where files are located by using selected folder path.

    For Each wbFile In fldr.Files
    
    If fso.GetExtensionName(wbFile.Name) = "xlsx" Then ' Only use .xlsx files for imports.
        
        Set wb = Workbooks.Open(wbFile.Path) ' wb temporarily represents the opened workbook.
        
        For Each ws In wb.Sheets ' for each sheet in the temporarily opened workbook.
        y = ThisWorkbook.Sheets(3).cells(Rows.count, 1).End(xlUp).Row + 1 ' Original Last Row: Get row below last used row.
        
        If y > 3 Then
                    
            ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 1) = ws.cells(14, 3) ' Name reference "C14".
            ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 2) = ws.cells(25, 11) ' length reference "K25".
            ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 3) = ws.cells(21, 11) ' Average slope in m/km reference "K21".
            ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 4) = ws.cells(20, 11) ' Average slope in m/m reference "K20".
            ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 5) = ws.cells(6, 4) ' Reference name "D6".
            ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 6) = CDate(ws.cells(3, 12)) ' Date created reference "L3".
            ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 7) = wbFile.Path ' Full file path.
            y = y + 1
                        
            Else
            
        End If
                
        Next ws
        
        wb.Close False
                
    End If
    
Next wbFile

Application.ScreenUpdating = True

End Sub

The ws.cells references store the data from the source workbooks but the cells in the summary sheet does not display the values.
My summary sheet remains blank.
Any help will be appreciated.

 

[Fluff]

I suspect that y=2 & therefore the code never goes into the loop.

 

[Mars84]

Thank you for your feedback.

I played around with the code and rewrote a section of the loop code as follows:

 If y <= 3 Then

MsgBox "The Row Count is: " & y

Else

ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 1) = ws.cells(14, 3) ' Catchment name reference "C14".
ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 2) = ws.cells(25, 11) ' Watercourse length reference "K25".
ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 3) = ws.cells(21, 11) ' Watercourse Average slope in m/km reference "K21".
ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 4) = ws.cells(20, 11) ' Watercourse Average slope in m/m reference "K20".
ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 5) = ws.cells(6, 4) ' Reference name "D6".
ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 6) = CDate(ws.cells(3, 12)) ' Date created reference "L3".
ThisWorkbook.Sheets("3. EAS Reports Summary").cells(y, 7) = wbFile.Path ' Full file path.

y = y + 1

End If

After running the revised code, I saw the message box indicated that the row count stayed at Row3 but the loop was executing for each file in the selected folder. I then revisited the layout of the summary sheet and unmerged the merged columns in the headings (for the Name, Length, Reference , Date and Source Data Path).

If I remove the merged cells, then the loop works correctly and imports the data as it should.
I will use it in this way and include code that formats the summary sheet and merges the required titles after the data has been imported to avoid issues with the row counts.

Thank you again for your assistance.

 

[Fluff]

Glad you sorted it & thanks for the feedback.