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
```
Oct 10, 2022 in Others 284 views

## 1 answer to this question.

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.
answered Oct 11, 2022 by
• 63,700 points

## Convert Feet to meters in excel with macro for entire column

You can convert Feet to Kilometers using ...READ MORE

## Creating a function in excel VBA to calculate the average point in a circular set of numbers

I used the following code to determine ...READ MORE

## Creating a chart in Excel that ignores #N/A or blank cells

A dynamic data series will be used ...READ MORE

## How do I use the Indirect Function in Excel VBA to incorporate the equations in a VBA Macro Function

Try this: Sub Test() Dim str As String: str ...READ MORE

## Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

## How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

## Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

## How to copy a formula horizontally within a table using Excel VBA?

Hi so basically, create an adjacent column ...READ MORE