Example-
How can I copy date from cells A1:A5 and then paste them in cells B1:B5 sp
that the data from cell A5 is pasted in cell B1, A4 in B2, A3 in B3, A2 in
B4, A1 in B5?

Please help. Thanks.

Don't paste, use a formula.

Enter this in B1 and drag/copy to B5

=INDEX($A$1:$A$5,5-(ROW(A1)-1))


Gord Dibben MS Excel MVP

On Sat, 15 Dec 2007 08:52:01 -0800, Tigerxxx
<(E-Mail Removed)> wrote:

>Example-
>How can I copy date from cells A1:A5 and then paste them in cells B1:B5 sp
>that the data from cell A5 is pasted in cell B1, A4 in B2, A3 in B3, A2 in
>B4, A1 in B5?
>
>Please help. Thanks.

Hi,

To invert copy ...

Sub InvertCopy()
Dim i As Integer
Dim j As Integer
j = Cells(Cells.Rows.Count, "A").End(xlUp).Row
For i = 1 To j
Cells(i, 1).Copy Cells(j - i + 1, 2)
Next i
End Sub

HTH

Thanks for the advise Gord.
However I was looking for something simpler that may exist in Excel in lines
of paste special. I may have to do this frequently and hence qriting formulas
each time would be time consuming.
Thanks again.

"Gord Dibben" wrote:

> Don't paste, use a formula.
>
> Enter this in B1 and drag/copy to B5
>
> =INDEX($A$1:$A$5,5-(ROW(A1)-1))
>
>
> Gord Dibben MS Excel MVP
>
> On Sat, 15 Dec 2007 08:52:01 -0800, Tigerxxx
> <(E-Mail Removed)> wrote:
>
> >Example-
> >How can I copy date from cells A1:A5 and then paste them in cells B1:B5 sp
> >that the data from cell A5 is pasted in cell B1, A4 in B2, A3 in B3, A2 in
> >B4, A1 in B5?
> >
> >Please help. Thanks.
>
>

Hi Carim,
How can I make use of your program code in excel. I am not too familiar with
the VB editor. Do I need to program this as a macro?
Please advise.
Thanks.


"Carim" wrote:

> Hi,
>
> To invert copy ...
>
> Sub InvertCopy()
> Dim i As Integer
> Dim j As Integer
> j = Cells(Cells.Rows.Count, "A").End(xlUp).Row
> For i = 1 To j
> Cells(i, 1).Copy Cells(j - i + 1, 2)
> Next i
> End Sub
>
> HTH
>

Yes, it is a macro ...

But you should use Gord's formula, which is a lot more
elegant ... !!!

Thanks Carim.
Is there a way I can use uour code to program a macro?

"Carim" wrote:

> Yes, it is a macro ...
>
> But you should use Gord's formula, which is a lot more
> elegant ... !!!
>
>

How about a macro that asks what should be copied and where it should be pasted?

Option Explicit
Sub testme()
Dim FromRng As Range
Dim ToCell As Range
Dim cCtr As Long

Set FromRng = Nothing
On Error Resume Next
Set FromRng = Application.InputBox _
(Prompt:="Select a range with exactly 1 area and 1 column", _
Default:=Selection.Areas(1).Columns(1).Address, _
Type:=8).Areas(1).Columns(1)
On Error GoTo 0

If FromRng Is Nothing Then
'user hit cancel
MsgBox "Try later"
Exit Sub
End If

Set ToCell = Nothing
On Error Resume Next
Set ToCell = Application.InputBox _
(Prompt:="Select a the top cell of the range to paste", _
Type:=8).Areas(1).Cells(1)
On Error GoTo 0

If ToCell Is Nothing Then
MsgBox "Ok, try later"
Exit Sub
End If

For cCtr = FromRng.Cells.Count To 1 Step -1
ToCell.Offset(FromRng.Cells.Count - cCtr, 0).Value _
= FromRng.Cells(cCtr)
Next cCtr

End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Tigerxxx wrote:
>
> Thanks Carim.
> Is there a way I can use uour code to program a macro?
>
> "Carim" wrote:
>
> > Yes, it is a macro ...
> >
> > But you should use Gord's formula, which is a lot more
> > elegant ... !!!
> >
> >

--

Dave Peterson

Thanks a lot Dave. Your codes helped. Actually I was able to change them to
use it for rows as your codes were written for columns. My first shot at
macros.
Can we make this simpler wherein I can select a range of rows or range of
columns, then click copy, then run a macro which would paste the copied data
inverted?

"Dave Peterson" wrote:

> How about a macro that asks what should be copied and where it should be pasted?
>
> Option Explicit
> Sub testme()
> Dim FromRng As Range
> Dim ToCell As Range
> Dim cCtr As Long
>
> Set FromRng = Nothing
> On Error Resume Next
> Set FromRng = Application.InputBox _
> (Prompt:="Select a range with exactly 1 area and 1 column", _
> Default:=Selection.Areas(1).Columns(1).Address, _
> Type:=8).Areas(1).Columns(1)
> On Error GoTo 0
>
> If FromRng Is Nothing Then
> 'user hit cancel
> MsgBox "Try later"
> Exit Sub
> End If
>
> Set ToCell = Nothing
> On Error Resume Next
> Set ToCell = Application.InputBox _
> (Prompt:="Select a the top cell of the range to paste", _
> Type:=8).Areas(1).Cells(1)
> On Error GoTo 0
>
> If ToCell Is Nothing Then
> MsgBox "Ok, try later"
> Exit Sub
> End If
>
> For cCtr = FromRng.Cells.Count To 1 Step -1
> ToCell.Offset(FromRng.Cells.Count - cCtr, 0).Value _
> = FromRng.Cells(cCtr)
> Next cCtr
>
> End Sub
>
> If you're new to macros, you may want to read David McRitchie's intro at:
> http://www.mvps.org/dmcritchie/excel/getstarted.htm
>
> Tigerxxx wrote:
> >
> > Thanks Carim.
> > Is there a way I can use uour code to program a macro?
> >
> > "Carim" wrote:
> >
> > > Yes, it is a macro ...
> > >
> > > But you should use Gord's formula, which is a lot more
> > > elegant ... !!!
> > >
> > >
>
> --
>
> Dave Peterson
>

Sorry, I didn't read the whole thread and thought you wanted vertical ranges.

Maybe you could enhance this to do both rows and columns:

Option Explicit
Sub testme()
Dim FromRng As Range
Dim ToCell As Range
Dim cCtr As Long
Dim myRow As Range

Set FromRng = Nothing
On Error Resume Next
Set FromRng = Application.InputBox _
(Prompt:="Select a range with exactly 1 area", _
Default:=Selection.Areas(1).Address, _
Type:=8).Areas(1)
On Error GoTo 0

If FromRng Is Nothing Then
'user hit cancel
MsgBox "Try later"
Exit Sub
End If

Set ToCell = Nothing
On Error Resume Next
Set ToCell = Application.InputBox _
(Prompt:="Select a the top left cell of the range to paste", _
Type:=8).Areas(1).Cells(1)
On Error GoTo 0

If ToCell Is Nothing Then
MsgBox "Ok, try later"
Exit Sub
End If

For Each myRow In FromRng.Rows
For cCtr = myRow.Cells.Count To 1 Step -1
ToCell.Offset(0, myRow.Cells.Count - cCtr).Value _
= myRow.Cells(1).Offset(0, cCtr - 1)
Next cCtr
Set ToCell = ToCell.Offset(1, 0)
Next myRow

End Sub



Tigerxxx wrote:
>
> Thanks a lot Dave. Your codes helped. Actually I was able to change them to
> use it for rows as your codes were written for columns. My first shot at
> macros.
> Can we make this simpler wherein I can select a range of rows or range of
> columns, then click copy, then run a macro which would paste the copied data
> inverted?
>
> "Dave Peterson" wrote:
>
> > How about a macro that asks what should be copied and where it should be pasted?
> >
> > Option Explicit
> > Sub testme()
> > Dim FromRng As Range
> > Dim ToCell As Range
> > Dim cCtr As Long
> >
> > Set FromRng = Nothing
> > On Error Resume Next
> > Set FromRng = Application.InputBox _
> > (Prompt:="Select a range with exactly 1 area and 1 column", _
> > Default:=Selection.Areas(1).Columns(1).Address, _
> > Type:=8).Areas(1).Columns(1)
> > On Error GoTo 0
> >
> > If FromRng Is Nothing Then
> > 'user hit cancel
> > MsgBox "Try later"
> > Exit Sub
> > End If
> >
> > Set ToCell = Nothing
> > On Error Resume Next
> > Set ToCell = Application.InputBox _
> > (Prompt:="Select a the top cell of the range to paste", _
> > Type:=8).Areas(1).Cells(1)
> > On Error GoTo 0
> >
> > If ToCell Is Nothing Then
> > MsgBox "Ok, try later"
> > Exit Sub
> > End If
> >
> > For cCtr = FromRng.Cells.Count To 1 Step -1
> > ToCell.Offset(FromRng.Cells.Count - cCtr, 0).Value _
> > = FromRng.Cells(cCtr)
> > Next cCtr
> >
> > End Sub
> >
> > If you're new to macros, you may want to read David McRitchie's intro at:
> > http://www.mvps.org/dmcritchie/excel/getstarted.htm
> >
> > Tigerxxx wrote:
> > >
> > > Thanks Carim.
> > > Is there a way I can use uour code to program a macro?
> > >
> > > "Carim" wrote:
> > >
> > > > Yes, it is a macro ...
> > > >
> > > > But you should use Gord's formula, which is a lot more
> > > > elegant ... !!!
> > > >
> > > >
> >
> > --
> >
> > Dave Peterson
> >

--

Dave Peterson