Creating macro in excel

Question

Please assist me in designing a macro that searches several workbook sheets for a specific column and displays the number of values in that column in a new sheet along with the sheet name.

I have 3 pages that are labelled A, B, and C. I tried the code below, however, it is not calculating based on the name of the column header.

I have columns like Company Company Company ABC ABC ABC DEF GHI
JKL

Wanted Output: Sheet name Count A 1 B 3 C 2

Sub Click() Dim sh As Worksheet, ws As Worksheet, LstRw As Long, x, s As String Dim rng1 As Range

Set ws = Sheets.Add
ws.Name = "Report"
s = "A"
For Each sh In Sheets
    If sh.Name <> ws.Name Then
        With sh

              x = Application.WorksheetFunction.CountA(.Range("A:A"))
            With ws
                LstRw = .Cells(.Rows.Count, "A").End(xlUp).Row + 1
                .Cells(LstRw, 1) = sh.Name
                .Cells(LstRw, 2) = x

            End With
        End With
    End If
Next sh

End Sub

Answer 1

The Macro Recorder is used to record macros, and Excel uses your actions to generate VBA code that you may later edit as necessary.

See:
MSDN : Recording a Macro to Generate Code, and,
MSDN : Revising Recorded Visual Basic Macros.