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

[patricevease]

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!

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.

[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.

[patricevease]

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.

[snb]

Thank you

[SamT]

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:

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

Sub Test_TableAddress()
Dim TableRng As Range

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