Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to create a copy/paste special/transpose macro?
I have a 1300 row sheet of 650 addresses on 2 rows/1 column each (1st
address in A1, A2-----2nd address B1, B2, etc) Right now I am copying the 2 cellsPaste SpecialTranspose Is there a way to record a macro to do that? Will that allow me to then click on the A1, then B1, then C1, etc and run the macro using a single keystroke? Or is there another better way? I don't know VBA at all, though. ....Lisa |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to create a copy/paste special/transpose macro?
I know it's bad form to reply to your own messages but I got this far:
Sub transpose() ' ' transpose Macro ' Macro recorded 8/7/2007 by Lisa Hetherington ' ' Keyboard Shortcut: Ctrl+t ' Range("A222:A223").Select Selection.Copy Range("B220").Select Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _ False, transpose:=True End Sub But I need the macro to work on whatever cell I click on. Is that called a relative "something or other"? ....Lisa On Aug 7, 10:46 pm, " wrote: I have a 1300 row sheet of 650 addresses on 2 rows/1 column each (1st address in A1, A2-----2nd address B1, B2, etc) Right now I am copying the 2 cellsPaste SpecialTranspose Is there a way to record a macro to do that? Will that allow me to then click on the A1, then B1, then C1, etc and run the macro using a single keystroke? Or is there another better way? I don't know VBA at all, though. ...Lisa |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to create a copy/paste special/transpose macro?
I'm awful!!
The addresses are 2 rows each but are in A1, A2 then A3, A4 then A5, A6 Thank you for anyyone who's reading this and is still willing to help me out despite my confusing posts!!!!! I want the macro to paste the contents in the B column, ie I want A1, A2 to be transposed into B1 and C1 ....Lisa On Aug 7, 10:46 pm, " wrote: I have a 1300 row sheet of 650 addresses on 2 rows/1 column each (1st address in A1, A2-----2nd address B1, B2, etc) Right now I am copying the 2 cellsPaste SpecialTranspose Is there a way to record a macro to do that? Will that allow me to then click on the A1, then B1, then C1, etc and run the macro using a single keystroke? Or is there another better way? I don't know VBA at all, though. ...Lisa |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to create a copy/paste special/transpose macro?
Please check your addressing again. You say you have 650 addresses in 1300
rows. So I'm thinking A1 = addr1 pt1 A2 = addr1 pt2 A3 = addr2 pt1 A4 = addr2 pt2 Which would use 1300 rows for 650 addresses. But you've tossed in column B and C. I need a little better picture. Maybe you have: A B 1 addr1 pt1 addr326 pt1 2 addr1 pt2 addr326 pt2 3 addr2 pt1 addr327 pt1 4 addr2 pt2 addr327 pt2 .... on down to row 650? and how do you want them to end up arranged? All in just column C? In which case it would be a simple matter of copying and pasting the two lists end to end. " wrote: I have a 1300 row sheet of 650 addresses on 2 rows/1 column each (1st address in A1, A2-----2nd address B1, B2, etc) Right now I am copying the 2 cellsPaste SpecialTranspose Is there a way to record a macro to do that? Will that allow me to then click on the A1, then B1, then C1, etc and run the macro using a single keystroke? Or is there another better way? I don't know VBA at all, though. ....Lisa |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to create a copy/paste special/transpose macro?
In view of this latest info, this macro will give you two columns(A and B) of
transposed cells. Sub ColtoRows() Dim Rng As Range Dim i As Long Dim J As Long Set Rng = Cells(Rows.Count, 1).End(xlUp) J = 1 For i = 1 To Rng.Row Step 2 Cells(J, "A").Resize(1, 2).Value = _ Application.Transpose(Cells(i, "A").Resize(2, 1)) J = J + 1 Next End Sub Gord Dibben MS Excel MVP On Tue, 07 Aug 2007 20:08:54 -0700, " wrote: I'm awful!! The addresses are 2 rows each but are in A1, A2 then A3, A4 then A5, A6 Thank you for anyyone who's reading this and is still willing to help me out despite my confusing posts!!!!! I want the macro to paste the contents in the B column, ie I want A1, A2 to be transposed into B1 and C1 ...Lisa On Aug 7, 10:46 pm, " wrote: I have a 1300 row sheet of 650 addresses on 2 rows/1 column each (1st address in A1, A2-----2nd address B1, B2, etc) Right now I am copying the 2 cellsPaste SpecialTranspose Is there a way to record a macro to do that? Will that allow me to then click on the A1, then B1, then C1, etc and run the macro using a single keystroke? Or is there another better way? I don't know VBA at all, though. ...Lisa |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to create a copy/paste special/transpose macro?
see my above correction
sorry for the confusion ....Lisa On Aug 7, 11:14 pm, JLatham <HelpFrom @ Jlathamsite.com.(removethis) wrote: Please check your addressing again. You say you have 650 addresses in 1300 rows. So I'm thinking A1 = addr1 pt1 A2 = addr1 pt2 A3 = addr2 pt1 A4 = addr2 pt2 Which would use 1300 rows for 650 addresses. But you've tossed in column B and C. I need a little better picture. Maybe you have: A B 1 addr1 pt1 addr326 pt1 2 addr1 pt2 addr326 pt2 3 addr2 pt1 addr327 pt1 4 addr2 pt2 addr327 pt2 ... on down to row 650? and how do you want them to end up arranged? All in just column C? In which case it would be a simple matter of copying and pasting the two lists end to end. " wrote: I have a 1300 row sheet of 650 addresses on 2 rows/1 column each (1st address in A1, A2-----2nd address B1, B2, etc) Right now I am copying the 2 cellsPaste SpecialTranspose Is there a way to record a macro to do that? Will that allow me to then click on the A1, then B1, then C1, etc and run the macro using a single keystroke? Or is there another better way? I don't know VBA at all, though. ....Lisa |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
how to create a copy/paste special/transpose macro?
Thanks!!
....Lisa On Aug 7, 11:33 pm, Gord Dibben <gorddibbATshawDOTca wrote: In view of this latest info, this macro will give you two columns(A and B) of transposed cells. Sub ColtoRows() Dim Rng As Range Dim i As Long Dim J As Long Set Rng = Cells(Rows.Count, 1).End(xlUp) J = 1 For i = 1 To Rng.Row Step 2 Cells(J, "A").Resize(1, 2).Value = _ Application.Transpose(Cells(i, "A").Resize(2, 1)) J = J + 1 Next End Sub Gord Dibben MS Excel MVP On Tue, 07 Aug 2007 20:08:54 -0700, " wrote: I'm awful!! The addresses are 2 rows each but are in A1, A2 then A3, A4 then A5, A6 Thank you for anyyone who's reading this and is still willing to help me out despite my confusing posts!!!!! I want the macro to paste the contents in the B column, ie I want A1, A2 to be transposed into B1 and C1 ...Lisa On Aug 7, 10:46 pm, " wrote: I have a 1300 row sheet of 650 addresses on 2 rows/1 column each (1st address in A1, A2-----2nd address B1, B2, etc) Right now I am copying the 2 cellsPaste SpecialTranspose Is there a way to record a macro to do that? Will that allow me to then click on the A1, then B1, then C1, etc and run the macro using a single keystroke? Or is there another better way? I don't know VBA at all, though. ...Lisa |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Copy & Paste Special Macro | Excel Discussion (Misc queries) | |||
Paste Special / transpose | Excel Discussion (Misc queries) | |||
When I special paste and transpose.... | New Users to Excel | |||
Copy Paste Special Macro | Excel Discussion (Misc queries) | |||
HELP....Is there a way to automate copy/paste special/transpose - | Excel Worksheet Functions |