-1

I have a problem with a vba macro that i can't seem to find the answer to anywhere. Feels like i've tried everyting so i'll put the question out there to see if anyone here can help me :)

My macro loops through 50 woorkbooks that all have a "Firstpage" where the data from all the other data worksheets are summarized. In that Firstpage i have a table called "Tabell_1". The table has a header row (B4:F4) and then one row for each data worksheet in the workbook and the a sum row. We have decided to add a new column (column D) to the table to add in data from a specific cell in all the other worksheets (B4).

I now loop through the data worksheets to copy the value in B4 and then i want to paste that value to the first empty row in the table on the "Firstpage" (starting from the cell under the header). The method to find the last row that i use in other parts of the macro doesn't work, it gives me the first row after the sum row and then pastes the values under the table. The picture shows the table that i'm working with for one of the workbooks.

enter image description here

BigBen
  • 46,229
  • 7
  • 24
  • 40
Johanna Falkenstrand
  • 1
  • [Find the last row in a table](https://stackoverflow.com/questions/43631926/lastrow-and-excel-table). – BigBen May 24 '21 at 14:13
  • You can use Match() on the sheet name to locate the corresponding row in the table, then put the B4 value in ColD. It would help you to get useful suggestions if you included the current macro in your post. – Tim Williams May 24 '21 at 15:57

1 Answers1

1

Hej Johanna!

Assuming that you are using listobjects to manage the data I would just do this..

Sub Test2()

    Dim lo As ListObject
    Dim lr As ListRow
    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("Sheet1")
    Set lo = ws.ListObjects("Tabell_1")

    Count = 1

    For Each lr In lo.ListRows

        If lo.ListColumns("Test2").DataBodyRange(lr.Index) = "" Then
            lo.ListColumns("Test2").DataBodyRange(lr.Index) = Count
            Count = Count + 1
        End If
    
    Next lr

End Sub

This is will fill the first empty row in column Test2.

Hopefully you can use this example :)

MrMartin
  • 43
  • 4
  • Thank you for taking time to answer my question :) We want to find the last empty row in the column that we just added to Tabell_1 which is not the last row of the table if that makes sens. First time the macro goes through the loop the we want the value to be pasted in the cell just under the header and then go on until the whole column is filled with data. So yes i guess its the different cake we are looking for. – Johanna Falkenstrand May 24 '21 at 14:53
  • ok, got it, but, so you mean the first empty row, not the last empty row... the cell just under the header row is necessarily the first empty row... editing to put the value in the first empty row. :) – MrMartin May 25 '21 at 20:40
  • Hello Johanna, did this help? – MrMartin Jun 02 '21 at 11:31