dijohnguitar
New Member
- Joined
- Jan 13, 2021
- Messages
- 3
- Office Version
- 365
- Platform
- Windows
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:
And the MACRO I have set up:
- 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:
VBA Code:
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:
VBA Code:
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
Last edited by a moderator: