On Jan 1, 12:13*am, Gord Dibben <gorddibbATshawDOTca wrote:
Try this in your sheet module.

Assumes you have formulas in B1:F1

Private Sub Worksheet_Change(ByVal Target As Excel.Range)
On Error GoTo enditall
* * Application.EnableEvents = False
If Target.Cells.Column = 1 Then
* * * * n = Target.Row
If Me.Range("A" & n).Value < "" Then
With Target
.Offset(-1, 1).Resize(, 5).Copy _
* * * * * * *Destination:=.Offset(, 1)
End With
* * * * End If
* * End If
enditall:
* * Application.EnableEvents = True
End Sub

Gord Dibben *MS Excel MVP

On Wed, 31 Dec 2008 12:24:42 -0800 (PST), S Davis
wrote:

Hey, sorry for the title. It's late here in Africa. Happy new year
btw!

I need two relatively simple things.

1) I have a recorded macro that just copies a bunch of cells and then
pastes them into the row below. Trouble is, they have direct cell
references (ie. "B3:F3") which I would rather just be referred to as
offsets from the activecell. Unsure how to do this in VBA though.

2) I would only like this macro to initiate once text is entered into
a cell in column A. For instance, if the user enters text into cell
A2, the macro should then initiate, copy all formulas from B2:F2, and
paste them into B3:F3. If the user then enters text into A3, it should
follow that it copies B3:F3 and pastes them into B4:F4.

In summary:
Once text is entered in to cell A2, the macro initiates and copies
cells B2:F2, pastes them into B3:F3, and then selects A3 to end the
macro. Of course, not ever referencing the cell by its name directly.

The purpose for all this is to save file size of course, rather than
having 65000*5 cells sitting there with inactive formulas jsut waiting
for text to go into the A column. I hope this makes sense. Thanks!

Much nicer, thank you Mr. Savant. I love this group :) Thank you Gord
and JLWhiz as well, just saved me 7 meg of space and taught me a ton!
-Sean in Africa