Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count and List only WorkSheets named (anything)&"-pilot"
I need help with the following code to count and list only worksheets
that contain the name "(anything)&-Pilot". I have code that installs the worksheet and names it, but I can't get a list of sheets with "(anything)&-Pilot". TIA for any help, Doug Present code: Private Sub CountPilots() Dim Plt As Long 'count only worksheets named (anything)-Pilot With ActiveWorkbook.Worksheets(Array("" & "," & "pilot", "" & "-pilot", "" & "- pilot")) Worksheets(Array("" & "," & "pilot", "" & "-pilot", "" & "- pilot")).Count = Plt End With 'Place name of all worksheets named (anything)-pilot on activesheet or "Name-List" sheet For Plt = 1 To Worksheets.Count ActiveCell(Plt, 1).Value = Worksheets(Plt).Name Next Plt End Sub |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Count and List only WorkSheets named (anything)&"-pilot"
Dim rng as Range Dim icnt as Long set rng = ActiveCell for each sh in ThisWorkbook.Worksheets if instr(1,sh.name, "-pilot",1) then rng.offset(icnt,0).Value = sh.name icnt = icnt + 1 end if Next If you will accept just "anything" & "pilot" without the hyphen, then change to Dim rng as Range Dim icnt as Long set rng = ActiveCell for each sh in ThisWorkbook.Worksheets if instr(1,sh.name, "pilot",1) then rng.offset(icnt,0).Value = sh.name icnt = icnt + 1 end if Next -- Regards, Tom Ogilvy "Doug" wrote in message om... I need help with the following code to count and list only worksheets that contain the name "(anything)&-Pilot". I have code that installs the worksheet and names it, but I can't get a list of sheets with "(anything)&-Pilot". TIA for any help, Doug Present code: Private Sub CountPilots() Dim Plt As Long 'count only worksheets named (anything)-Pilot With ActiveWorkbook.Worksheets(Array("" & "," & "pilot", "" & "-pilot", "" & "- pilot")) Worksheets(Array("" & "," & "pilot", "" & "-pilot", "" & "- pilot")).Count = Plt End With 'Place name of all worksheets named (anything)-pilot on activesheet or "Name-List" sheet For Plt = 1 To Worksheets.Count ActiveCell(Plt, 1).Value = Worksheets(Plt).Name Next Plt End Sub |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
"Count" and "List" functions across sheets | Excel Worksheet Functions | |||
Array as a "named range" - formula ok in cells, but error as "named range" | Excel Discussion (Misc queries) | |||
how can I count if column A="active" and column E="Job" in a list? | Excel Worksheet Functions | |||
Count occurences of "1"/"0" (or"TRUE"/"FALSE") in a row w. conditions in the next | New Users to Excel |