Hello, I am working on a model and have VBA code:
For example, if "Labor BOE 1" worksheet has a "3" in cell L2, then it will copy/paste the range ("A21:L30") from the template to this worksheet 3 times
2. Then, I am searching column A, and every time it finds a number, it will insert that many rows below and copy the formulas down in columns "C:L"
But... The array formula used in column D is extracting a unique list from the "Staffing Plan" worksheet and so it contains a mix of absolute/relative cell references. The reference that is giving me issues is the "$D$26:$D26". When the range is copy/pasted a second time on one sheet, it is stuck at "$D$26:$D36", but I need it to be "$D$36:$D36". When it is posted a third time on one sheet, it is stuck at "$D$26:$D46", but I need it to be "$D$46:$D46".
{=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D26, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"") }
Is it possible to add code to the first macro ("Insert Tasks") to unlock the cell reference "$D$26:$D26" to "D26:$D26", copy/paste it, and then switch back to "$D$26:$D26" ? Or do you have any other thoughts on how to get around this issue?
- Copy/paste a range ("A21:L30") from a template worksheet to multiple workbooks that begin with "Labor BOE", multiple times based on a number in cell "L2"
For example, if "Labor BOE 1" worksheet has a "3" in cell L2, then it will copy/paste the range ("A21:L30") from the template to this worksheet 3 times
Code:
Sub InsertTasks()
Application.ScreenUpdating = False
Dim num As Long
Dim i As Long
Dim b As Long
Dim Lastrow As Long
For i = 1 To Sheets.Count
If Left(Sheets(i).Name, 9) = "Labor BOE" Then
Lastrow = Sheets(i).Cells(Rows.Count, "L").End(xlUp).Row + 1
num = Sheets(i).Range("L2").Value
For b = 1 To num
Lastrow = Sheets(i).Cells(Rows.Count, "L").End(xlUp).Row + 1
Sheets("Template - Tasks").Range("A21:L30").Copy Destination:=Sheets(i).Range("A" & Lastrow)
Next
End If
Next
Application.ScreenUpdating = True
End Sub2. Then, I am searching column A, and every time it finds a number, it will insert that many rows below and copy the formulas down in columns "C:L"
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
sh.Range("C" & n & ":L" & n).Copy Destination:=sh.Range("C" & n + 1 & ":L" & n + Ins)
End If
Next n
End If
Next sh
MsgBox "BOE Generation Complete"
End SubBut... The array formula used in column D is extracting a unique list from the "Staffing Plan" worksheet and so it contains a mix of absolute/relative cell references. The reference that is giving me issues is the "$D$26:$D26". When the range is copy/pasted a second time on one sheet, it is stuck at "$D$26:$D36", but I need it to be "$D$36:$D36". When it is posted a third time on one sheet, it is stuck at "$D$26:$D46", but I need it to be "$D$46:$D46".
{=IFERROR(INDEX('Staffing Plan'!$J$8:$J$1002, MATCH(0, IF($L26='Staffing Plan'!$W$8:$W$1002, COUNTIF($D$26:$D26, 'Staffing Plan'!$J$8:$J$1002), ""), 0)),"") }
Is it possible to add code to the first macro ("Insert Tasks") to unlock the cell reference "$D$26:$D26" to "D26:$D26", copy/paste it, and then switch back to "$D$26:$D26" ? Or do you have any other thoughts on how to get around this issue?
Last edited:
