If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Copying Macro Needed
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 |
#2
|
|||
|
|||
Copying Macro Needed
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 he 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.) John Calder wrote: 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 |
#3
|
|||
|
|||
Copying Macro Needed
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 "John Calder" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|