Insert new row with drop-down box but problem with cell linkreferring to the original drop-down box

[Phi|]

I have a sheet called "Action plan", that has a summary in the top and
rows below starting from row 11. It ranges from 11A to AN11. Through
these rows I have a number of various drop-down boxes, which pull data
from a sheet called "mapping".

Im trying to create a button that can insert a new row below the last
row (ie. 12) with all its different drop-down boxes. However, Im using
cell-link with the drop-down box, which I seem unable to copy as a
variable. In example I have a drop-down box in K11 with a cell-link in
L11. When I try to copy the whole row manually till row 12, it does
not seem to change the cell-link reference to K12, but will still be
linked to K11.

In my example I have the drop-down box pull data from column C and
column D on the "mapping" sheet. Where column C is the drop-down box,
and column D gives the cell-link value.

Im a newbie at macro's and have tried searching the web for a
solution, though I have not found one so far. I hope you will be able
to help me.

Thank you in advance for your time and help.
/Phil

 

[Phi|]

When I try recording a new macro this is what I get:

Sub Makro4()
'
' Makro4 Makro
'

'
ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(711.75, 488.25, 86.25, 21).Select
ActiveSheet.DropDowns.Add(664.5, 488.25, 34.5, 21).Select
ActiveSheet.DropDowns.Add(510, 488.25, 34.5, 21).Select
ActiveSheet.DropDowns.Add(569.25, 488.25, 86.25, 21).Select
ActiveSheet.DropDowns.Add(967.5, 487.5, 78, 19.5).Select
ActiveSheet.DropDowns.Add(453.75, 488.25, 34.5, 21).Select
ActiveSheet.DropDowns.Add(920.25, 488.25, 34.5, 21).Select
ActiveSheet.DropDowns.Add(1207.5, 487.5, 45.75, 19.5).Select
ActiveSheet.DropDowns.Add(1289.25, 487.5, 45.75, 19.5).Select
ActiveSheet.DropDowns.Add(1365, 487.5, 45.75, 19.5).Select
ActiveSheet.DropDowns.Add(1551, 487.5, 80.25, 19.5).Select
ActiveSheet.DropDowns.Add(1707, 487.5, 63, 19.5).Select
ActiveSheet.DropDowns.Add(2525.25, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault
Rows("19:20").Select
End Sub

and If I record a macro where I change the cell link I get:

Sub Makro5()
'
' Makro5 Makro
'

'
ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 484.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(711.75, 488.25, 86.25, 21).Select
ActiveSheet.DropDowns.Add(664.5, 488.25, 34.5, 21).Select
ActiveSheet.DropDowns.Add(510, 488.25, 34.5, 21).Select
ActiveSheet.DropDowns.Add(569.25, 488.25, 86.25, 21).Select
ActiveSheet.DropDowns.Add(967.5, 487.5, 78, 19.5).Select
ActiveSheet.DropDowns.Add(453.75, 488.25, 34.5, 21).Select
ActiveSheet.DropDowns.Add(920.25, 488.25, 34.5, 21).Select
ActiveSheet.DropDowns.Add(1207.5, 487.5, 45.75, 19.5).Select
ActiveSheet.DropDowns.Add(1289.25, 487.5, 45.75, 19.5).Select
ActiveSheet.DropDowns.Add(1365, 487.5, 45.75, 19.5).Select
ActiveSheet.DropDowns.Add(1551, 487.5, 80.25, 19.5).Select
ActiveSheet.DropDowns.Add(1707, 487.5, 63, 19.5).Select
ActiveSheet.DropDowns.Add(2525.25, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
ActiveSheet.DropDowns.Add(1990.5, 487.5, 66, 19.5).Select
Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault
Rows("19:20").Select
ActiveSheet.Shapes("Drop Down 1765").Select
With Selection
.ListFillRange = "mapping!$C$1:$C$53"
.LinkedCell = "L20"
.DropDownLines = 5
.Display3DShading = True
End With
End Sub

The problem is that this wont work. Debug says that this:
"Selection.AutoFill Destination:=Rows("19:20"), Type:=xlFillDefault",
is a problem.
Also I would like to button to create a new row at the bottom, and not
as now copy row 19 and insert it to row 20.

Hope this makes more sense.

Thank you in advance for your help.

/Phil

 

[Dave Peterson]

This may get you closer:

Option Explicit
Sub testme()

Dim myDD As DropDown
Dim NextRow As Long
Dim wks As Worksheet
Dim wksList As Worksheet
Dim ListRng As Range

Set wks = Worksheets("Action plan")
Set wksList = Worksheets("Mapping")

With wksList
Set ListRng = .Range("C1:c53")
End With

With wks
'uses column A to determine the last row
NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1

.Rows(NextRow - 1).Copy
.Rows(NextRow).PasteSpecial Paste:=xlPasteFormulas

With .Cells(NextRow, "K")
Set myDD = .Parent.DropDowns.Add(Top:=.Top, _
Left:=.Left, Width:=.Width, Height:=.Height)
End With

With myDD
.ListFillRange = ListRng.Address(external:=True)
.LinkedCell = .Parent.Cells(NextRow, "L").Address(external:=True)
.DropDownLines = 5
.Display3DShading = True
End With
End With

End Sub