entering data in one cell but having it create a new column each time

[tuxedo76]

Hello,


I hope I can explain this well enough to get some help.


I'd like to enter a piece of data for "truck number" (e.g., M14853) in a cell on Sheet1. Then I'd like that same data (M14853) to populate another cell in Sheet2.


For example, I'd enter "M14853" in Sheet1!A1 and it'd come up automatically again as "M14853" in Sheet2!AB5. Easy, it seems.


But then five minutes later I'd like to enter "M14855" in Sheet1!A1 and have it come up again as "M14855" in Sheet2!AC5. (Same cell on Sheet1; different cell on Sheet2.)


And then tomorrow I'd like to enter "M88888" in Sheet1!A1 and have it come up again as "M88888" in Sheet2!AD5.


In other words, I'd like each time I enter data in one cell on Sheet1 to keep filling in "the next column" in Sheet2.


Thoughts or suggestions would be helpful on baby steps on how I can approach this.


Thanks!


Kelly Vielmo

 

[Luke M]

Right click on sheet1 tab, view code, paste this in:

Private Sub Worksheet_Change(ByVal Target As Range)

If Intersect(Target, Range("A1")) Is Nothing Then Exit Sub

If Target.Count > 1 Then Exit Sub

Dim LastCol As Integer

Application.ScreenUpdating = False

Application.EnableEvents = False


'Which row is data going to?

x = 5

'Which worksheet is data going to?

With Worksheets("Sheet2")

LastCol = .Cells(x, .Columns.Count).End(xlToLeft).Column

.Cells(x, LastCol + 1).Value = Target.Value

End With


Application.EnableEvents = True

Application.ScreenUpdating = True


End Sub

Note that this has no error handler in case you run out of columns. Not sure if that's a problem or not...