I need help; I'm helping the taxman.
Right click on the tab of the sheet you want this to work on, and choose
View code, then copy and paste this code where the cursor is flashing:
Code:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count = 1 Then
If Not Intersect(Target, Range("L:L")) Is Nothing Then
x = [{2017,2017;2016,2016;2015,2015;2014,2014;2013,2013;2012,2012;2006,2011;1994,2005;1987,1993;1980,1986;"",1979}]
Z = Application.Match(Target.Value, Application.Index(x, 0, 2), 0)
If Not IsError(Z) Then
Z = Z - 1
If Z >= 8 Then '<<<adjust the 8 to the minimum number of rows to be inserted
Application.EnableEvents = False
Target.Resize(Z).EntireRow.Insert
Target.Offset(-Z, -1).Resize(Z, 2).Value = x
Application.EnableEvents = True
Target.Offset(1).Select
End If
End If
End If
End If
End Sub
Go back to the sheet and enter 1979, 1986 or 1993 somewhere in column L.Currently, it works only for those three years, but this can easily be adjusted to other numbers by changing the line:
If Z >= 8 Then '<<<adjust the 8 to the minimum number of rows to be inserted
07-15-2018, 11:24 AM
Similar Threads
Would like a macro to copy a cell down a variable number of rows
Windows 10 
Office 2016
Hybrid Mode