Populate A List From One Cell On One Sheet

[Jmwend]

Hello, need a little help please.

I would like to compile a list of numbers I have typed into a cell on one page and list it on another page without writing over the number but putting it in a different cell.

For example: I type into the cell A1 sheet 1, 3000 then in cell A1 on sheet 2, 3000. I then clear A1 sheet 1 and type in 3040 then in cell A2 sheet 2, 3040, etc.

Making a list of numbers I have used so they I can keep track of them, and go back to see that I have used them.

Hopefully someone can help me!

 

[Case_Germany]

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:

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

 

[Jmwend]

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?

 

[Case_Germany]

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?

 

[Jmwend]

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.

 

[Case_Germany]

Hi,

try:

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
    ElseIf Target.Cells(1).Address = "$F$21" Then
        Sheet7.Cells(Rows.Count, 2).End(xlUp).Offset(1, 0).Value = Target.Value
    End If
End Sub

 

[Jmwend]

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.

 

[Jmwend]

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

 

[Case_Germany]

Hi,

it highlights the x1Up on the 3rd if

not 1, but a small L (l)

 

[Jmwend]

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