Template Help

P

Pablo

I am creating a form that has functions in various cells
of row 2. Row 1 is the header. I would like to create a
template that will allow users to enter information on
Rows 3..., and to carrying the cell functions to the new
rows.

Pablo
 
D

Dave Peterson

You may want to look at Data|Form. It gives the users a little dialog that
prompts for new values in new records and it copies the formulas down, too.

Alternatively, you could have an event macro looking for a change to the
worksheet.

I picked out column A. As soon as xl sees a change in that column, it'll B2:IV2
to the new row. And then wipe out the constants (B:IV).

If you like this idea, then right click on the worksheet tab that should have
this. Select View code and paste this in:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngToCopy As Range

If Target.Cells.Count > 1 Then Exit Sub
If Intersect(Target, Range("A:A")) Is Nothing Then Exit Sub

If Application.CountA(Target.Offset(0, 1) _
.Resize(1, Columns.Count - 1)) > 0 Then
'already has data
Exit Sub
End If

Set rngToCopy = Range("B2").Resize(1, 255)

Application.EnableEvents = False
rngToCopy.Copy _
Destination:=Target.Offset(0, 1)

On Error Resume Next
Target.Offset(0, 1).Resize(255).Cells _
.SpecialCells(xlCellTypeConstants).ClearContents
On Error GoTo 0
Application.EnableEvents = True

End Sub

If you like the data|form suggestion, you could put a macro in your auto_open
that shows the form when the user opens the workbook:

Option Explicit
Sub auto_open()
With Worksheets("sheet1")
.Select
.ShowDataForm
End With
End Sub

If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm
 
Top