macro with relative ranges

**lschilkowski** · 05-10-2010, 04:36 PM

I have an Excel spreadsheet where the name, company, address, and city/state/zip fields are listed all in one column. (See example below.)

Tom Jones
ABC Graphics
123 Main Street
Anytown, ST 98765
John Smith
Alpha Omega
567 Blue Street
Yourtown, ST 78945

I need to copy four lines, move over one column, and paste special/transpose, then move to the next entry and start again. This is the macro I've been trying to use. I have not yet put the loop in, as I wanted to be sure it worked on the first entry before I attempted to have it run through the entire worksheet:

Sub RelativeCopyPasteTranspose()

StartCell = ActiveCell.Offset(0, 0).Address
EndCell = ActiveCell.Offset(3, 0).Address
Range(StartCell, EndCell).Select
Selection.Copy
ActiveCell.Offset(0, 1).Select
Selection.PasteSpecial Paste:=x1PasteAll, Operation:=x1None, SkipBlanks:= _
False, Transpose:=True
ActiveCell.Offset(5, -1).Select

End Sub

Every time I run it, I get an error that says "Runtime error '1004': PasteSpecial method of Range class failed." Anyone have any ideas what I'm doing wrong?

**JBeaucaire** · 05-10-2010, 04:53 PM

A macro is fine, but you don't need to do it that way. Just put this formula in B2 and copy down and across:

=INDEX($A:$A, COLUMN(A1)+(ROW(A1)-1)*4)

Don't edit any of those cell references, just put this formula in the first cell you want the table to start, then copy down/across.

Then copy the table, and Edit > Paste Special > Values

**snb** · 05-10-2010, 05:27 PM

And if you prefer a macro:

Please Login or Register  to view this content.

**lschilkowski** · 05-10-2010, 05:45 PM

Ok, I tried that and it didn't work quite right because I have an extra line in between each entry. I can't use Go To/Special/Blanks to delete them, as I also have addresses that are missing lines (no company mostly) and that would then reduce some entries to less than 4 lines. I thought maybe I could modify yours and change the 4 to a 5 and have it work, but it doesn't.

**lschilkowski** · 05-10-2010, 05:47 PM

And the macro also produces an error.

**snb** · 05-10-2010, 06:05 PM

Please Login or Register  to view this content.

PS. Please be more specific in your feedback ('produces an error' ??; in which line ??)

**JBeaucaire** · 05-10-2010, 06:27 PM

Actually, changing it 5 should work based on the information you've provided so far.

You can post a sample workbook so we can see your data. You can replace names with Name1, Name2, etc. People invariably have things in their datasets they don't tell us about, until we're all looking at the same thing it may be impossible to get it exactly right.

Click GO ADVANCED and use the paperclip icon to post up your workbook.

**lschilkowski** · 05-10-2010, 07:18 PM

I was in a rush and ended up doing that one manually. I can make things even more difficult for you now though if you really like a challenge! LOL

Now I have a list about 9000-10000 rows long, close to the same format. I don't want to copy/paste or share the workbook, as it's nothing but names, addresses, phone numbers, email addresses, etc. of our clients. This one has no blank lines in between. The trick to this one is not every entry has the same number of rows. For instance, John Smith may have his name, then company, then address, then city/state/zip, then phone, then email and that's it. Mary Jones though may have her name, company, address, city/state/zip, phone, fax, email, and website. So, I have no way to count how many rows per entry. The only distinguishing characteristic about the new names is names and company names are all in bold. Any ideas on that one?