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

[Jay3253]

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.

Thanks for your help
Jason

[Dave Peterson]

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



Jay3253 wrote:
>
> 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.
>
> Thanks for your help
> Jason
>
> --
> Jay3253
> ------------------------------------------------------------------------
> Jay3253's Profile: http://www.excelforum.com/member.php...fo&userid=5955
> View this thread: http://www.excelforum.com/showthread...hreadid=465945

--

Dave Peterson

[Jay3253]

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

Thanks again.
Jason

[Dave Peterson]

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)



Jay3253 wrote:
>
> Thank you, but as I know little about VB and writing code I don't
> understand what most of that means. Could you tell me what this thing
> does and how it works and it I need to change names cells or numbers.
>
> Thanks again.
> Jason
>
> --
> Jay3253
> ------------------------------------------------------------------------
> Jay3253's Profile: http://www.excelforum.com/member.php...fo&userid=5955
> View this thread: http://www.excelforum.com/showthread...hreadid=465945

--

Dave Peterson

[Jay3253]

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

[Dave Peterson]

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).



Jay3253 wrote:
>
> 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
>
> --
> Jay3253
> ------------------------------------------------------------------------
> Jay3253's Profile: http://www.excelforum.com/member.php...fo&userid=5955
> View this thread: http://www.excelforum.com/showthread...hreadid=465945

--

Dave Peterson

[Jay3253]

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

[Dave Peterson]

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.



Jay3253 wrote:
>
> 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
>
> --
> Jay3253
> ------------------------------------------------------------------------
> Jay3253's Profile: http://www.excelforum.com/member.php...fo&userid=5955
> View this thread: http://www.excelforum.com/showthread...hreadid=465945

--

Dave Peterson

[Jay3253]

It works thank you very much

Jason