Count of Worksheets
 

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

Active.Worksheets.Count

--

HTH

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


"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

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

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


.

Bob,

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

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

"Bob Phillips" wrote in message
...
Active.Worksheets.Count

--

HTH

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


"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

Bob,

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

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

"Earl Kiosterud" wrote in message
...
Bob,

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

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

"Bob Phillips" wrote in message
...
Active.Worksheets.Count

--

HTH

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


"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

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.
--
Earl Kiosterud
mvpearl omitthisword at verizon period net
-------------------------------------------

"Terry" wrote in message
...
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


.

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


"Earl Kiosterud" wrote in message
...
Bob,

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

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

"Earl Kiosterud" wrote in message
...
Bob,

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

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

"Bob Phillips" wrote in message
...
Active.Worksheets.Count

--

HTH

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


"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