Little help please? How do I.....

[Red_Dragon]

This is probably something easy for y'all, but I am beating my head against a wall here, just can't figure it out! Little help please? I am trying to modify a recorded Macro to move information from a "Summary" page to an individual history page. I am stuck at how to get the macro to drop down a row so as to ADD the new info in the same colums, one row down. Here is what I have worked out so far...

Range("C27").Select
ActiveCell.FormulaR1C1 = "=Summary!Venus_Refuse"
Range("D27").Select
ActiveCell.FormulaR1C1 = "=Summary!Venus_Regurge"
Range("B27").Select
ActiveCell.FormulaR1C1 = "=Summary!Venus_feed"
Range("B28").Select

Now what I want to do is have it drop down one row each time the macro is run.....so that the next time it is run it records the info across row 28, then drops the active cell to B29. Then the next time the macro is run it would record the info across row 30.....ect.

Any help please?

 

[Jay Petrulis]

On 2002-04-23 10:39, Red_Dragon wrote:
This is probably something easy for y'all, but I am beating my head against a wall here, just can't figure it out! Little help please? I am trying to modify a recorded Macro to move information from a "Summary" page to an individual history page. I am stuck at how to get the macro to drop down a row so as to ADD the new info in the same colums, one row down. Here is what I have worked out so far...

Range("C27").Select
ActiveCell.FormulaR1C1 = "=Summary!Venus_Refuse"
Range("D27").Select
ActiveCell.FormulaR1C1 = "=Summary!Venus_Regurge"
Range("B27").Select
ActiveCell.= "=Summary!Venus_feed"
Range("B28").Select

Now what I want to do is have it drop down one row each time the macro is run.....so that the next time it is run it records the info across row 28, then drops the active cell to B29. Then the next time the macro is run it would record the info across row 30.....ect.

Any help please?

Try,

Sub test()
Dim lastrow As Long

With ActiveSheet
.UsedRange
lastrow = .Cells.SpecialCells(xlCellTypeLastCell).Row
.Cells(lastrow + 1, 2) = "=Summary!Venus_feed"
.Cells(lastrow + 1, 3) = "=Summary!Venus_Refuse"
.Cells(lastrow + 1, 4) = "=Summary!Venus_Regurge"
End With
End Sub


Bye,
Jay

 

[Red_Dragon]

Thank you. That actually didn't work, but you pointed me in the right direction I think. I wasn't even sure what to look for in the help menus, but your code helped me see what to look for. Now I've got it to change cells for me, I have to tweak it a bit more, but I'm much closer! Thanks for the help.

What I have now:

Public Sub VenusUpdate()
Worksheets("Venus").Activate
ActiveCell.offset(rowOffset:=1, columnOffset:=-6).Activate
ActiveCell.FormulaR1C1 = "=Summary!Venus_feed"
ActiveCell.offset(columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = "=Summary!Venus_Refuse"
ActiveCell.offset(columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = "=Summary!Venus_Regurge"
ActiveCell.offset(columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = "=Summary!Venus_Preshed"
ActiveCell.offset(columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = "=Summary!Venus_Shed"
ActiveCell.offset(columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = "=Summary!Venus_General"
ActiveCell.offset(columnOffset:=1).Activate
ActiveCell.FormulaR1C1 = "=Summary!Venus_Notes"
Worksheets("Summary").Activate
End Sub