Relative reference for named tabs?

  • Thread starter Dave in Fair Oaks
  • Start date
D

Dave in Fair Oaks

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

Mike H

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
 
G

Gord Dibben

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
 
D

Dave in Fair Oaks

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

Gord Dibben

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
 
D

Dave in Fair Oaks

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

Gord Dibben

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
 
Top