Thank you. Always appreciate the help.... most useful
site.
The following is what I was trying to do. I cannot put
all the data on one sheet.

Sheets.Add Type:="Worksheet"
ActiveSheet.Name = "All-dBA"

Range("A3").Value = "Tab"
Range("B3").Value = "Lvl 10"
Range("C3").Value = "Lvl 50"
Range("D3").Value = "Lvl 90"
Range("E3").Value = "Lvl 99"


i = 4
For Each shtNext In Sheets
strSheetName = shtNext.Name
If Left(strSheetName, 2) = "R-" Then
Sheets(strSheetName).Activate
Range("J4:J7").Copy
Sheets("All-dBA").Activate
strRange = "B" & i
Range(strRange).Select
Selection.PasteSpecial Paste:=xlPasteAll,
Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
strRange = "A" & i
Range(strRange).Value = strSheetName
i = i + 1
End If
Next shtNext

End Sub


-----Original Message-----
Terry,

Worksheets.Count

I wonder if your data should be in separate sheets.
Before you go much
farther down this road, read
http://www.smokeylake.com/excel/excel_truths.htm.
Read "Data across
multiple sheets."

--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Terry" wrote in
message
...
How do you count how many Worksheets (tabs) in a Excel
file. I know at one time I have a macro that went
through
each worksheet.
I'm trying to get the number of worksheets
and then loop thru all the worksheets and if the tab
name
of any start with "R-" copy the data from that worksheet
to another to combine all the data from all the "R-"
worksheets.

If this makes since can anyone help me? Thanks


.