List Creation from Static tab

[gary]

i have created a sheet in Excel that will be used by sales reps to enter in a
few items which they can get from drop down boxes. the problem i am having
is, this will be the only sheet for them to use, and we are asking them to
'save as" and then when complete, to re-use the same sheet for the next phone
activity. i need to creat a second tab that will keep a running list of what
was entered into the static tab as soon as they save it. how do i do this?

 

[ryguy7272]

This is event code; you must right-click on Sheet1 (or whatever sheet you
use) and click View Code. Then paste the code below into the window that
opens:

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$1:$b$400")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("Sheet2")
.Select
.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Target.Address
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Target.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = InputBox("You've made a change to the Rates tab.
Please enter your name here for historical purposes.")
Application.EnableEvents = True
Application.ScreenUpdating = True
End With
End If
End Sub



Regards,
Ryan--
PS, this code is pretty awesome (not my own doing); found it on this DG a
while back.

 

[gary]

it didn't work. nothing came over from the first tab.

 

[ryguy7272]

Your initial post did not contain many details, so I can't tell what you are
doing. Try to change the range in the code. Now it is set for:
Range("$A$1:$b$400")

Change this to match your data.
Post back if it does not work when you retry.
If you do post back, give more info.

Regards,
Ryan--

 

[Gord Dibben]

Have you tried the Template Wizard with Data Tracking?

Writes each newly created workbook record to a data.xls workbook.


Gord Dibben MS Excel MVP

 

[RyGuy]

Maybe this will work better for you (it is slightly more automated than the
prior version):

Function MyUserName() As String
MyUserName = Environ("UserName")
End Function

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Application.Intersect(Target, Range("$A$1:$BB$4000")) Is Nothing Then
Application.EnableEvents = False
Application.ScreenUpdating = False
With Worksheets("Sheet2")
..Select
..Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Select
ActiveCell.Value = Target.Address
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Target.Value
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = Now()
ActiveCell.NumberFormat = "mm/dd/yy"
ActiveCell.Offset(0, 1).Select
ActiveCell.Value = MyUserName()
Application.EnableEvents = True
Application.ScreenUpdating = True
End With
End If
End Sub

Regards,
Ryan---