Copy row details Automatically a number of times as defined by a value in a cell

[bob_caw]

I want to copy formula & formatting from say Row 3, Clm C to R to the next row a number of times designated by the value populated into a cell (say B1). ie if B1=4 i want to copy Row 3 (Clm C-R), to the next 4 rows, (Row4,5,6 & 7).
Note i want the copy to happen automatically when the detail is entered into cell B1

I am using Excel 2007
Any Ideas would be great
Thanks
BOB

 

[Peter_SSs]

Welcome to the MrExcel board!

See if this is what you want. To implement ..

1. Right click the sheet name tab and choose "View Code".

2. Copy and Paste the code below into the main right hand pane that opens at step 1.

3. Close the Visual Basic window.

4. Try entering a number in B1

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim mycell As Range, Sel As Range, ActCell As Range
    
    Set mycell = Range("B1")    '<-- The cell you want to trigger this action
    
    If Not Intersect(Target, mycell) Is Nothing Then
        If IsNumeric(mycell.Value) Then
            If mycell.Value >= 1 Then
                Set Sel = Selection
                Set ActCell = ActiveCell
                Application.EnableEvents = False
                Range("C3:R3").Copy
                With Range("C4:R4").Resize(mycell.Value)
                    .PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                    .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
                        SkipBlanks:=False, Transpose:=False
                End With
                Application.CutCopyMode = False
                Sel.Select
                ActCell.Activate
                Application.EnableEvents = True
            End If
        End If
    End If
End Sub