VBA Code Creation in Excel

[Sivas]

Please help me on creating a macro which search multiple sheets in a workbook with a particular column name and display the count of values in that column and display in a new sheet with sheet name and the count.

Here is my code,

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("A2:A100"))
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



But it doesn't workout. I have 3 sheets A, B & C and i want to search only the column name "XYZ". I have the data as below,

Sheet A Sheet B Sheet C
[TABLE="width: 320"]
<colgroup><col width="64" span="5" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]XYZ[/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64"]XYZ[/TD]
[TD="width: 64"][/TD]
[TD="class: xl63, width: 64"]XYZ[/TD]
[/TR]
[TR]
[TD="class: xl64"]1418780[/TD]
[TD][/TD]
[TD="class: xl64"]1418780[/TD]
[TD][/TD]
[TD="class: xl64"]1418780[/TD]
[/TR]
[TR]
[TD="class: xl64"]1438095[/TD]
[TD][/TD]
[TD="class: xl64"] [/TD]
[TD][/TD]
[TD="class: xl64"]1438095[/TD]
[/TR]
[TR]
[TD="class: xl64"]1643542[/TD]
[TD][/TD]
[TD="class: xl64"]1643542[/TD]
[TD][/TD]
[TD="class: xl64"] [/TD]
[/TR]
[TR]
[TD="class: xl64"]1309082[/TD]
[TD][/TD]
[TD="class: xl64"]1309082[/TD]
[TD][/TD]
[TD="class: xl64"] [/TD]
[/TR]
</tbody>[/TABLE]


and my result should look like,

[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: xl65"]A[/TD]
[TD="class: xl65"]4[/TD]
[/TR]
[TR]
[TD="class: xl65"]B[/TD]
[TD="class: xl65"]3[/TD]
[/TR]
[TR]
[TD="class: xl65"]C[/TD]
[TD="class: xl65"]2
[/TD]
[/TR]
</tbody>[/TABLE]


Could anyone help on this.

 

[AlphaFrog]

[color=darkblue]Sub[/color] Click()
    [color=darkblue]Dim[/color] sh [color=darkblue]As[/color] Worksheet, ws [color=darkblue]As[/color] Worksheet, x [color=darkblue]As[/color] [color=darkblue]Long[/color], i [color=darkblue]As[/color] Long
    
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Report"
    [color=darkblue]Set[/color] ws = ActiveSheet
    i = 2
    ws.Range("A1:B1").Value = Array("Name", "Count")
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] sh [color=darkblue]In[/color] Sheets
        [color=darkblue]If[/color] [color=darkblue]Not[/color] sh [color=darkblue]Is[/color] ws [color=darkblue]Then[/color]
            x = Application.WorksheetFunction.CountA(sh.Range("A2:A100"))
            sh.Cells(Rows.Count, "A").End(xlUp).Offset(1).Resize(, 2).Value = Array(sh.Name, x)
            ws.Rows(i).Range("A1:B1").Value = Array(sh.Name, x)
            i = i + 1
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] sh
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Sivas]

Thank you for your valuable answer. But it's not searching for particular column name.

 

[AlphaFrog]

[color=darkblue]Sub[/color] Click()
    [color=darkblue]Dim[/color] sh [color=darkblue]As[/color] Worksheet, ws [color=darkblue]As[/color] Worksheet, x [color=darkblue]As[/color] [color=darkblue]Long[/color], i [color=darkblue]As[/color] Long, rngXYZ [color=darkblue]As[/color] Range
    
    Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Report"
    [color=darkblue]Set[/color] ws = ActiveSheet
    i = 2
    ws.Range("A1:B1").Value = Array("Name", "Count")
    
    [color=darkblue]For[/color] [color=darkblue]Each[/color] sh [color=darkblue]In[/color] Sheets
        [color=darkblue]If[/color] [color=darkblue]Not[/color] sh [color=darkblue]Is[/color] ws [color=darkblue]Then[/color]
            [color=darkblue]Set[/color] rngXYZ = sh.Rows(1).Find("XYZ", , xlValues, xlWhole, 1, 1, 0)
            [color=darkblue]If[/color] [color=darkblue]Not[/color] rngXYZ [color=darkblue]Is[/color] [color=darkblue]Nothing[/color] [color=darkblue]Then[/color]
                x = Application.CountA(rngXYZ.EntireColumn) - 1
                sh.Cells(Rows.Count, rngXYZ.Column).End(xlUp).Offset(1).Resize(, 2).Value = Array(sh.Name, x)
                ws.Rows(i).Range("A1:B1").Value = Array(sh.Name, x)
                i = i + 1
            [color=darkblue]End[/color] [color=darkblue]If[/color]
        [color=darkblue]End[/color] [color=darkblue]If[/color]
    [color=darkblue]Next[/color] sh
    
[color=darkblue]End[/color] [color=darkblue]Sub[/color]

 

[Sivas]

Thank you very much for the help.

 

[Sivas]

The report comes at last sheet. I need it as first sheet

 

[AlphaFrog]

The report comes at last sheet. I need it as first sheet

Sheets.Add(Before:=Sheets(1)).Name = "Report"

 

[Sivas]

Thank you very much for your answer and help. I have one more additional query in this.

How to highlight the sheet with color which has the value more than 0.

 

[AlphaFrog]

How to highlight the sheet with color which has the value more than 0.

I don't know what that means.

 

[shg]

How to highlight the sheet with color which has the value more than 0.

https://www.contextures.com/xlCondFormat01.html