Hello everyone,
What I'm trying to achieve is take data from SHEET1 and one column "A15" on the same line as the drop down box "J15" and if the criteria is met (yes/no selection) then the data from "A15" is pasted on another sheet "Impacts" on column "D" but first the macro has to check columns A through G to make sure no data is pasted there already, and that it doesn't duplicate the data that has been previously entered, this is all within the same workbook. I have multiple lines and multiple drop down boxes and can't seem to get the code to go row by row, checking the drop down boxes. Also if they select "No" I'm not quite sure how to make the code skip to the next line without pasting the data from column A.
Here is my current code:
[pre]
[/pre]
Thanks for your help in advance.
What I'm trying to achieve is take data from SHEET1 and one column "A15" on the same line as the drop down box "J15" and if the criteria is met (yes/no selection) then the data from "A15" is pasted on another sheet "Impacts" on column "D" but first the macro has to check columns A through G to make sure no data is pasted there already, and that it doesn't duplicate the data that has been previously entered, this is all within the same workbook. I have multiple lines and multiple drop down boxes and can't seem to get the code to go row by row, checking the drop down boxes. Also if they select "No" I'm not quite sure how to make the code skip to the next line without pasting the data from column A.
Here is my current code:
[pre]
Code:
Sub Test()
Dim i As Integer
For i = 15 To Sheets("SHEET1").Range("J" & Rows.Count).End(xlUp).Row
Select Case Sheets("SHEET1").Range("J" & i)
'If Yes is selected in drop down box
Case "Yes"
Dim LastRow As Integer
LastRow = Worksheets("Impacts").Range("A5:G" & Rows.Count).End(xlUp).Row + 1
Dim order As String
order = Sheets("SHEET1").Range("A" & i)
Sheets("Impacts").Range("D" & LastRow) = order
'If No is selected in drop down box
Case "No"
'If nothing is selected in drop down box
Case ""
'Message displayed for empty box
MsgBox ("Not all boxes have been selected, please make sure to select Yes or No.")
Exit For
End Select
Next i
MsgBox ("All Impacts have been transfered")
End Sub
Thanks for your help in advance.