Macro Creation in Excel

[Sivas]

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.
Thanks

 

[davesexcel]

You need more details, right now anybody who supplies an answer would just be guessing what you want.

here's my guess.
This will loop through the sheets and Count the "A"'s in column A. You would have to delete the new sheet if you wanted to run the code again

Sub Button1_Click()
    Dim sh As Worksheet, ws As Worksheet, LstRw As Long, x, s As String

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

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

 

[Sivas]

Dave, Thank you for your valuable answer. Please help me with little modifications as below, For example i have the sheet names as A,B,C and i wanted to search for particular column with name "Company" as below.

[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]A[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]B[/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]C[/TD]
[/TR]
[TR]
[TD="class: xl63, width: 64"]Company[/TD]
[TD][/TD]
[TD="class: xl63, width: 64"]Company[/TD]
[TD][/TD]
[TD="class: xl63, width: 64"]Company[/TD]
[/TR]
[TR]
[TD="class: xl64"]ABC [/TD]
[TD][/TD]
[TD="class: xl64"]ABC [/TD]
[TD][/TD]
[TD="class: xl64"]ABC [/TD]
[/TR]
[TR]
[TD="class: xl64"]DEF[/TD]
[TD][/TD]
[TD="class: xl64"] [/TD]
[TD][/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64"]GHI[/TD]
[TD][/TD]
[TD="class: xl64"] [/TD]
[TD][/TD]
[TD="class: xl64"]GHI[/TD]
[/TR]
[TR]
[TD="class: xl64"]JKL[/TD]
[TD][/TD]
[TD="class: xl64"]JKL[/TD]
[TD][/TD]
[TD="class: xl64"]JKL[/TD]
[/TR]
</tbody>[/TABLE]

and the result looks like below as ,

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Sheet Name[/TD]
[TD="class: xl65, width: 64"]Count[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl67, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl67, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl67, align: right"]3

[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sivas]

Expecting the result as below

[TABLE="width: 128"]
<colgroup><col width="64" span="2" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl65, width: 64"]Sheet Name[/TD]
[TD="class: xl65, width: 64"]Count[/TD]
[/TR]
[TR]
[TD="class: xl66"]A[/TD]
[TD="class: xl67, align: right"]4[/TD]
[/TR]
[TR]
[TD="class: xl66"]B[/TD]
[TD="class: xl67, align: right"]2[/TD]
[/TR]
[TR]
[TD="class: xl66"]C[/TD]
[TD="class: xl67, align: right"]3[/TD]
[/TR]
</tbody>[/TABLE]

 

[davesexcel]

Check it out

Sub OhYa()
    Dim sh As Worksheet, ws As Worksheet, LstRw As Long, x, s As String

    Set ws = Sheets.Add
    ws.Name = "Result"
    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

 

[Sivas]

i wanted to search for particular column with name "Company" as below

[TABLE="class: cms_table, width: 320"]
<tbody>[TR]
[TD="class: cms_table_xl63, width: 64"]Company[/TD]
[TD][/TD]
[TD="class: cms_table_xl63, width: 64"]Company[/TD]
[TD][/TD]
[TD="class: cms_table_xl63, width: 64"]Company[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64"]ABC[/TD]
[TD][/TD]
[TD="class: cms_table_xl64"]ABC[/TD]
[TD][/TD]
[TD="class: cms_table_xl64"]ABC[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64"]DEF[/TD]
[TD][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD][/TD]
[TD="class: cms_table_xl64"][/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64"]GHI[/TD]
[TD][/TD]
[TD="class: cms_table_xl64"][/TD]
[TD][/TD]
[TD="class: cms_table_xl64"]GHI[/TD]
[/TR]
[TR]
[TD="class: cms_table_xl64"]JKL[/TD]
[TD][/TD]
[TD="class: cms_table_xl64"]JKL[/TD]
[TD][/TD]
[TD="class: cms_table_xl64"]JKL[/TD]
[/TR]
</tbody>[/TABLE]

and the result should like like

[TABLE="width: 128"]
<tbody>[TR]
[TD="class: xl65, width: 64"]Sheet Name[/TD]
[TD="class: xl65, width: 64"]Count[/TD]
[/TR]
</tbody>[/TABLE]

 

[Sivas]

Could you please help Dave