PDA

View Full Version : VBA Expert Wanted! >> Populate Table by Cycling Through Sheets



patricevease
01-14-2016, 04:11 AM
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
01-14-2016, 04:39 AM
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
01-14-2016, 04:49 AM
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
01-14-2016, 11:05 AM
Thank you :clap:

SamT
01-14-2016, 12:13 PM
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