Where did you place the macro at? What version of Excel are you using? Did
you change any of the code in the macro (if so, please post your modified
code)? I ask because I tested the code before I posted it and it worked fine
on my copy of Excel 2003.

--
Rick (MVP - Excel)


"speedy" wrote in message
...
The macro starts to work but it is bringing up a box with all of my files
and
I must answer cancel also the macro stops at N. Refers ToRange.copy
--
Mr Speedy


"Rick Rothstein" wrote:

A minor modification just in case you have other named ranges besides the
pg# ones...

Sub GetTransposeData()
Dim N As Name
Dim LastRow As Long
Const DataStartRow As Long = 3
Const DataStartColumn As String = "c"
With Worksheets("Data")
LastRow = DataStartRow
.Range(.Cells(DataStartRow, DataStartColumn), _
.Cells(.Rows.Count, .Columns.Count)).Clear
For Each N In ActiveWorkbook.Names
If N.Name Like "[Pp][Gg]*" Then
N.RefersToRange.Copy
.Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True
LastRow = LastRow + N.RefersToRange.Columns.Count
End If
Next
Application.CutCopyMode = False
Application.Goto .Cells(LastRow, DataStartColumn)
End With
End Sub

--
Rick (MVP - Excel)


"Rick Rothstein" wrote in message
...
Yes, that helps clarify what want for me. Give this macro a try...

Sub GetTransposeData()
Dim N As Name
Dim LastRow As Long
Const DataStartRow As Long = 3
Const DataStartColumn As String = "c"
With Worksheets("Data")
LastRow = DataStartRow
.Range(.Cells(DataStartRow, DataStartColumn), _
.Cells(.Rows.Count, .Columns.Count)).Clear
For Each N In ActiveWorkbook.Names
N.RefersToRange.Copy
.Cells(LastRow, DataStartColumn).PasteSpecial Transpose:=True
LastRow = LastRow + N.RefersToRange.Columns.Count
Next
Application.CutCopyMode = False
Application.Goto .Cells(LastRow, DataStartColumn)
End With
End Sub

Use the two Const statements to set the row and column you want to
start
placing your transposed data at.

--
Rick (MVP - Excel)


"speedy" wrote in message
...
Okay,
My workbook contain several sheets right now. The workbook will
increase
in
sheets as time goes on. The first sheet has numbers on it that I
need.
I
created a range name to capture the numbers that I need on the first
sheet.
The range of the range name is c2 through h51. I named this range
pg1.
I
have the same range on the next worksheet which is right next to the
other
worksheet. I am using the same RANGE but I am calling this pg2. My
steps
are as follow:
Go to or F5.
highlight range name.
copy.
paste special.
transpose.
Then I will paste this to a sheet that I have already created that is
the
last worksheet in this workbook. I need to put the next range that I
paste
to this last sheet let us call it data below the items that I just
pasted
from pg1.

So I will have a pg1 pasted then pg2 pasted and so on and so on until
all
of
my ranges are pasted.

I hope that makes it clear.
--
Mr Speedy


"Rick Rothstein" wrote:

See inline comments...

I am still confused.

That maybe because your first posting didn't ask the actual question
you
wanted an answer to.

The range names that I have are pg1 all the way to pg10
right now. I may have more later. These range names are on each
sheet of
one workbook. I want to go to the range name that I created and
copy
the
range to another worksheet which I have call data that is located
in
the
same
workbook. When I copy to the data sheet I want to past special
value
and
transpose. After that is done I want to go get pg2 and put it into
data
at
active file. Please help.

Give us a hint as to how your data is laid out. What kind of ranges
do
pg1,
pg2, etc. refer to? Where on the "data" sheet to you want to place
the
transposed data? You say in your next to last sentence "active
file"...
did
you mean "active workbook"? The more data you give us, the better
able
we
are to understand what you are trying to do.

--
Rick (MVP - Excel)