Thread: Creating a template row ...

Every time I need add something to the sheet I need copy a row with all the formulas and formatting and change it contents, is there any way each new row has a pre defined template with formatting and formulas?

Hi, in Excel 2003 you can define your data table as a List. Highlight the table, including the column labels and then click Data - List - Create List and then hit OK.

Now you should see a blue border around the data table. When you click inside the table, there will be a new empty row added to the bottom of the list, where formatting and calculations are automatically applied as in the cells above.

Starting from your screenshot:

place the cursor in cell A23
start the macro recorder
set it to relative cell referencing
copy the two rows above the active cell
do your paste and tidy up thing
stop the macro recorder

Next time you want to insert a new set of rows, place the cursor in Column A in the next empty row and run the macro.

Lots of fine tuning can be done here, but this should give you a starting point. You could create a button somewhere on the sheet that runs the macro, you could use code to find the last empty cell by itself ....

try this in the worksheet

Private Sub Worksheet_Change(ByVal Target As Range)
  
    
    Dim TargetCell As Range, N As Long
    
    On Error GoTo FormulaFillFail
   
    If (Not IsNumeric(Target.Value)) Or IsEmpty(Target) Then
        
        Exit Sub
    End If
   
    For Each TargetCell In Intersect(ActiveSheet.UsedRange, Target.EntireRow)
       

        If TargetCell.Value = "" Then
           
            For N = 1 To TargetCell.Row - 1
                
                If TargetCell.Offset(-N, 0).HasFormula Then
                    
                    TargetCell.Offset(-N, 0).Copy Destination:=TargetCell.Offset(-N + 1, 0).Resize(N, 1)
                   
                    Exit For
                End If
            Next N
        End If
     
    Next TargetCell
    
    
   
   Exit Sub
    
    
FormulaFillFail:


End Sub

i think its an old "Jack in the UK" code