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