PDA

View Full Version : [SOLVED] need help with counting sheets with specific names plus adding sheets after them



RudaAgata
12-20-2013, 04:29 AM
hi I would need help with a comment to count the sheets with name starting with "wf" in a workbook and then add a growing sheet after last tabs with "wf". I have other sheets with other names within that workbook so the new growing sheet needs to come exactly after the last found one with "wf" used.
thank you so much
Agata

mikerickson
12-20-2013, 07:22 AM
Something like
Dim lastWFWorksheet as Worksheet
Dim countOfWF as Long
Dim oneSheet as Worksheet

For each oneSheet in ThisWorkbook.Sheets
If oneSheet.Name Like "wf*" Then
countOfWF = countOfWF + 1
Set lastWFWorksheet = oneSheet
End If
Next oneSheet
' ...
MsgBox CStr(countOfWF) & " sheets names begin with wf (case sensitive)."
' ...
If countOfWF <> 0 Then
ThisWorkbook.Sheets.Add after:=lastWFWorksheet
End If

RudaAgata
12-23-2013, 01:58 AM
Hi
thank you for help, however it gives me 0 count but the files starting with wf are there...

Kenneth Hobs
12-23-2013, 07:00 AM
Welcome to the forum!

Mike's code worked fine for me. Did you notice that the search was case sensitive? In other words, wf5<>WF5.

You lost me with files starting with. You asked for sheets with names starting with "wf".

mikerickson
12-23-2013, 08:46 AM
Are we talking worksheets, contained in the same workbook as the code.
Or are we talking about files, in a folder?

SamT
12-26-2013, 11:26 AM
Mikes code: Case Insensitive

If LCase(oneSheet.Name) Like "wf*" Then
'...
MsgBox CStr(countOfWF) & " sheets names begin with wf (case insensitive)."

RudaAgata
01-20-2014, 04:41 AM
Hallo,
thank you all for help! I used following code:

Dim WS As Worksheet, iCnt As Long
Dim WW As Worksheet
For Each WS In ActiveWorkbook.Worksheets
Debug.Print WS.Name
If InStr(1, Left(WS.Name, 2), "wf", vbTextCompare) > 0 Then
myTotal = myTotal + 1
different version was 'If InStr(1, WS.Name, "wf", vbTextCompare) = 1 Then iCnt = iCnt + 1 - also working
'Set WW = WS
End If