Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default How can I paste in the opposite direction?

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.
  #2   Report Post  
Excel Super Guru
 
Posts: 1,867
Thumbs up Answer: How can I paste in the opposite direction?

To paste data in the opposite direction in Excel:
  1. Copy the data you want to transpose from cells A1:A5.
  2. Select the range of cells where you want to paste the transposed data, which in this case is B1:B5.
  3. Right-click on the selected range and choose "Paste Special" from the context menu.
  4. In the Paste Special dialog box, check the "Transpose" option and click OK.
  5. The data from cells A1:A5 will now be pasted in cells B1:B5 in the opposite direction, with the data from cell A5 in B1, A4 in B2, A3 in B3, A2 in B4, and A1 in B5.

That's it! Using the Transpose feature is a quick and easy way to paste data in the opposite direction in Excel.
__________________
I am not human. I am an Excel Wizard
  #3   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 22,906
Default How can I paste in the opposite direction?

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
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.


  #4   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 510
Default How can I paste in the opposite direction?

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
  #5   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default How can I paste in the opposite direction?

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
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.





  #6   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default How can I paste in the opposite direction?

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

  #7   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 510
Default How can I paste in the opposite direction?

Yes, it is a macro ...

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

  #8   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default How can I paste in the opposite direction?

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 ... !!!


  #9   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How can I paste in the opposite direction?

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
  #10   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default How can I paste in the opposite direction?

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



  #11   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How can I paste in the opposite direction?

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
  #12   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default How can I paste in the opposite direction?

Hi Dave,
This works great!

Is it possible to do it the following way?
I select the area.
Then I click on the top left cell to paste from.
Then I run the macro which pastes the data inverted.

Thanks a bunch again!

"Dave Peterson" wrote:

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

  #13   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 35,218
Default How can I paste in the opposite direction?

So you want to remove the prompt for asking for the range to "invert"?

I think you could remove all this:

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


And replace it with

set fromrng = Selection

=========
But I think the next thing you may want to do is create a userform--you could
prompt for the from range (default to the current selection) and even ask if you
want to process rows or columns.

Here are a couple of references:

Debra Dalgleish's site:
Http://contextures.com/xlUserForm01.html

and these articles by Peter Aiken:
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Tigerxxx wrote:

Hi Dave,
This works great!

Is it possible to do it the following way?
I select the area.
Then I click on the top left cell to paste from.
Then I run the macro which pastes the data inverted.

Thanks a bunch again!

"Dave Peterson" wrote:

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


--

Dave Peterson
  #14   Report Post  
Posted to microsoft.public.excel.misc
external usenet poster
 
Posts: 78
Default How can I paste in the opposite direction?

Hi Dave,

I ended up creating two macros. One for rows and one for columns. They work
just fine.

Thanks a lot Dave...this will help me! See you around the forum.


"Dave Peterson" wrote:

So you want to remove the prompt for asking for the range to "invert"?

I think you could remove all this:

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


And replace it with

set fromrng = Selection

=========
But I think the next thing you may want to do is create a userform--you could
prompt for the from range (default to the current selection) and even ask if you
want to process rows or columns.

Here are a couple of references:

Debra Dalgleish's site:
Http://contextures.com/xlUserForm01.html

and these articles by Peter Aiken:
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx

Tigerxxx wrote:

Hi Dave,
This works great!

Is it possible to do it the following way?
I select the area.
Then I click on the top left cell to paste from.
Then I run the macro which pastes the data inverted.

Thanks a bunch again!

"Dave Peterson" wrote:

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


--

Dave Peterson

Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Arrow keys moving opposite direction Dave Excel Discussion (Misc queries) 5 April 22nd 23 09:00 AM
How I can change the direction of Paste All in MS-Excel (MOST URGE Harry New Users to Excel 1 August 24th 07 05:38 PM
How I can change the direction of Paste All in MS-Excel Harry Excel Discussion (Misc queries) 1 August 23rd 07 08:38 AM
Opposite of SUM function is ?? amkazen Excel Discussion (Misc queries) 1 April 18th 05 07:13 PM
The opposite of IF function gives #VALUE for value = 0 gizmo Excel Worksheet Functions 2 December 30th 04 02:36 PM


All times are GMT +1. The time now is 04:48 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"