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.