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 ....

Dave

The statment "Thisworkbook" means the code will run on whatever workbook
contains the code.

OK if the code is run from within the workbook to be acted upon.

But if you had the code in Personal.xls or an add-in you would want to use
"Activeworkbook" so it acts upon whatever workbook is currently active.

VBA help can be accessed through the VB Editor.

Alt + F11 to open VBE and click on help.

A list of after market books can be found on Debra Dalgleish's site.

http://www.contextures.on.ca/xlbooks.html

See also Debra's list of what's on her bookshelf.

http://www.contextures.on.ca/xlbookshelf.html


Gord

On Tue, 19 Jun 2007 08:23:53 -0700, Dave in Fair Oaks
wrote:

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 ....