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)