We help IT Professionals succeed at work.

Macro to replicate set of rows with formulas based on entry in another cell.

agwalsh used Ask the Experts™
I have attached a form that has a number of shelves marked in yellow (F3) in rows 3 and 4 (merging cells - their idea not mine :-)). How would I create a macro that would replicate the section (starting in cell A19 to E23) for the same number of shelves as in F3 e.g. if there were 2 in F3 , the sections would only appear twice.  Thank you
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Mechanical Engineer
Most Valuable Expert 2013
Top Expert 2013
I could have designed the macro to run when the value in cell F3 is changed. That didn't appear to be what you wanted, so I added a button to call the macro.

As written, the code clears any data from shelves 2 through n. It then adds the required number of shelves, numbers them and adds probe positions 1 through 5.
Sub Reshelf()
Dim i As Long, j As Long, n As Long, nRows As Long
Dim rg As Range, rgg As Range

Application.ScreenUpdating = False

With ActiveSheet
    Set rg = .Range("A19:H23")
    nRows = rg.Rows.Count
    n = .Range("F3").Value
    If rg.Cells(nRows + 1, 2).Value <> "" Then
        Set rgg = rg.Cells(nRows, 2).End(xlDown)
        Set rgg = Range(rg.Cells(nRows + 1, 1), rgg.EntireRow.Cells(rg.Columns.Count))
    End If
    If n > 1 Then
        For i = 2 To n
            rg.Offset(nRows * (i - 1), 0).PasteSpecial
            For j = 1 To nRows
                rg.Cells(nRows * (i - 1) + j, 3).MergeArea.ClearContents
            rg.Cells(nRows * (i - 1) + 1, 1).Value = i
    End If
End With
End Sub

Open in new window



This is brilliant!!! I love it Thank you :-)