Macro to move cells to new column?

Posted by Craig Rothfuss on July 09, 2001 10:47 AM

I have several data files consisting of 1 column - every 7 cells down contain info that I have to get into columns: i.e.

name
address
city
state
zip...

gotta wind up with:

name address etc...

How can I programatically select (n) cells down and move 'em to columns next to cell #1?? Tried a macro, but I can't get it to move to the next set of 7 properly!

The answer might be somewhere in this message board already, but it's HUGE! How about a search function?

THANX!

Posted by Ben O. on July 09, 2001 11:48 AM

Craig,

Try this. It will create a new sheet and transpose every 7th row (row 1, row 8, row 15, etc.) from your original sheet onto your new sheet:

Sub TransSheet()
sSheet = ActiveSheet.Name
Sheets.Add
dSheet = ActiveSheet.Name
For x = 1 To 5000 Step 7
Sheets(sSheet).Activate
Range(Cells(x, 1), Cells(x, 1).Offset(4, 0)).Select
Selection.Copy
Sheets(dSheet).Activate
Selection.PasteSpecial Paste:=xlAll, Operation:=xlNone, SkipBlanks:=False _
, Transpose:=True
ActiveCell.Offset(1, 0).Select
Next x
End Sub

Just change the 5000 in this line:

For x = 1 To 5000 Step 7

to the last row of your data.

Select the sheet your data is on and run it. I tested it on some sample data and it worked. if it runs too slow insert and Application.ScreenUpdating = False at the beginning of the code.

-Ben

Posted by Craig Rothfuss on July 09, 2001 11:52 AM

Ben:

THANX for you help! It's greatly appreciated. You 'da man... :-)