I have a workbook with 50+ sheets and each tab is named. I need
to create a new sheet in the same workbook that will pick of the same
cell from each of the 50+ sheets and put them in a column on the new
sheet. In tab order, left to right.
How can I do that without having to type each tab name in the
reference?

Thanks,
Dave ....

Dave,

With your new worksheet selected call this code:-
Sub names()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Cells(x, 1).Value = ws.Name
x = x + 1
Next ws
End Sub

Mike


"Dave in Fair Oaks" wrote:

I have a workbook with 50+ sheets and each tab is named. I need
to create a new sheet in the same workbook that will pick of the same
cell from each of the 50+ sheets and put them in a column on the new
sheet. In tab order, left to right.
How can I do that without having to type each tab name in the
reference?

Thanks,
Dave ....

Mike and Dave

Mike's code places the sheetnames in a column on the activesheet.

I think maybe a revision is in order to list the values from a cell on each
sheet across row 1 on the activesheet.

Sub names22()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Cells(1, x).Value = ws.Range("A1").Value
x = x + 1
Next ws
End Sub


Gord Dibben MS Excel MVP


On Fri, 8 Jun 2007 00:37:21 -0700, Mike H
wrote:

Dave,

With your new worksheet selected call this code:-
Sub names()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Cells(x, 1).Value = ws.Name
x = x + 1
Next ws
End Sub

Mike


"Dave in Fair Oaks" wrote:

I have a workbook with 50+ sheets and each tab is named. I need
to create a new sheet in the same workbook that will pick of the same
cell from each of the 50+ sheets and put them in a column on the new
sheet. In tab order, left to right.
How can I do that without having to type each tab name in the
reference?

Thanks,
Dave ....

Mike and Gord,
You both offered something I can use. With a little trial and
error I reverse engineered your routines to give me this:

Sub Both()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Cells(x, 1).Value = ws.Name
Cells(x, 2).Value = ws.Range("C19").Value
x = x + 1
Next ws
End Sub

Cell C19 on each sheet has the data I want, so now I get a 2-column
array with the tab name in the left column and the value of C19 on the
sheet in the right column. Exactly what I wanted. Thank you both!
Dave ....

Thanks for posting back Dave.

Looks like both Mike and I misread but the combination looks good.

We generally get there even if it takes a herd of us working on it<g

One caveat.........you might want to change ThisWorkbook to ActiveWorkbook just
in case you want to store this macro in Personal.xls or an Add-in


Gord

On Fri, 08 Jun 2007 15:30:50 -0700, Dave in Fair Oaks
wrote:


Mike and Gord,
You both offered something I can use. With a little trial and
error I reverse engineered your routines to give me this:

Sub Both()
x = 1
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
Cells(x, 1).Value = ws.Name
Cells(x, 2).Value = ws.Range("C19").Value
x = x + 1
Next ws
End Sub

Cell C19 on each sheet has the data I want, so now I get a 2-column
array with the tab name in the left column and the value of C19 on the
sheet in the right column. Exactly what I wanted. Thank you both!
Dave ....

Gord,
I was so satisfied with myself that I didn't check back until
today. ;-)
My programming "general knowledge" carries me right up to your
latest suggestion. How do the keywords "This..." and "Active..."
differ in what they affect? Is there someplace in Excel Help where I
can find VBA programming infromation? Or can you recommend an after-
market book?
I inherited a lot of data in a format that would not yield to the
reporting requirements placed upon it. What you and Mike offered
really made a weak workbook design much more useful. Now I can use
the original worksheets for data input, then I use "our" macro to
extract the data in a useable format. Very cool. I think this will
eventually evolve into something better handled in Access. But for
now I'd like to gain some facility with programming macros.

Thanks again,
Dave ....