CODEWITHSUNDEEP
excelvba
SarangArd
SarangArd

Reputation: 1165

Check for a Sheet in Excel using VBA

I might have Sheets in my excel workbook named as Data1, Data2,... What i want to know is how many such Sheets are there in my Workbook.

I was trying to compare the sheet name and keep a count. But it did not work Properly.

So hoping that someone might help me out here. Thanks in advance.

Upvotes: 2

Views: 363

Answers (2)

Our Man in Bananas
Our Man in Bananas

Reputation: 5981

try this

msgbox thisworkbook.sheets.count

that will return the number of sheet sijn the workbook.

To return the number of sheets with names like 'Data' do this:

Dim iCountSheets As Integer
Dim oSheet As Worksheet
'
For Each oSheet In Sheets
    If oSheet.Name Like "Data*" Then
       iCountSheets = iCountSheets + 1
    End If
Next

MsgBox iCountSheets & " sheets like data"

another way is to iterate through the sheets collection by index:

Dim iCountSheets As Integer, iIdx As Integer
Dim oSheet As Worksheet
'
For iIdx = 1 To Sheets.Count ' non-zero based collection
    If Sheets(iIdx).Name Like "Data*" Then
       iCountSheets = iCountSheets + 1
    End If
Next

MsgBox iCountSheets & " sheets like data"

Upvotes: 6

Siddharth Rout
Siddharth Rout

Reputation: 149277

Is this what you are trying?

'~~> Case Sensitive
Sub Sample()
    Dim ws As Worksheet
    Dim shCount As Long

    For Each ws In ThisWorkbook.Sheets
        If ws.Name Like "Data*" Then shCount = shCount + 1
    Next ws

    Debug.Print shCount
End Sub

'~~> Or use the below if you have sheets like data1, Data2, DaTa3 etc
Sub Sample()
    Dim ws As Worksheet
    Dim shCount As Long

    For Each ws In ThisWorkbook.Sheets
        If UCase(ws.Name) Like "DATA*" Then shCount = shCount + 1
    Next ws

    Debug.Print shCount
End Sub

Upvotes: 4

Related Questions