Populate a list from one cell on one sheet...

  • Thread starter Thread starter Jay3253
  • Start date Start date
J

Jay3253

Hello, need a little help please.

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

For example: I type into the cell A1 sheet 1, 3000 then in cell A1 o
sheet 2, 3000. I then clear A1 sheet 1 and type in 3040 then in cel
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.

Thanks for your help
Jaso
 
You can do it using an event macro.

Rightclick on the worksheet tab that should have this behavior. Select view
code. Paste this into the code window.

Then back to excel to test it out:

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim DestCell As Range

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub
If IsEmpty(.Value) Then Exit Sub
If IsError(.Value) Then Exit Sub

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

DestCell.Value = .Value

End With

End Sub
 
Thank you, but as I know little about VB and writing code I don'
understand what most of that means. Could you tell me what this thin
does and how it works and it I need to change names cells or numbers.

Thanks again.
Jaso
 
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)

Dim DestCell As Range

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub
If IsEmpty(.Value) Then Exit Sub
If IsError(.Value) Then Exit Sub

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

DestCell.Value = .Value

End With

End Sub

The first thing is that since this goes behind the worksheet itself, it runs
each time you make a change to that worksheet.

The first thing it does is to check to see how many cells you changed. If it's
more than one, it just quits.
If .Cells.Count > 1 Then Exit Sub

If you didn't make the single change in A1, it quits.
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub

If you just cleared the cell, it quits:
If IsEmpty(.Value) Then Exit Sub

If you typed an error in the cell, it quits:
If IsError(.Value) Then Exit Sub

If the code is still running, it finds the next available cell in sheet2 (bottom
of column A).

With Worksheets("sheet2")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

Then it just puts the same value into that cell:
DestCell.Value = .Value

======
So if you're checking a different cell than A1, change this line:
If Intersect(.Cells, Me.Range("a1")) Is Nothing Then Exit Sub

If you're using a different named sheet (not sheet2), change this line:
With Worksheets("sheet2")

If you don't want to use column A of that other worksheet, change this line:
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
 
First off thank you, second it isn't working. I made the neccesary
changes to the code but it does nothing. Also, I make about 3 changes
on the sheet each time but I only need the information in one of the
boxes to be put in my list. I don't know if that will an effect. Can
I record a macro that will do this? If so, how?

Thanks
Jason
 
You could record a macro, but I don't think it would add to the existing code.

First, are you sure that you put the code behind the worksheet (rightclick on
the worksheet tab that gets your input, select view code and paste into the code
window).

Second, do you have macros enabled?
Tools|macro|security|security level tab|medium
And close and reopen the workbook.

Third, if these don't help, post the code you tried and include what you wanted
to do (just a short description will do).
 
This is how the code reads:

Private Sub Worksheet_Change(ByVal Target As Range)

Dim DestCell As Range

With Target
If .Cells.Count > 1 Then Exit Sub
If Intersect(.Cells, Me.Range("b2")) Is Nothing Then Exit Sub
If IsEmpty(.Value) Then Exit Sub
If IsError(.Value) Then Exit Sub

With Worksheets("sheet5")
Set DestCell = .Cells(.Rows.Count, "A").End(xlUp).Offset(1, 0)
End With

DestCell.Value = .Value

End With

End Sub

The cell I am typing into, is Sheet1 (I changed the name to "UL
listing") "B2". I would like the data to make a list down the "A"
column on sheet5 (I changed the name to "Finished Instruction")
starting with "A2".

If I type into B2 on the first sheet, I would like it to compile a list
on the fifth sheet of the numbers I have typed into B2. Like I said I
don't know much if anything about VB and would like to learn. I am
going about this the hard way but I will get there in the end.

Thanks
Jason

Edit: I put this code under tab of sheet5
 
I think that the only line you need to change is this:
With Worksheets("sheet5")
to
With Worksheets("Finished Instruction")

But do make sure that the code is behide the "UL listing" worksheet and you have
macros enabled.
 
Back
Top