asked on

Dialog input box to insert number of rows on selected sheets

Below is a list of steps outlining what I’m trying to accomplish:

1. Select a range of specified worksheets using the following subroutine:

Public Sub SelectSheets()
    Dim MyList As String
    Dim arr As Variant
    Dim ws As Worksheet
     
    MyList = "Summary,Details"
    
    With ThisWorkbook
        For Each ws In .Worksheets
            If LCase(ws.Name) Like "zone*" Then
                MyList = MyList & "," & ws.Name
            End If
        Next
    
        arr = Split(MyList, ",")
    
        .Worksheets(arr).Select
        .Worksheets("Summary").Activate
    End With
End Sub

Open in new window

2. Copy the contents (formulas, values, formatting, and etc) in the row of the cell that I have selected (i.e. whether I have C4, A4, B4, or D4 selected; it would copy all the contents of row 4.

3. Trigger a dialog box that allows me to input the number of rows I’d like insert between the row that has been copied and the row above it. Below is a nice code that is very close to what I need, but may need some very small adjustments.

Public Sub InsertRows()
    Dim Rng, n As Long, k As Long
    
    Application.ScreenUpdating = False
    
    Rng = InputBox("Enter number of rows required." & vbCrLf & vbCrLf & _
    "All formulas and formatting in row above current position of cursor will be copied.")
    
    If Rng = "" Then Exit Sub
             
    Range(ActiveCell, ActiveCell.Offset(Val(Rng) - 1, 0)).EntireRow.Insert
    'need To know how many  formulas To copy down.
    'Assumesfrom A over To last entry In row.
    k = ActiveCell.Offset(-1, 0).Row
    n = Cells(k, 256).End(xlToLeft).Column
    Range(Cells(k, 1), Cells(k + Val(Rng), n)).FillDown

    Application.ScreenUpdating = True

End Sub

Open in new window

4. The insert should be applied on all selected worksheets

As always, I’m grateful for any proposed solutions. Thanks!
Sample-File.xlsm

Arno Koster

Rows(Selection.Row).copy

Open in new window

ASKER CERTIFIED SOLUTION

Arno Koster

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

KP_SoCal

ASKER

This looks great. Thanks!

KP_SoCal

ASKER

Akoster,
This is very close to what I need, but I noticed that the formulas are not inheriting their new reference after the insert is complete. For instance, the formula in cell A8 is “=SUM(‘Zone XY:Zone 8B’!A8)”.

If I were to manually select the ‘Summary’ and ‘Zone*’ worksheets and manually insert 5 rows starting at row 3 (without copying anything), the formula would correctly reflect “=SUM(‘Zone XY:Zone 8B’!A13)”. However with this macro “process()”, it remains as “=SUM(‘Zone XY:Zone 8B’!A8)”. Please see attached file.

One way around this, I believe, is to first insert the required rows and then copy the targetted row and paste the results into the rows I just inserted. When I execute this manually, it works. I'm just not sure how to incorporate it into your suggested macro. Any additional help you can provide on this would be greatly appreciated. Thanks!

Sample-File-v2.xlsm

KP_SoCal

ASKER

Akoster, disregard. I'm good-to-go now. Thanks again for your help.

Arno Koster

no problem. thanks for the update !