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

Mars84

New Member
Joined
Jun 11, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
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 (y) 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.

VBA Code:
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.
 

Attachments

  • Summary Sheet Sample.JPG
    Summary Sheet Sample.JPG
    53.2 KB · Views: 3

Excel Facts

Remove leading & trailing spaces
Save as CSV to remove all leading and trailing spaces. It is faster than using TRIM().

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,547
Office Version
  1. 365
Platform
  1. Windows
I suspect that y=2 & therefore the code never goes into the loop.
 

Mars84

New Member
Joined
Jun 11, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
Thank you for your feedback.

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

VBA Code:
 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

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
77,547
Office Version
  1. 365
Platform
  1. Windows
Glad you sorted it & thanks for the feedback.
 

Forum statistics

Threads
1,175,663
Messages
5,898,756
Members
434,729
Latest member
Tej92

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top