Create an Array and transpose array values to range

[Drawleeh]

Hello, I have some code that will loop through each worksheet in my workbook and then fetch the amount of rows and populate integer 'rowcount' with that number. How do I then take that integer, store all of those values in an array and then transpose that array onto a range so it looks something like this?

Where all the values are the number of rows on the worksheet and the worksheet name is titled above that value (as shown above). The worksheets are all in the same order as above.

Sub Totals()

Dim wrksheet As Worksheet
Dim x As Long
Dim rowcount As Integer


For Each wrksheet In ActiveWorkbook.Worksheets
    If wrksheet.Visible And Not wrksheet.Name = "Log" Then

        With wrksheet
       
                rowcount = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count
           
            End With

        End If
Next wrksheet
  

End Sub

 

[Fluff]

Try

Sub Totals()

Dim wrksheet As Worksheet
Dim x As Long
Dim rowcount As Integer
Dim Ary As Variant

ReDim Ary(1 To 2, 1 To ActiveWorkbook.Worksheets.Count)

For Each wrksheet In ActiveWorkbook.Worksheets
    If wrksheet.Visible And Not wrksheet.Name = "Log" Then

        With wrksheet
                x = x + 1
                Ary(1, x) = .b = Name
                Ary(2, x) = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count
           
            End With

        End If
Next wrksheet
Sheets("Sheet2").Range("A1").Resize(2, x).Value = Ary

End Sub

 

[Drawleeh]

Try

Sub Totals()

Dim wrksheet As Worksheet
Dim x As Long
Dim rowcount As Integer
Dim Ary As Variant

ReDim Ary(1 To 2, 1 To ActiveWorkbook.Worksheets.Count)

For Each wrksheet In ActiveWorkbook.Worksheets
    If wrksheet.Visible And Not wrksheet.Name = "Log" Then

        With wrksheet
                x = x + 1
                Ary(1, x) = .b = Name
                Ary(2, x) = .Range("B2", .Range("B" & .Rows.Count).End(xlUp)).Rows.Count
          
            End With

        End If
Next wrksheet
Sheets("Sheet2").Range("A1").Resize(2, x).Value = Ary

End Sub

I get method or data member not found error at .b? I'm not sure I understand what you're referencing with .b either to fix it.

 

[Fluff]

Not sure what happened there, but that should read

Ary(1, x) = .Name