Insert Rows and Drag Formulas Down on Multiple Sheets - VBA

jwb1012

Board Regular
Joined
Oct 17, 2016
Messages
167
Hello, I am attempting to get my code to loop through all worksheets that begin with "Labor BOE", find all numbers in column A (beginning in row 2), insert that many rows below the cell with the number, and then drag the formulas down.

For example, if cell A10 = 3, then I need 3 rows to be inserted below. But, after these rows are inserted, I need columns C-J to be dragged down.

I have everything working, except I am unable to get the formulas to drag down. Any thoughts?


Code:
Sub InsertRows()
Dim End_Row As Long, n As Long, Ins As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
    If Left(sh.Name, 9) = "Labor BOE" Then
        
        End_Row = sh.Range("L" & Rows.Count).End(xlUp).Row
        
        Application.ScreenUpdating = False
        For n = End_Row To 3 Step -1
            Ins = sh.Cells(n, "A").Value
            
            If Ins > 0 Then sh.Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
        Next n
        
    End If
Next sh
End Sub
 

baitmaster

Well-known Member
Joined
Mar 12, 2009
Messages
2,031
You could try this. I don't like it as it involves copy & paste which will probably fail unless you start activating worksheets etc., but see how you get on
Code:
Sub InsertRows()
Dim End_Row As Long, n As Long, Ins As Long
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Sheets
    If Left(sh.Name, 9) = "Labor BOE" Then
        
        End_Row = sh.Range("L" & Rows.Count).End(xlUp).Row
        
        Application.ScreenUpdating = False
        For n = End_Row To 3 Step -1
            Ins = sh.Cells(n, "A").Value
            
            If Ins > 0 Then
                sh.Range("A" & n + 1 & ":A" & n + Ins).EntireRow.Insert
[COLOR=#FF0000]                sh.range("C" & n & ":J" & n).copy destination:= sh.range("C" & n+1 & ":J" & n+ins)
            end if[/COLOR]
        Next n
        
    End If
Next sh
End Sub
 

Forum statistics

Threads
1,082,287
Messages
5,364,299
Members
400,787
Latest member
bs04c

Some videos you may like

This Week's Hot Topics

  • populate from drop list with multiple tables
    Hi All, i have a drop list that displays data, what i want is when i select one of those from the list to populate text from different tables on...
  • Find list of words from sheet2 in sheet1 before a comma and extract text vba
    Hi Friends, Trying to find the solution on my task. But did not find suitable one to the need. Here is my query and sample file with details...
  • Dynamic Formula entry - VBA code sought
    Hello, really hope one of you experts can help with this - i've spent hours on this and getting no-where. .I have a set of data (more rows than...
  • Listbox Header
    Have a named range called "AccidentsHeader" Within my code I have: [CODE]Private Sub CommandButton1_Click() ListBox1.RowSource =...
  • Complex Heat Map using conditional formatting
    Good day excel world. I have a concern. Below link have a list of countries that carries each country unique data. [URL...
  • Conditional formatting
    Hi good morning, hope you can help me please, I have cells P4:P54 and if this cell is equal to 1 then i want row O to say "Fully Utilised" and to...
Top