Hi All,

I'm assuming this is possible but I can't find anything in the help file.

I have roughly 30 sheets and a "totals" sheet. I want to select the first
sheet in the workbook and copy some data from it to the totals sheet, and
so on from the 30 odd sheets I have.

The problem that I'm having is the sheets all have different names and the
vba sheet numbers are never in a pattern.

Example; in the vb editor my sheets are listed in this fashion.

Sheet2(under12s)
Sheet4(under15s)
Sheet5(under18s)
Sheet12(under5s)
Sheet15(totals)

What I want to do is select the first in the list (Sheet2) and copy some
data to the totals sheet, then the next (Sheet4) then the next (Sheet5) and
so on.

Is there a way to do this without having to name every sheet in vba?

Ron

Hi Ron

See if this helps. It assumes that you don't want the bottommost sheet,
that's what the -1 does:

Sub test()
Dim L As Long
For L = 1 To ActiveWorkbook.Worksheets.Count - 1
Worksheets(L).Select
MsgBox Worksheets(L).Name
Next
End Sub

You don't have to select sheets to copy from them, but that's another story.

HTH. Best wishes Harald


"Ron" skrev i melding
...
Hi All,

I'm assuming this is possible but I can't find anything in the help file.

I have roughly 30 sheets and a "totals" sheet. I want to select the first
sheet in the workbook and copy some data from it to the totals sheet, and
so on from the 30 odd sheets I have.

The problem that I'm having is the sheets all have different names and the
vba sheet numbers are never in a pattern.

Example; in the vb editor my sheets are listed in this fashion.

Sheet2(under12s)
Sheet4(under15s)
Sheet5(under18s)
Sheet12(under5s)
Sheet15(totals)

What I want to do is select the first in the list (Sheet2) and copy some
data to the totals sheet, then the next (Sheet4) then the next (Sheet5)
and
so on.

Is there a way to do this without having to name every sheet in vba?

Ron

Hi Harold,

That's great, thanks.

Ron










"Harald Staff" wrote in
:

Hi Ron

See if this helps. It assumes that you don't want the bottommost
sheet, that's what the -1 does:

Sub test()
Dim L As Long
For L = 1 To ActiveWorkbook.Worksheets.Count - 1
Worksheets(L).Select
MsgBox Worksheets(L).Name
Next

End Sub

You don't have to select sheets to copy from them, but that's another
story.

HTH. Best wishes Harald