I'm trying to get this macro to loop and have no idea what I need to do do to get the macro to keep repeating until it gets to the last item in a list.
I have a list of addresses (some are 3 rows long, some 4 and some 5). And I want to Copy each one then paste special in the empty cell between it & the next address entry.
Here's the code for my simple macro that does one copy & paste ... I have to do each one manually & would like to modify the macro to go all the way to the end of my list.
I read that there are 4 types of loops (While Loop, For Loop, Do While, ??) ... don't know how to choose which one or how to use/format with my exisiting macro.
Sub Transpose_j()
'
' Transpose_j Macro
' Transpose
'
' Keyboard Shortcut: Ctrl+j
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub
My list looks like this before I run the macro ... only longer:
American Community Bank
300 Glen Street
Glen Cove, NY 11542 3070
American Stock Transfer & Trust Company
59 Maiden Lane
New York, NY 10038 4667
Mail To : 59 Maiden Lane, Plaza Level
New York, NY 10038 4667
American Stock Transfer & Trust Company,
LLC
59 Maiden Lane
New York, NY 10038 4667
Anthos Trust Company, LLC
277 Park Avenue
New York, NY 10172
Banco Popular North America
7 West 51st Street
New York, NY 10019
Mail To : 120 Broadway
New York, NY 10271
My goal is to use paste special to transpose each one & then I'll have something that looks like this:
<TABLE style="WIDTH: 369pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=491 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Community Bank</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>300 Glen Street</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>Glen Cove, NY 11542 3070</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 465pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=619 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Stock Transfer & Trust Company</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>59 Maiden Lane</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>New York, NY 10038 4667</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Mail To : 59 Maiden Lane, Plaza Level</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>New York, NY 10038 4667</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 417pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=555 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Stock Transfer & Trust Company,</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>LLC</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>59 Maiden Lane</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>New York, NY 10038 4667</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 369pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=491 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>Anthos Trust Company, LLC</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>277 Park Avenue</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>New York, NY 10172</TD></TR></TBODY></TABLE>
I don't care if it delete's the oringial rows or not ... I can always sort to get rid of those.
Thanks in advance!
I have a list of addresses (some are 3 rows long, some 4 and some 5). And I want to Copy each one then paste special in the empty cell between it & the next address entry.
Here's the code for my simple macro that does one copy & paste ... I have to do each one manually & would like to modify the macro to go all the way to the end of my list.
I read that there are 4 types of loops (While Loop, For Loop, Do While, ??) ... don't know how to choose which one or how to use/format with my exisiting macro.
Sub Transpose_j()
'
' Transpose_j Macro
' Transpose
'
' Keyboard Shortcut: Ctrl+j
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Selection.End(xlDown).Select
ActiveCell.Offset(1, 0).Range("A1").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
End Sub
My list looks like this before I run the macro ... only longer:
American Community Bank
300 Glen Street
Glen Cove, NY 11542 3070
American Stock Transfer & Trust Company
59 Maiden Lane
New York, NY 10038 4667
Mail To : 59 Maiden Lane, Plaza Level
New York, NY 10038 4667
American Stock Transfer & Trust Company,
LLC
59 Maiden Lane
New York, NY 10038 4667
Anthos Trust Company, LLC
277 Park Avenue
New York, NY 10172
Banco Popular North America
7 West 51st Street
New York, NY 10019
Mail To : 120 Broadway
New York, NY 10271
My goal is to use paste special to transpose each one & then I'll have something that looks like this:
<TABLE style="WIDTH: 369pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=491 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Community Bank</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>300 Glen Street</TD><TD class=xl64 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>Glen Cove, NY 11542 3070</TD></TR></TBODY></TABLE>
<TABLE style="WIDTH: 465pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=619 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 48pt" span=2 width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Stock Transfer & Trust Company</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>59 Maiden Lane</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>New York, NY 10038 4667</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>Mail To : 59 Maiden Lane, Plaza Level</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>New York, NY 10038 4667</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 417pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=555 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>American Stock Transfer & Trust Company,</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>LLC</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>59 Maiden Lane</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 48pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=64>New York, NY 10038 4667</TD></TR></TBODY></TABLE><TABLE style="WIDTH: 369pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=491 summary=format border=0><COLGROUP><COL style="WIDTH: 129pt; mso-width-source: userset; mso-width-alt: 6290" width=172><COL style="WIDTH: 83pt; mso-width-source: userset; mso-width-alt: 4022" width=110><COL style="WIDTH: 157pt; mso-width-source: userset; mso-width-alt: 7643" width=209><TBODY><TR style="HEIGHT: 15pt" height=20><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 129pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 15pt; BACKGROUND-COLOR: transparent" width=172 height=20>Anthos Trust Company, LLC</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 83pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=110>277 Park Avenue</TD><TD class=xl65 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 157pt; BORDER-BOTTOM: #ece9d8; BACKGROUND-COLOR: transparent" width=209>New York, NY 10172</TD></TR></TBODY></TABLE>
I don't care if it delete's the oringial rows or not ... I can always sort to get rid of those.
Thanks in advance!