To be an American member of the 9
4 Group you seriously could not figure out that the word "Ball" was just titles in a row (-:
Uh. . . nope. We speek an' tink VBA heer, and thet ain't VBA. I see from looking at the bottom of the page that people from 4 different countries have viewed your issue, possibly to see if they can help. You can continue to 'speak' as if we all fully understand your Game terminology, but it won't help your cause.
What does this code mean?
Sub ertert()
Dim x, i&, j&
With Sheets("Counter Totals")
x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
End With
For i = 1 To UBound(x)
If (x(i, 1)) = "Game" Then j = j + 1
If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) Then
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
.Areas(j)(.Areas(j).Count + 1, 1).Resize(, 91).Value = Application.Index(x, i, 0)
End With
End If
Next i
End Sub
Dim = Declare a variable so the Compiler can assign a memory location for the relevant value.
Dim x = Declare x as a Variant Type variable, which can be used as an array
Dim i&, j& = Declare i and j as Long Type variables, which can hold numbers large enough to Count Rows and Columns
x = .Range("A2:CM" & .Cells(Rows.Count, 1).End(xlUp).Row).Value
Set x = to a two dimensional array of all the values in the Range, where x(i) is the Row number and the second parameter, [x(i, 1),] is the first column in the i Row
For i = 1 to Ubound(x)
Upper Boundary of x is the last used Row number, (in column 1,) so it's the same as "For each Used Row on the Sheet in Column A"
IsNumeric(x(i, 1))) * (Len(x(i, 1))
The * symbol or Mutliplication symbol is, in this case, the same as the Boolean operator "And". This is because zero is Boolean False and all other numbers are Boolean True.
IsNumeric returns a Boolean if the value parameter [x(i, 1),] is a number
Len is the VBA String Length function
Len(x(i, 1) is not zero if x(i, 1) is not ""
If x(i, 1).value is zero, It is still numeric,(Boolean True,) but if Excel doesn't show zeros, the Length is also zero, (Boolean False.)
If (IsNumeric(x(i, 1))) * (Len(x(i, 1))) can be thought of as If IsNumeric And Not Zero
If (x(i, 1)) = "Game" Then j = j + 1
J is zero when Dimmed, so the first j encountered will be j = 1
If x(Row i, Column A) is a number other than zero.
With Sheets("Game" & x(i, 1)).Columns(1).SpecialCells(2)
With Sheets(GameNumber), Column A, Cells that are not Formulas
I presume that the Parameter Sets in the GameNumber sheet(s) have a blank row above them.
An Area is a rectangular set of used Cells bordered by empty Cells. Areas is a collection Object containing all such sets
.Areas(j)
The Jth set of used cell bordered by empty cells
.Areas(j)(.Areas(j).Count + 1, 1)
(.Areas(j).Count + 1, 1) looks like a Cells assignment, so .Areas(j).Count + 1 is the Row below that Area, and the Second Parameter is the Column Number
.Resize(, 91)
Resize that Cell on that game sheet to a Range 91 Columns Wide.
.Value = Application.Index(x, i, 0)
.