VBA for Automated DropDown Functions

[dijohnguitar]

I have a spreadsheet that has Columns A-L. Dropdown lists are located in Columns F-K. All of my DropDowns have a default value of "(Select Value)" which obviously instructs users to pick a value from the dropdown. I'm trying to see if it's possible to do the following:
- In Column K, this dropdown selection will insert a new blank row under the current row, and remove all contents and validation EXCEPT in Column K. However, if they select "None" or if the Dropdown stays with "(Select Value)" no new row will be created.
- The Dropdown from Column K needs to copy down to the new row into Column K and be set with the "(Select Value)" title
- I need this to repeat for every row of data on the sheet

The information for the data validation in Column K comes from a separate sheet entitled "SOFTWARE" which has a substantial amount of choices (A1:A182). I'd certainly appreciate any assistance on this. I've set up a Macro to run exactly what I'm asking above, but it's only doing it for 1 row and not copying down for all the rows. I'd really appreciate the Excel Gurus with helping me tackle this one. Thank you!!

Here's the VBA that I currently have:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("K3")) Is Nothing Then
        Select Case Range("K3")
            Case Is <> "(select value)": Macro1
           
        End Select
    End If
    End Sub

And the MACRO I have set up:

Sub Macro1()
'
' Macro1 Macro
'
'
Rows("4:4").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
Range("K3").Select
Selection.AutoFill Destination:=Range("K3:K4"), Type:=xlFillDefault
Range("K3:K4").Select
Range("A4:J4").Select
Selection.ClearContents
Selection.Validation.Delete
Range("K4").Select
Range("K4") = "(select value)"

End Sub

 

[fadee2]

hi,
If I understand it correctly, try the following and see if it works.

Sub Macro1()
'
' Macro1 Macro
'
lastrow = Cells(Rows.Count, 1).End(xlUp).Row

Rows(lastrow).Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
'Range("K3").Select             <<<<<<< not required
Range("k3").AutoFill Destination:=Range("K3:K" & lastrow), Type:=xlFillDefault
'Range("K3:K4").Select          <<<<<<< not required
Range("A" & lastrow, "J" & lastrow).ClearContents
Range("A" & lastrow, "J" & lastrow).Validation.Delete
'Selection.Validation.Delete    <<<<<<< not required
'Range("K" & lastrow).Select    <<<<<<< not required
Range("K" & lastrow) = "(select value)"

End Sub

hth...

 

[dijohnguitar]

Hello Fadee2,

The code you provided did not work. What it ended up doing was this: as soon as I select an option from the dropdown in Column K, it copied and pasted the dropdown from the first row, to the 2nd-to-last row. It then inserted a blank row after that with the dropdown menu. The new dropdown on the inserted row did not insert any new rows after it once a selection was made. I really appreciate you helping me with this.

 

[fadee2]

Well I only updated your code so that it would work for every row.
anyway, can you share your part of your worksheet in question?