Autopopulate a column based on a cell value

[levans_1986]

Hello

I am creating a survey in an excel workbook where the first tab contains a unique number for each row (survey number) and then each column has a question with a yes/no answer. There is a second tab which only needs to be completed if for example the answer to the question in column P on sheet 1 is yes.

I want the second excel sheet to be able to autopopulate the survey number if the answer to the question is yes in column P of sheet 1. This means that it is easier for the person who is completing the survey. However I'm not sure how to do this

any help appreciated

thanks

laura

 

[hatstand]

Hi,

Put this code in the sheet with the questions. I guessing that you just want the unique number to be copied into cell A1. Hope it helps.


Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
Dim Rn As Long
Set KeyCells = Range("P1:P10")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing Then
Rn = Target.Row
Worksheets("Sheet2").Range("A1").Value = Cells(Rn, 1).Value
End If
End Sub

 

[levans_1986]

Thanks hatstand.

I have tried that, presumably there is nothing within this code I need to update as long as the range is correct and the worksheet name? However I have copied this into the code for the worksheet 1 and it is not working.

Just to be clear I do want the unique number. If the answer to the question is no I want to bring back the first instance where the answer is yes and then any subsequent yes's. does that make sense?

thanks for your help I'll keep playing but not sure why its not working

thanks
laura

 

[hatstand]

Hi,

I have amended the code to pick up the answer. I'm a little lost as to what you want to happen after a 'no' is selected. You want to copy any '"yes" selections on the same line then copy into Sheet2?

Private Sub Worksheet_Change(ByVal Target As Range)

Dim KeyCells As Range
Dim Rn As Long
Set KeyCells = Range("P1:P10")

If Not Application.Intersect(KeyCells, Range(Target.Address)) Is Nothing And Range(Target.Address).Value = "Yes" Then

Rn = Target.Row
Worksheets("Sheet2").Range("A1").Value = Cells(Rn, 1).Value
End If
End Sub

 

[levans_1986]

Thanks hatstand and apologies if I am unclear.

I have tried your code but it is still not working.

For example
In worksheet 1 I have
Survey Number, Answer
S1, No,
S2, No
S3 Yes
S4 Yes

In this example on sheet 2 I want cell A2 to be S3 and cell A3 to be S4.

I have put your code into the worsheet code for sheet 1 but its still not working

All help appreciated

thanks

laura

 

[hatstand]

Hi,

Sorry I thought you wanted the copy to take place when a 'Yes' is chosen in column P. What you are after is to basically filter column 'P' for 'Yes' then copy the results to sheet2? Am I on the right track?

 

[levans_1986]

Hi hatstand

sorry yes I do want the copy to take place when a yes is chosen in column P. However in the example above if we had populated S1,S2, S3 and S4, when we go to worksheet 2 we will see S3 and S4. If we had only populated as far as S3 then we would only see S3 on the worksheet 2. Does that make sense?

thanks for your patience!

 

[hatstand]

Hi,

PS. I have been away from a PC for a few days.

No problem with patience! If your new to stuff like this (I'm no expert and have learned so much from people on here). The following will copy any 'Yes' rows on Sheet 1 into Sheet 2 in into the first blank row. I've added some helper text so you can make any changes easily. Give it a go and see if it's what you need.

Sub CopyIt()
Application.ScreenUpdating = False 'Turn off screen updating

Dim WsFirst As Worksheet, WsSecond As Worksheet 'Worksheet name variables
Dim LRow As Long 'Row variable

Set WsFirst = Worksheets("Sheet1") 'Set Worksheet 1 variable name
Set WsSecond = Worksheets("Sheet2") 'Set Worksheet 2 variable name

LRow = WsSecond.Range("A" & Rows.Count).End(xlUp).Row + 1 'Returns the first blank row on Sheet 2

With WsFirst 'Apply alll that follows to Sheet1
.AutoFilterMode = False 'Trn off any filters
.Range("A1:P1").AutoFilter 'Apply a filter
.Range("A1:P1").Range("$A$1:$P$1000").AutoFilter Field:=16, Criteria1:="Yes" 'Apply Yes to the filter in Column 'P'
.AutoFilter.Range.Offset(1, 0).Copy Destination:=Worksheets("sheet2").Range("A" & LRow) 'Copy the filtered range to the first blank row in Sheet 2
.AutoFilterMode = False 'Turn filter off
End With

End Sub

 

[levans_1986]

Thanks for responding. This looks like just what I need however perhaps a silly question but where should I paste this? I haev tried to paste it in the code for the sheet and also the code for the workbook but I cant get it to trigger. I'm not getting an error message its just not doing anything!

Thanks again

 

[hatstand]

I forgot to say. Paste it into a standard Module and if you need to, add a button to your Sheet1. Use a Form Button and when asked select teh name of the Macro. If you get stuck just post back.