Hi all

First, I hope I'm asking the right question to my problem

I ran a macro in order to get the process for copying a field and
pasting it transposed to another sheet. That's O.K. but since I need
to do this for 25 variaous sized fields, I was hoping I could just
write an Array with the field names and cycle thru each of them to
paste in the new sheet. As mentioned, the field sizes vary which is
why I thought to use field names.

I am rather new at this and Arrays scare me a bit as I am not really
comfortable with how they function....but my idea is to create an
Array for the field names (e.g. Qu1-25) and cycle thru the section of
macro below for each....the reason I ask is to find a way to compile
the amount of repeated code I need to do this. Actually, this should
act as a "refresh" button for when data has been manipulated or
changed....to update the new sheet.

Sheets("Data").Select
Range("B506:F506").Select
Selection.Copy
Sheets("Refresh").Select
Range("D1").Select
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:= _
False, Transpose:=True

Any help would be greatly appreciated.

Thanks for all your knopwledge
Carmen

I don't know what you mean by a field in Excel but consider a single
routine
sub MySub(rngFoo as range)
....
end sub

First, thanks for the speedy reply...

A field name is a series of cells that are bound by a name

e.g.
=Data!$B$506:$F$506 (=Qu1) i.e. Insert / name where field names are
defined (perhaps I'm using the wrong term)

anyway, could you elaborate a bit more on what your example is supposed
to achieve.


Thanks again
Carmen



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

I was suggesting to set up your recorded code as
sub MySub(rngFoo as range)
and then call it like
call MySub(Qu1)
(etc.)

Another idea: If you are able to assert that every "named range" that
you defined (e.g. when you go alt-Insert-Name-define) should be
processed, you can go something like

sub MySub(rngFoo as range)
Dim rng, sSubstr As String
For Each rng In Names
sSubstr = Mid(rng.name, InStr(rng.name, "!") + 1)
If (Left(sSubstr, 5) - "Print") Then Exit Sub
' rest of code
Next
end sub

But you can play around with that idea ... there may be exceptions that
don't come to mind (and you would need to avoid beginning a name with
"Print" - maybe test for "Print_Area" and "Print_Titles") Oh well, it's
just an idea you might play with if any of this "Another idea" makes
sense to you. Personally I would probably just type each range name in
a single range itself, and then go something like
For Each rng In myRangeNamesToTranspose
call MySub(rng)

Good luck - I'm offline for a stretch now.

On Mon, 18 Aug 2003 06:04:34 -0700, Carmen A wrote:


First, thanks for the speedy reply...

A field name is a series of cells that are bound by a name

e.g.
=Data!$B$506:$F$506 (=Qu1) i.e. Insert / name where field names are
defined (perhaps I'm using the wrong term)

anyway, could you elaborate a bit more on what your example is supposed
to achieve.


Thanks again
Carmen



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

For pasting all in a single column one set below the previous:

Dim rng1 as Range
Dim rng as Range
Dim i as Long
set rng1 = worksheets("Refresh").Range("D1")
for i = 1 to 25
set rng = Worksheets("Data").Range("QU" & I)
rng.copy
rng1.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
set rng1 = Worksheets("Refresh:").Cells(rows.count,1).end(xlu p)(2)
Next i

For pasting in adjacent Columns:

Dim rng1 as Range
Dim rng as Range
Dim i as Long
set rng1 = worksheets("Refresh").Range("D1")
for i = 1 to 25
set rng = Worksheets("Data").Range("QU" & I)
rng.copy
rng1.PasteSpecial Paste:=xlValues, _
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=True
set rng1 = rng1.offset(0,1)
Next i

--
Regards,
Tom Ogilvy

..
"Carmen A" wrote in message
...

First, thanks for the speedy reply...

A field name is a series of cells that are bound by a name

e.g.
=Data!$B$506:$F$506 (=Qu1) i.e. Insert / name where field names are
defined (perhaps I'm using the wrong term)

anyway, could you elaborate a bit more on what your example is supposed
to achieve.


Thanks again
Carmen



*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks again Tom your solution is working a bit better...

just once problem I'm having

the last line of code for the row input (i.e.) the first example of your
code keeps giving me a error 9 message "Index outside range" (I think,
it's in German and this is my best translation)

I'm not sure which part of the code it's affecting. It works great up
until the last line.

Can you offer perhaps some areas where I could research the problem....I
looked in the help index and the solutions given either don't apply or
don'r work....(i.e. I tried inputing the code to bypass the error
message)

Thanks again
Carmen

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Thanks all so much!!! Really, I was playing around too much with
classes and functions......that I felt I was really in over my head but
these suggestions will really help!!!

Thanks again for the speedy replies and all the ideas

Carmen

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!