VBA - cells overwritten when looping through multiple workbooks and saving to master workbook

Question

I have a folder with multiple .xlsx workbooks, all of which contains the same 3 tables on one page with the exact same layout but different values within each table. As each workbook contains 3 tables on the same page I have a VBA script which loops through the files in the folder and specifically pulls the necessary data/cells from each of the 3 tables and writes it to the active or 'master' workbook. The problem I am having is that although it is writing the data correctly and in the format I need it i.e. Table 1 data on row 1, Table 2 data on row 2, Table 3 data on row 3, etc.. for each workbook- each new workbook it reads is overwriting the previous workbook data. So in the end I am left with only the data from the last table the script reads. Here is my code so far:

Sub getDataFromWbs()

Dim wb As Workbook, ws As Worksheet
Set fso = CreateObject("Scripting.FileSystemObject")
Set fldr = fso.GetFolder("path-to-directory-with-multiple-workbooks")

For Each wbFile In fldr.Files

    If fso.GetExtensionName(wbFile.Name) = "xlsx" Then
    
        Set wb = Workbooks.Open(wbFile.Path)
        
        For Each ws In wb.Sheets

                'Some table data to be laid out from A1 to C1 in the master workbook

                ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = ws.Cells(4, 1)
                ThisWorkbook.Sheets("Sheet1").Cells(1, 2) = ws.Cells(5, 29)
                ThisWorkbook.Sheets("Sheet1").Cells(1, 3) = ws.Cells(5, 32)
                
                'Table Data to be Laid out From D1 to F1 in the master workbook

                ThisWorkbook.Sheets("Sheet1").Cells(1, 4) = ws.Cells(18, 1)
                ThisWorkbook.Sheets("Sheet1").Cells(1, 5) = ws.Cells(18, 2)
                ThisWorkbook.Sheets("Sheet1").Cells(1, 6) = ws.Cells(18, 4)

                'Some table data to be laid out from A2 to C2 in the master workbook

                ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = ws.Cells(8, 15)
                ThisWorkbook.Sheets("Sheet1").Cells(2, 2) = ws.Cells(8, 22)
                ThisWorkbook.Sheets("Sheet1").Cells(2, 3) = ws.Cells(9, 15)
                
                'Table Data to be Laid out From D2 to F2 in the master workbook

                ThisWorkbook.Sheets("Sheet1").Cells(2, 4) = ws.Cells(18, 20)
                ThisWorkbook.Sheets("Sheet1").Cells(2, 5) = ws.Cells(18, 23)
                ThisWorkbook.Sheets("Sheet1").Cells(2, 6) = ws.Cells(18, 25)

                'Some table data to be laid out from A3 to C3 in the master workbook

                ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = ws.Cells(19, 29)
                ThisWorkbook.Sheets("Sheet1").Cells(3, 2) = ws.Cells(19, 30)
                ThisWorkbook.Sheets("Sheet1").Cells(3, 3) = ws.Cells(19, 32)
                
                'Table Data to be Laid out From D3 to F3 in the master workbook

                ThisWorkbook.Sheets("Sheet1").Cells(3, 4) = ws.Cells(19, 38)
                ThisWorkbook.Sheets("Sheet1").Cells(3, 5) = ws.Cells(19, 40)
                ThisWorkbook.Sheets("Sheet1").Cells(3, 6) = ws.Cells(19, 43)
        
              Next ws
        
        wb.Close
        
    End If
    
Next wbFile

End Sub

So far, I have it down to pulling the correct file, reading the data from the first table in the first workbook and placing it in the first row of the master workbook, then the second table of the first workbook and placing that data in the 2nd row, then the third etc.. After which it closes the first workbook and opens the second and goes through the same process with the next empty row and so on through x amount of workbooks in the directory.

I imagine my issue is with the fact that I am explicitly stating with

 ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = ws.Cells(4, 1)
 ...

to write the data it pulls to Cell(x, x) which I am currently explicitly specifying where to put it. Which is why with every workbook it iterates, it will just overwrite the cell data that currently exists. I have tried setting 'y' as:

y = ThisWorkbook.Sheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row + 1

and then utilizing it as:

 ThisWorkbook.Sheets("Sheet1").Cells(y, 1) = ws.Cells(4, 1)

But that does not allow me to specify when one table ends to cut to a new row for the next table.

I am new to VBA but would appreciate any and all assistance!

Thank you

BDra · Accepted Answer · 2020-07-15T06:15:02.240

You have found 90% of the solution on your own. Rows 1, 2, 3 keep getting overwritten because your original code uses those static row references. To solve it you need to use a variable instead (called y in your example).

The only missing piece is to control the row reference when you need to shift one row down. You can simply use y + 1 for the second table, and y + 2 for the third table:

ThisWorkbook.Sheets("Sheet1").Cells(y + 1, 1).Value = ws.Cells(4, 1).Value

[Expanding on answer in response to Gideon B's comment:]

You need to manage the variable row reference through the loops of your code. Let me call the variable WriteRow for clarity. Let's also increment the value of WriteRow in separate lines of code for better readability. In pattern you will want to do something along these lines:

'Initialize your variable at the outset
WriteRow = 1

For Each wbFile In fldr.Files

    If fso.GetExtensionName(wbFile.Name) = "xlsx" Then
    
        Set wb = Workbooks.Open(wbFile.Path)
        
        For Each ws In wb.Sheets
            ThisWorkbook.Sheets("Sheet1").Cells(WriteRow, 1) = SomeTable1Data
            '... write as much data as you need to this row ...

            '... then increment WriteRow:
            WriteRow = WriteRow + 1
            ThisWorkbook.Sheets("Sheet1").Cells(WriteRow, 1) = SomeTable2Data
            '... etc., incrementing WriteRow as needed

            'Make sure to end by incrementing WriteRow in preparation for the next loop
            WriteRow = WriteRow + 1
        
        Next ws
        
        wb.Close
        
    End If
    
Next wbFile

Thank you for the prompt response. I suppose a question I have then is what happens when I pull the next workbook with 3 more tables? If Table 1, 2, and 3 are written to rows 1,2, and 3 in the master workbook and the code iterates and opens the next workbook in the directory, will those next 3 tables then be written to rows 4, 5, and 6 and so on as I iterate through x number of workbooks in the directory? — Gideon B, Jul 15 '20 at 05:36
This did it! Thank you - you helped me identify where I was having issues and that was knowing to increment the variable, which makes total sense now. +1 for prompt responses and explanation. — Gideon B, Jul 15 '20 at 06:23

VBA - cells overwritten when looping through multiple workbooks and saving to master workbook

1 Answers1