Hi,

I am using Excel 2002 and I am trying to find a simpler or elegant way in
doing cell reference. Here is my example I will have a column let's say
from A1 to A10 in worksheet name "Insured". What I want to do is to get the
information from A1 to A10 and put in a different worksheet called "Policy
Holder", but the trick is I want the information starting from A1-J1 (row).
Any tips will be appreciated. Thank you in advance.

Alex,

PasteSpecial Transpose might be your key.

You can try it in Excel, observe how it works, then record your steps and
see what the macro recorder does if you need the VBA equivalent.

Bill

"Alex Martinez" wrote in message
...

Hi,

I am using Excel 2002 and I am trying to find a simpler or elegant way in
doing cell reference. Here is my example I will have a column let's say
from A1 to A10 in worksheet name "Insured". What I want to do is to get
the information from A1 to A10 and put in a different worksheet called
"Policy Holder", but the trick is I want the information starting from
A1-J1 (row). Any tips will be appreciated. Thank you in advance.

Hi Alex~
Try this:
Place this code in your VBA module (this already has transpose set to true):

~~~~~~~~~~~~~~~~~
Sub PasteSpecial_InsVals_SkipBlanks()
'
' PasteSpecial_InsVals_SkipBlanks Macro
' Macro to Paste Special with Ins Val & Skip Blank
'
' Keyboard Shortcut: Ctrl+Shift+V
'

Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=True, Transpose:=True
End Sub
~~~~~~~~~~~~~~~~~~~

Then save the module and go to the spreadsheet you need to run this on.
Define the cells and hit CTRL C, then place the cursor on the first cell in
the new worksheet. Now press CTRL SHFT V.

Hope this helps,
Don




"Alex Martinez" wrote:


Hi,

I am using Excel 2002 and I am trying to find a simpler or elegant way in
doing cell reference. Here is my example I will have a column let's say
from A1 to A10 in worksheet name "Insured". What I want to do is to get the
information from A1 to A10 and put in a different worksheet called "Policy
Holder", but the trick is I want the information starting from A1-J1 (row).
Any tips will be appreciated. Thank you in advance.