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 Sub

It 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