VBA to insert rows

Posted on 2011-10-31
Last Modified: 2012-05-12
I've posted a thread that is related to this question, but I want to bring it in a different direction and add a little more clarity behind what I'm trying to accomplish.

I’m looking for a code that will select a specific range of worksheets that includes: ‘Summary’, ‘Details’, and sheet names that start with ‘Zone’, and insert a specified number of rows immediately below the row with the active cell.  The number of rows to insert should be referenced from a message input box that requires a user to type how many rows are required to be inserted.

Next it should copy the contents of the row containing the active cell and paste them into the rows that were just inserted.  The reason for inserting prior to copying is to ensure the formulas that reference other worksheets refer to the correct cell row.  If I were to copy and then insert, the formulas would not update correct.

If I were to manually execute this without a macro, the steps would go like this:
1. In the attached file, notice Summary!A8 has a formula of SUM('Zone XY:Zone 8B'!A8)
2. Select the following sheets: ‘Summary’, ‘Details’, ‘Zone XY’, ‘Zone 12’, and ‘Zone 8B’
3. Select rows 5, 6, 7 to insert three rows below row 4
4. Copy row 4 and pasted into rows 5, 6, 7
5. Now Summary!A11 has a formula of SUM('Zone XY:Zone 8B'!A11)

That’s the logic, but I just can't piece together the right code that will execute it properly.  This is a tough one.  Thanks in advance for your help.

P.S. A really nice macro to select the specified worksheets is listed below (compliments of MatthewsPatrick)
    Dim MyList As String
    Dim arr As Variant
    Dim ws As Worksheet
    Application.ScreenUpdating = False
    MyList = "Summary,Details"
    With ThisWorkbook
        For Each ws In .Worksheets
            If LCase(ws.Name) Like "zone*" Then
                MyList = MyList & "," & ws.Name
            End If
        arr = Split(MyList, ",")
    End With

Open in new window

Question by:KP_SoCal
    LVL 22

    Expert Comment

    Just a few thoughts to start.
    If you want formulas to copy without changing references, you can change them to absolute references (put $ before the column and row references, like $C$12)  Then when you copy and paste, the references remain exactly the same.

    Recording a macro to insert a line is pretty simple.  Let me know if you need help with that.
    Next, adding a "popup" box to ask for how many rows is a tiny bit more challenging, but not that bad.

    If you want me to continue, I'll take a look at your attachment, but I'll also let MatthewPatrick continue with this if he sees it.

    Author Comment

    Hi rspahitz.  Thank you for replying.  I'm familiar with absolute and relative references.  The issue is I'm running into is that relative references are behaving like an absolute reference when rows are copied inserted.  

    The easiest way to understand this is open my attached file call "Example Code.xlsm".  And perform the following steps:

    Part A
    1. Select cell Summary!A8 and you'll see the following forumla displayed:  SUM('Zone XY:Zone 8B'!A8)
    2. Next select the following tabs by clicking on them: 'Summary', 'Details', and all 'Zone' tabs
    3. Then insert 5 rows between rows 2 and 3
    4. Now you'll notice the original row 8 was pushed down to row 13
    5. When you view the formula in cell A13, it will be correctly displayed as: SUM('Zone XY:Zone 8B'!A13)
    6. Close the file

    Part B
    1. Open file
    2. Select cell Summary!A4
    3. Press [Alt][F11] on your keyboard to bring up the IDE
    4. Run public sub process
    5. In the dialog box, key in the number 5
    6. Now if you go down to cell A13, you see the formula is incorrectly displayed as: SUM('Zone XY:Zone 8B'!A8)

    It should be A13 and not A8.  I hope this sheds some light on this issue.  Thanks for your help.

    LVL 17

    Accepted Solution

    This is a strange one.  The problem as you know is with the 3D formula reference on the summary sheet, which does not work well in VBA because VBA cannot deal with 3D ranges like this in the same way you can through the user interface.  Consequently, I have amended the code to get the original insert done by selecting all the sheets as a group first, then doing the insert.  This correctly shifts the reference in the summary sheet.  Then the original code loops through and fils the ranges as before - this bit I couldn't get to work on the grouped sheets. I think this now does what you want.

    Public Sub process()
    Dim MyList As String
    Dim arr As Variant
    Dim arrZones As Variant
    Dim ws As Worksheet
    Dim rng As String
    Dim start As Range
    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 & IIf(Len(MyList) = 0, "", ",") & ws.Name
            End If
        arr = Split(MyList, ",")
        ' first add the rows to the zones sheets
        sel = Selection.Row
        Set start = Selection
        ' select all required sheets as a group, then do the insert
        ActiveSheet.Rows(sel).Resize(rng, 1).EntireRow.Select
        '-- perform the insert on selected worksheets
        For Each ws In Worksheets(arr)
            '-- insert formulae
            ws.Cells(sel - 1, 1).Resize(rng + 1, 1).EntireRow.FillDown
        Next ws
    End Sub

    Open in new window

    LVL 17

    Expert Comment

    BTW this is one of the few times I believe you really have to Select things and use the selection object in VBA code.  I would normally avoid using Selection in VBA because it is slower and introduces all sorts of dependencies on what's visble, active, slected, etc. However in this case, because VBA does not 'do' 3d ranges, you have to select and fool Excel into thinking it's all coming through the user interface.

    Author Closing Comment

    Excellent!  I'm extremely dissappointed that I can't award you 1000 points for this!  Thanks again!!!

    Author Comment

    Also, I just noticed that I attached the wrong file.  So even more kudos to you for piecing this together without having the correct example file.  ;-)

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    Join & Write a Comment

    This article will show you how to use shortcut menus in the Access run-time environment.
    No matter the version of Windows you are using, you may have some problems with Windows Search running too slow or possibly not running at all. Before jumping into how you can solve this issue, just know there are many other viable alternative deskt…
    Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.
    This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now