Excel- copy and pasting

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:

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!

coolcatch:
========

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

This may get you at least started:

Code:

---

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 Sub

---

It is a primitive example of iterating through cells programatically, and referencing a range of cells.

Good luck in your efforts.

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?

hi coolcatch

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

VB Code:

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

VB Code:

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

i hope this could help you

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

anyone for any ideas?

VB Code:

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