title: creating macro in excel
body: <p>Please help me on creating a macro which search multiple sheets in a workbook for a particular column and display the count of values in that column and display in a new sheet with sheet name and the count.</p>
  
  <p>Am having 3 sheets with the name A, B &amp; C. I have tried the below code, but it is not calculating based on the column header name.</p>
  
  <p>I have columns like </p>
  
  <p>Company     Company     Company
  ABC         ABC         ABC 
              DEF
  GHI<br>
  JKL     </p>
  
  <p>and the result should looks like</p>
  
  <p>Sheet name  Count
  A             1
  B             3
  C             2</p>
  
  <p>Sub Click()
      Dim sh As Worksheet, ws As Worksheet, LstRw As Long, x, s As String
      Dim rng1 As Range</p>
  
  <pre><code>Set ws = Sheets.Add
  ws.Name = "Report"
  s = "A"
  For Each sh In Sheets
      If sh.Name &lt;&gt; 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
  </code></pre>
  
  <p>End Sub</p>
  
  <p>Thanks</p>