Copying Macro Needed

[John Calder]

Hi

I run Excel 2K

I need a macro that copies the contents of cell E7 to cell AG5 then the next
time I run the maco it copies the contents from cell E7 to cell AG6 then the
next time i run the macro it copies cell E7 to cell AG7 etc etc etc....

Thanks

 

[Dave Peterson]

I'd start at the bottom of the worksheet and look for the next open cell in
column AG.

Option Explicit
Sub testme()

Dim DestCell As Range
Dim wks As Worksheet

Set wks = ActiveSheet

With wks

If IsEmpty(.Range("E7").Value) Then
MsgBox "E7 is empty!" & vbLf & "quitting"
Exit Sub
End If

Set DestCell = .Cells(.Rows.Count, "AG").End(xlUp).Offset(1, 0)

'check to see if we went too far up
If DestCell.Row < 5 Then
Set DestCell = .Range("AG5")
End If

DestCell.Value = .Range("e7").Value
End With

End Sub

If you're new to macros:

Debra Dalgleish has some notes how to implement macros here:
http://www.contextures.com/xlvba01.html

David McRitchie has an intro to macros:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Ron de Bruin's intro to macros:
http://www.rondebruin.nl/code.htm

(General, Regular and Standard modules all describe the same thing.)

 

[FSt1]

hi
since you are moving down column AG, i'm assuming that column AG is empty.
try this...
Sub copyit()
Dim r As Range
Dim ro As Range
Set r = [E7]
Set ro = [AG65000].End(xlUp).Offset(1, 0)
ro.Value = r.Value
End Sub

this would go into a standard module.

Regards
FSt1