Count Specific words from all the sheet.

[SHEEPRAL]

Dear All,

I am trying to build a macro to find specific words from the excel sheet with all the excel sheets available in macro.
My requirement are as below.

Need the count of that specific word in last ( summary ) sheet.
Also need a sheetwise count in single row.
Sheet name should be copied in summary sheet.

example. ( Summary Sheet )

Sheet name	vishal	Raghu	uday
Sheet1 ( as per name given to sheet )	2	3	5
Sheet2( as per name given to sheet )	1	2	0

<tbody>
</tbody>

So if in Sheet1 ( Range A:A ) if Vishal name appears 2 times details in summary sheet should be as quoted above.

Hope you will assist me in creating this macro which will save my lots of time.

 

[Fluff]

Hi, how about

Sub GetCounts()

   Dim Ary As Variant
   Dim SuSht As Worksheet
   Dim Ws As Worksheet
   Dim Cnt As Long
   Dim Rw As Long
   
   Ary = Array("[COLOR=#ff0000]vishal[/COLOR]", "[COLOR=#ff0000]raghu[/COLOR]", "[COLOR=#ff0000]uday[/COLOR]")
   Set SuSht = Sheets("Summary")
   Rw = 1
   
   SuSht.Range("B1").Resize(, UBound(Ary) + 1).Value = Ary
   For Each Ws In Worksheets
      Rw = Rw + 1
      SuSht.Range("A" & Rw).Value = Ws.name
      If Not Ws.name = "Summary" Then
         For Cnt = LBound(Ary) To UBound(Ary)
            SuSht.Cells(Rw, Cnt + 2).Value = WorksheetFunction.CountIf(Ws.Columns(1), Ary(Cnt))
         Next Cnt
      End If
   Next Ws

End Sub

Values in red are the search terms

 

[SHEEPRAL]

<tbody>
</tbody>

Dear Fluff,

Thanks a lot for your valuable help.

Sorry to disturb you again, can we add one more details in Summary sheet.
For e.g. I have similar names in another column B, and I need the details of the same too.

example. ( Summary Sheet )

	A RESULT	A RESULT	A RESULT		B RESULT	B RESULT	B RESULT
SHEET NAME	RAGHU	VISHAL	UDAY		RAGHU	VISHAL	UDAY
SHEET1	1	2	4		10	5	10

<tbody>
</tbody>

 

[Fluff]

How about

Sub GetCounts()

   Dim AryA As Variant
   Dim AryB As Variant
   Dim SuSht As Worksheet
   Dim Ws As Worksheet
   Dim CntA As Long
   Dim CntB As Long
   Dim Rw As Long
   
   AryA = Array("vishal", "raghu", "uday")
   AryB = Array("vishal", "raghu", "uday")
   Set SuSht = Sheets("Summary")
   Rw = 1
   
   SuSht.Range("B1").Resize(, UBound(AryA) + 1).Value = AryA
   SuSht.Cells(1, Columns.Count).End(xlToLeft).Offset(, 1).Resize(, UBound(AryB) + 1).Value = AryB
   For Each Ws In Worksheets
      Rw = Rw + 1
      If Not Ws.name = "Summary" Then
         SuSht.Range("A" & Rw).Value = Ws.name
         For CntA = LBound(AryA) To UBound(AryA)
            SuSht.Cells(Rw, CntA + 2).Value = WorksheetFunction.CountIf(Ws.Columns(1), AryA(CntA))
         Next CntA
         For CntB = LBound(AryB) To UBound(AryB)
            SuSht.Cells(Rw, CntA + CntB + 2).Value = WorksheetFunction.CountIf(Ws.Columns(1), AryA(CntB))
         Next CntB
      End If
   Next Ws

End Sub