Count of Worksheets

T

Terry

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
 
B

Bob Phillips

Active.Worksheets.Count

--

HTH

RP
(remove nothere from the email address if mailing direct)
 
T

Terry

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
 
E

Earl Kiosterud

Bob,

Don't think that'll work unless you Dim "active" as a workbook. A typo,
mayhaps?
 
E

Earl Kiosterud

Bob,

Oops. That sounded kind of haughty the way I wrote it. I meant to say "I
don't think that'll work..."
 
E

Earl Kiosterud

Terry,

Looks as if you got it. You might like this. If you use Cells instead of
Range, you can do away with the name-generating stuff:

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
Cells(i, 2).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True
Cells(i, 1).Value = strSheetName
i = i + 1
End If
Next shtNext

And if you don't care about actually selecting the sheets, it will
streamline even further (and probably run a little faster):

For Each shtNext In Sheets
strSheetName = shtNext.Name
If Left(strSheetName, 2) = "R-" Then
Sheets(strSheetName).Range("J4:J7").Copy ' copy
Sheets("All-dBA").Cells(i, 2).PasteSpecial Paste:=xlPasteAll, _
Operation:=xlNone, SkipBlanks:=False, Transpose:=True ' paste
Sheets("All-dBA").Cells(i, 1).Value = strSheetName ' put
sheet name
i = i + 1
End If
Next shtNext

You can still single-step it with F8 to watch its progress.
 
B

Bob Phillips

Hi Earl,

Not to me it didn't, it sounded right :).

I need the spellchecker (with my typing, I need two), but it bites me
sometimes.

Bob
 
Top