• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 395
  • Last Modified:

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
0
KP_SoCal
Asked:
KP_SoCal
  • 3
  • 3
1 Solution
 
Arno KosterCommented:
2)

Rows(Selection.Row).copy

Open in new window

0
 
Arno KosterCommented:
this will do if you place it in the module :
Public Sub process()
Dim MyList As String
Dim arr As Variant
Dim ws As Worksheet
Dim rng As String
Dim sel As Integer
Dim n As Integer
    
    
    '-- determine required number of rows
    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
    
    '-- generate list of worksheets to process
    MyList = "Summary,Details"
    For Each ws In ThisWorkbook.Worksheets
        If LCase(ws.Name) Like "zone*" Then
            MyList = MyList & "," & ws.Name
        End If
    Next
    arr = Split(MyList, ",")
    
    '-- copy the contents
    sel = Selection.Row
    ThisWorkbook.ActiveSheet.Rows(sel).Copy
    
    '-- perform the insert on selected worksheets
    For Each ws In Worksheets(arr)
        '-- insert rows
        ws.Rows(sel & ":" & sel + CInt(rng) - 1).Insert
        '-- insert formulae
        n = ws.Cells(sel - 1, 256).End(xlToLeft).Column
        ws.Range(ws.Cells(sel - 1, 1), ws.Cells(sel - 1 + CInt(rng), n)).FillDown
    Next ws
        
End Sub

Open in new window

0
 
KP_SoCalAuthor Commented:
This looks great.  Thanks!
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
KP_SoCalAuthor Commented:
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
0
 
KP_SoCalAuthor Commented:
Akoster, disregard.  I'm good-to-go now.  Thanks again for your help.
0
 
Arno KosterCommented:
no problem. thanks for the update !
0

Featured Post

Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now