What do you mean by label  names?

If you mean range names, then the worksheet  does have a collection of Names, I
think.

Do you mean cells with labelling text in them? If you can find something they
all have in common - and none of the data cells have - then you can look for
cells like that and copy  them.

What specifically do you mean by "label names"?

I highlight one cell that has 'text' in a Column
header name, and/or a range of cells that contain
'text' or numerics under the Column header name.
Then using the 'Insert' drop down menu,
('define' 'create' label') options;
to apply a 'name' to that cell or range of
cells for later reference in a macro or
formula.

Label names  are not the same as range names. I haven't ever used label  names,
so can't say for sure how you'd find them. They're not in the Names collection,
nor does there seem to be a Labels collection. Someone else know?

As far as I can tell, "label name" is a meaningless term. From his description,
it sounds like Jon is just using creating range names  based on labels in
adjacent cells.

Although the labels are not in the Names collection, the range names created
using them are. We just need to find every named range which has a label
above/below/left/right which matches the range name. That could be a range which
was created using Insert >> Name >> Create. The following macro will identify
all such ranges:

Sub TestRngNames()
Dim n As Name, Sht As Worksheet, Rng As Range
On Error Resume Next
For Each n In ActiveWorkbook.Names
Set Sht = Sheets(Mid(n.RefersTo, 2, (InStr(1, n.RefersTo, "!") - 2)))
Set Rng = Sht.Range(Right(n.RefersTo, (Len(n.RefersTo) - (InStr(1,
n.RefersTo, "!")))))
If Rng.Row > 1 Then
If Cells(Rng.Row - 1, Rng.Column).Value = n.Name Then
Range(Cells(Rng.Row - 1, Rng.Column), Cells(Rng.Row +
Rng.Rows.Count - 1, Rng.Column + Rng.Columns.Count - 1)).Select
MsgBox "Found " & n.Name & " label  ABOVE range. Label is in row
" & Rng.Row - 1 & ", column " & Rng.Column
End If
End If
If (Rng.Row + Rng.Rows.Count) <= Rows.Count Then
If Cells(Rng.Row + Rng.Rows.Count, Rng.Column).Value = n.Name Then
Range(Cells(Rng.Row, Rng.Column), Cells(Rng.Row +
Rng.Rows.Count, Rng.Column + Rng.Columns.Count - 1)).Select
MsgBox "Found " & n.Name & " label BELOW range. Label is in row
" & Rng.Row + Rng.Rows.Count & ", column " & Rng.Column
End If
End If
If Rng.Column > 1 Then
If Cells(Rng.Row, Rng.Column - 1).Value = n.Name Then
Range(Cells(Rng.Row, Rng.Column - 1), Cells(Rng.Row +
Rng.Rows.Count - 1, Rng.Column + Rng.Columns.Count - 1)).Select
MsgBox "Found " & n.Name & " label to LEFT of range. Label is in
row " & Rng.Row & ", column " & (Rng.Column - 1)
End If
End If
If (Rng.Column + Rng.Columns.Count) <= Columns.Count Then
If Cells(Rng.Row, Rng.Column + Rng.Columns.Count).Value = n.Name
Then
Range(Cells(Rng.Row, Rng.Column), Cells(Rng.Row + Rng.Rows.Count
- 1, Rng.Column + Rng.Columns.Count)).Select
MsgBox "Found " & n.Name & " label to RIGHT of range. Label is
in row " & Rng.Row & ", column " & (Rng.Column + Rng.Columns.Count)
End If
End If
Next n
Set Sht = Nothing
Set Rng = Nothing
End Sub

At the point when the range is selected, code could be added to copy  the range
to wherever it goes on the new sheet (I am assuming Jon wants to copy the entire
range, including the label).

Is the desired result of Jon's request to have the same named ranges on
multiple worksheets in the same workbook? Why not just refer to the ranges on
the first sheet, or move them all to a separate sheet for easier maintenance?

I assumed he meant the thing you get from Insert / Name / Label. As far as I
could see, nothing went into the names  collection from creating one of these.

there is certainly confusion on this, and I start to think that Thomas
is right. Do you use Insert / Name / Label to create a Column Label or Row
Label, or do you use Insert / Name / Define to create a Named Range?

As Thomas says, Named Range names are stored in the Names collection. I can't
find where Column Labels or Row Labels are kept.

I apologize for any confusion. And I appreciate all the attention

the members have expended on this.



I have used the ‘Insert/Name/Create’ to apply a ‘proper noun(?)’ to

either a ‘Column Heading’ , and also to a ‘Range of Cells’.



Thanks to all for the help, you are great!

Insert/Name/Create creates a range name. These are in the names  collection.

You can look at all range names from the application's collection or narrow the
field by looking at the workbook's or the worksheet's collections.

However, I'm now confused over why you'd want to copy  these names.

Although you can have multiple range names with the same name in a single
workbook (in separate worksheets), they're more than a little hard to use and
essentially negate the benefit of having the names in the first place.

What are you trying to achieve?

There are 20 worksheets within one Spreadsheet. I

want to automate the ‘export’ of the data values

out of ‘selected’ columns (8 out of 20) from all the

worksheets to a new spreadsheet in order to build

PivotTables.



The need for a ‘new’ spreadsheet is

due to the presence of many ’blank ‘ cells (null)

within the columns. I’m not permitted to modify

the source spreadsheet.



The Client wants to have a way to facilitate the

analysis of the huge amount of data that is in the

source spreadsheet – thus the use of PivotTables.





VB Code to export to the ‘new’ spreadsheet

using Label names  and Range names would help.



I’m open to any alternative approaches that you, or

the others could suggest. Any help would be

appreciated.

I think we've jumped from "too specific" to "too high-level". :-)

Going back to your original question for a moment ... was the purpose of wanting
to copy  the range names  because each worksheet  has the same layout and you could
attach the names to indicate where the information was to be copied from? If
so, a list of column names would probably be better.

You'll need to break your problem down into more precise steps before you can
think about coding it. Answers to questions like these will get you on the
right track:

Is it the same 8 columns from every sheet? (If so, you can have one list of
columns and apply it against multiple sheet names.)

Do you always start at the top of the column and go to the bottom of the
information in that column? Are all columns within a sheet the same height?
Are all columns in all sheets the same height? (Determines how you find the
data to copy. May involve a search up from the bottom of the sheet to find the
last row used in the columns.)

Are the wanted columns adjacent, or are there columns in there that you don't
want to copy? (Might be able to define a rectangular range and copy the lot in
one go if the end row is also the same.)

Is there a header row to be avoided? (May need to start from row 2.)

How do you detect null / blank cells and what do you do with them?
(Specifically here, I suppose, is whether we're talking about ignoring whole
rows or treating individual columns independently. How do you decide a row is
null / blank? Do all cells have to be empty?)

How do you consolidate the information? Vertically in 8 tall columns?
Horizontally in 160 columns? Summed? (Seeing you're talking pivot tables, I'd
anticipate 8 tall columns.)

I'm sure I've missed questions, but that's a start.

===================

Seeing you're bringing information together for pivot tables, I'd anticipate the
following:

It is the same 8 columns on each sheet.

You always start at the top of the column and need to find the bottom of the
data. Columns in different sheets have different heights, but all columns in a
sheet are the same height.

The wanted columns are adjacent. (Or you could copy a couple of unwanted
columns to allow for a rectangular area copy.)

There is a header row to be avoided.

You detect and ignore empty rows, not individual empty cells.

You are consolidating into 8 tall columns.

===================

If I'm close, then the thing I'd like to use would be a rectangular copy of
cells to the new sheet. The only problem with this would be that it would copy
the blank rows too.

One option is to copy the blank rows, then clean them up in the consolidated
sheet. This is probably a good option in this case.

(Gotta run off to work, so quickly to finish ...)

I'd tend to use a left and right column number to denote the sides of my area.
First row is row 2. Last row is an xlUp from Cell (1, 65000).

That defines your "from" for any sheet and also gives you the number of rows
you're copying.

Assign an appropriate rectangle in your consolidation sheet with the values from
the rectangular area in your source sheet (a single assignment statement should
do this). Update your "last row" variable for the consolidation sheet. Then do
the same rows on the next sheet.

Once all sheets are done, work down your consolidated sheet and detect and
delete any empty rows. Then you should be ready for your pivot tables.