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

On Dec 31, 11:47*pm, Lars-Åke Aspelin
wrote:
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!

Try this macro:

Private Sub Worksheet_Change(ByVal Target As Range)
* *If Target.Column = 1 Then
* * *Target.Offset(0, 1).Resize(1, 5).Copy
* * *Target.Offset(1, 1).PasteSpecial Paste:=xlPasteFormulas
* * *Application.CutCopyMode = False
* *End If
End Sub

Hope this helps / Lars-Åke

Wow! I can't even tell what is happening there. Can you explain how it
realizes the change in the cell and then copies the formulas down?

Many thanks,
_Sean

The Worksheet_Change sub is a predefined macro that is triggered
whenever there are any changes in any cells of the worksheet.

The (also predefined) parameter Target holds a reference to the cell
that has been changed.
In this case we are only interested in changes to column A (which has
the value 1) therefore the If statement.

The remaining three statements is the copying of Bn:Fn to Bn+1:Fn+1.
Example: If changes are made to cell A2 then
Target = A2
Target.Column = 1 (so this should be handled)
Target.Offset(0,1) = B2
Target.Offset(0,1).Resize(1,5) = B2:F2
Target.Offset(1,1) = B3

Application.CutCopyMode = False
is just to end the copying mode.

Lars-Åke