Populate A List From One Cell On One Sheet

Hi,

the following code in the code section of sheet 1. All entries in cell A1 in sheet 1 are listed in sheet 2 cell A2 and the following cells:

Hey Case_Germany, thank you so much for the quick response. The code works when I put it into sheet1 of a new workbook. However, when i try and adjust it for use on a seperate workbook its not working. I need it to take the value from cell F20 on sheet2, and generate the populating list on sheet7. So I pasted this into the sheet2 code:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells(1).Address = "$F$20" Then
Sheet7.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Target.Value
End If
End Sub


For some reason it is not working. Is there something I am missing in this code?

Hi again,

"Sheet7" is the code name of the worksheet (the name before the brackets) - look in the VBA editor - Sheet7 (Sheet7). Where the name in the brackets is the name as it appears in the Excel sheet.

Here is a sample file:

A simple example...

Or is cell F20 changed via a formula?

Ok I got it to work thank you!

Now if i wanted to add to the code and do the same thing with a different cell posting to a list in column 2 of sheet7 how would I add that? So basically I now want cell F21 to populate a list in column 2 of sheet7 along with the original F20 populating column1 of sheet7.

Hi,

try:

Thanks again for the quick response. So from you last response I assumed the following code to add the next cell "F22" to a 3rd list on sheet 1:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells(1).Address = "$F$20" Then
Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Target.Value
ElseIf Target.Cells(1).Address = "$F$21" Then
Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Target.Value
ElseIf Target.Cells(1).Address = "$F$22" Then
Sheet1.Cells(Rows.Count, 3).End(x1Up).Offset(1, 0).Value = Target.Value
End If
End Sub


And it doesnt work. It says variable not defined and it highlights the x1Up on the 3rd if. Sorry to keep adding onto my previous questions, however I keep running into issues i think I can figure out but I can't.

Here's the code I tried
code:
Option Explicit
Private Sub Worksheet_Change(ByVal Target As Excel.Range)
If Target.Cells(1).Address = "$F$20" Then
Sheet1.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).Value = Target.Value
ElseIf Target.Cells(1).Address = "$F$21" Then
Sheet1.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Target.Value
ElseIf Target.Cells(1).Address = "$F$22" Then
Sheet1.Cells(Rows.Count, 3).End(x1Up).Offset(1, 0).Value = Target.Value
End If
End Sub

Hi,

it highlights the x1Up on the 3rd if

Click to expand...

not 1, but a small L (l)

Oops yup that was it, thanks. Now to add on to the previous questions, is it possible that when somebody plugs a number into the input cells , not only the input cell copies to sheet 1 but also a seperate cell value that is associated. For instance, if i were to plug a number into cell F21, that number copies to its sheet1 list and the date that is entered into a seperate cell (C5) automatically copies aswell to sheet1 in an adjacent list?

Again, thanks for all the help I really appreciate it