VBA Expert Wanted! >> Populate Table by Cycling Through Sheets

Hi all.

I am struggling to even begin on this script. I have a similar one which I will post below with an explanation as to what it does. But for now I'll explain what I am looking for help with.
I am trying to create a VBA for this one as the nature of it is different.

The idea is that the code will be assigned to a button on worksheet "BusDev" for example. It will then clear a table (simple clear contents code) and repopulate it by cycling through all the other sheets on the worksheet, excluding sheets that are told to be excluded from. Next it will select all the rows of data from a specific table (to put into context it's the last of 3 tables on each worksheet) and copy them onto another 'overview' table on another worksheet... One problem I am having here is that the number of rows in the original table is dynamic and therefore I can't use a simple Range code (I think that was the hard part at least!)

Now it needs to take the selected data and paste it into the overview table to repopulate it... The problem here is that the data from each sheet needs to be pasted below any other data from different sheets that is being cycled through in order for the data to not overlap.

If this seems too confusing please look at the code below which does something similar!

Code:

---

Sub TablePopulate()


Dim sheetcount As Integer
Dim i As Integer


Sheets("HighViewRemakeTest").Activate
Range(Cells(2, 3), Cells(18, 1500)).ClearContents


sheetcount = ActiveWorkbook.Worksheets.Count


clientcounter = 3


For i = 1 To sheetcount


    If ActiveWorkbook.Sheets(i).Name <> "HighViewRemakeTest" And ActiveWorkbook.Sheets(i).Name <> "ClientTemplate" And ActiveWorkbook.Sheets(i).Name <> "ClientTemplateBackup" And ActiveWorkbook.Sheets(i).Name <> "Lists" And ActiveWorkbook.Sheets(i).Name <> "BusDev" Then
   
        ActiveWorkbook.Sheets(i).Activate
       
        Cells(2, 3).Activate
        clientname = Cells(2, 3)
       
        Cells(2, 3).Copy
       
       
        datarow_start = 1
        datarow_end = 1
        For j = 1 To 1000
       
            If Cells(j, 3) = "Status Average" Then
           
                datarow_start = j + 1
           
               
                Exit For
               
            End If
       
        Next j
       
        If datarow_start <> 1 Then
       
            For g = datarow_start To datarow_start + 50
           
                If Cells(g, 3) = "" Then
               
                On Error Resume Next
                       
                    datarow_end = g - 1
                    Exit For
                   
                End If
           
            Next g
           
            Range(Cells(datarow_start, 3), Cells(datarow_end, 3)).Select
            Selection.Copy
       
        End If
       
        Sheets("HighViewRemakeTest").Activate
       
        Cells(2, clientcounter) = clientname
       
        Cells(3, clientcounter).Select
       
        If datarow_start <> 1 Then
            Selection.PasteSpecial xlPasteValues
        End If
       
             
        clientcounter = clientcounter + 1
   
    End If


Next i


Sheets("HighViewRemakeTest").Select
Range("A1").Activate
Application.CutCopyMode = False
Range("A1").Select


End Sub

---

The code above will clear a Table on sheet "HighViewRemakeTest" and repopulate it by selecting data from column C of each of the worksheets that it has cycled through. It will then paste it on the HighViewRemakeTest sheet table into a column 1 to the right (to ensure no overlaps of data) under the heading of the value of cell C2. The way that it selects it's data is by finding "Status Average" in Column C and selecting all rows containing data below that. This is different to the VBA I am trying to create because I need data by rows instead of column and it will be selecting a whole range that is dynamically changing it's position.

I understand this is greatly confusing and I probably haven't explained it well, but please let me know if you have any parts of the code that could help out and if you have any questions!

Thank you for the help in advance.

My first question: can you please adapt the title of this thread ?
Remove all elements that do not relate to the content of your question.
Abstain from any use of exclamation marks.

Quote:

---

Originally Posted by snb

My first question: can you please adapt the title of this thread ?
Remove all elements that do not relate to the content of your question.
Abstain from any use of exclamation marks.

---

Done, sorry.

Thank you :clap:

Quote:

---

One problem I am having here is that the number of rows in the original table is dynamic and therefore I can't use a simple Range code

---

Off the top of my head:

Code:

---

Function TableAddress(ShtName As String) As String

Const FirstColumnOfTable As Long = 1

Dim StartRng As Range
Dim EndRng As Range

Set EndRng = Sheets(ShtName).Cells(Rows.Count. FirstColumnOfTable).End(xlUp).End(xlToRight)
Set StartRng = EndRgn.End(xlUp).End(xlUp).Offset(1) 'Omit Offset if table has no header over last column

TableAddress = Range(StartRng, EndRng).Address
End Function

---

Code:

---

Sub Test_TableAddress()
Dim TableRng As Range

Set TableRng = Sheets("Sht1").Range(TableAddress("Sht1"))
End Sub

---