[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


VBA to insert rows

Posted on 2011-10-31
Medium Priority
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
  • 3
  • 2
LVL 22

Expert Comment

ID: 37059960
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

ID: 37060041
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

andrewssd3 earned 2000 total points
ID: 37062985
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

Get quick recovery of individual SharePoint items

Free tool – Veeam Explorer for Microsoft SharePoint, enables fast, easy restores of SharePoint sites, documents, libraries and lists — all with no agents to manage and no additional licenses to buy.

LVL 17

Expert Comment

ID: 37063008
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

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

Author Comment

ID: 37063984
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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

New style of hardware planning for Microsoft Exchange server.
Currently, there is an issue with being able to copy values from an external application to a dropdown list in Project Web Access (PWA).  The standard copy and paste methods don't seem to work properly. Here is a way to accomplish this task to s…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

867 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