Excel- copy and pasting

[coolcatch]

Basically i have all my contact information in a spread sheet, in one gigantically long row of like 2000 cells, and what i want to do it write a macro that will take the 7 sections that each contanct information is long, and past it below the other one in the A column, so instead of one gigantically long line i have a long row or the contacts each lined up one after the other, i understand this probably needs a variable to plus one to make sure its pasted in the row below each time, and set it in a loop, but how the hell do i do this with the stupid letter in the cell name and everything, so far i have this, which is jsut the reorded macro info, but i need it not to do it twice like i have it, but like a thousand times so.... heres the code:

Sub Macro5()
'
' Macro5 Macro
' Macro recorded 23/05/2005 by cool
'
' Keyboard Shortcut: Ctrl+q

Range("H1:N1").Select
Selection.Copy
Range("A2").Select
ActiveSheet.Paste
Range("H1:N1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
Range("H1:N1").Select
Selection.Copy
Range("A3").Select
ActiveSheet.Paste
Range("H1:N1").Select
Application.CutCopyMode = False
Selection.Delete Shift:=xlToLeft
End Sub


Please help me work this out!

[Webtest]

coolcatch:
========

Ahh, grasshopper, you've discovered that you can not reference cells programatically with recorded Macros!

This may get you at least started:Option Explicit
Sub Iter_Test()
Dim irow As Integer 'Row Counter
Dim icol As Integer 'Column Counter
Dim arange As Range 'A Range Object Variable

icol = 2 'Initialize the Column Counter
'Iterate down the column showing the cell addresses and contents
For irow = 1 To 10
MsgBox (Cells(irow, icol).Address & " | " & Cells(irow, icol).Value)
Next irow
'Adjust irow back to the last iterated row number
irow = irow - 1
'Here is a sample of setting a range of cells programatically
Set arange = Range(Cells(1, icol), Cells(irow, icol))
MsgBox (arange.address)
End SubIt is a primitive example of iterating through cells programatically, and referencing a range of cells.

Good luck in your efforts.

[coolcatch]

could u just talk me through how exactly i would get that (what you have written) to help me do what i want to do, i understand the vairables u defined but then what do i do about the actual copying and pasting of the cells in excel, where do i implement that, and how exactly do i define a range of cells, say from 1 to 2000 in the row?

[asmdev]

hi coolcatch

if u use vb macro wizard to record copy paste, then what u would get is like below

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 25/05/2005 by USER
'
'
Range("C10").Select
Selection.Copy
Sheets("Sheet2").Select
Range("C8").Select
ActiveSheet.Paste
End Sub


which is kinda long, but if u code urself, what u might have is probably like below

Sub mycopy()
Sheet1.Range("C10").Copy
Sheet2.Range("C20").PasteSpecial xlPasteValues
End Sub


i hope this could help you

[coolcatch]

thanks asmdev but my problem is not the copying and pasting, its trying to get the thing to copy and paste about 2000 cells in one row, down in a vertical column in my groups of seven, i really dont wanna do that by hand, so id prefer to have it just so that it keeps moving the entire row down to the left and taking the 7 cells next to my first set of cells and pasting them below in the a column, and then the next seven below that, and the next seven below that, see my picture if u dont understand

[coolcatch]

anyone for any ideas?

[westconn1]

f = 7 'no of entries per listing +1
Range("a1").Activate
Selection.End(xlToRight).Select
lcell = ActiveCell.Column
crow = ActiveCell.Row
Do

Range(Cells(crow, f + 1), Cells(crow, lcell)).Select
crow = crow + 1: lcell = lcell - f
Selection.Cut Destination:=Range(Cells(crow, 1), Cells(crow, lcell))
Loop While lcell > f


this seems to work, but you might have to allow for a header row

pete