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

VBA to insert rows

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
        Next
    
        arr = Split(MyList, ",")
    
        .Worksheets(arr).Select
        .Worksheets("Summary").Activate
    End With

Open in new window



Sample-File.xlsx
0
KP_SoCal
Asked:
KP_SoCal
  • 3
  • 2
1 Solution
 
rspahitzCommented:
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.
0
 
KP_SoCalAuthor Commented:
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.









Example.xlsx
0
 
andrewssd3Commented:
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
    Next
    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
    ThisWorkbook.Worksheets(arr).Select
    ActiveSheet.Rows(sel).Resize(rng, 1).EntireRow.Select
    Selection.Insert
    
    ActiveSheet.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

0
The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

 
andrewssd3Commented:
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.
0
 
KP_SoCalAuthor Commented:
Excellent!  I'm extremely dissappointed that I can't award you 1000 points for this!  Thanks again!!!
0
 
KP_SoCalAuthor Commented:
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.  ;-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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