I'd like to enter data in a cell and have it duplicate the data in the first non-blank column on a different tab

[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

 

[pboltonchina]

Try this on a copy of your data as it's untested. Put it in the Sheet 1 of your workbook

Private Sub Worksheet_SelectionChange(ByVal Target As Range)

Dim LastColumn As Integer
If Target.Address = "$A$1" Then
Sheets("Sheet2").Activate
If WorksheetFunction.CountA(Cells) > 0 Then _
  LastColumn = Cells(5, Columns.Count).End(xlToLeft).Column
Cells(5, LastColumn + 1).Value = Sheets("Sheet1").Range("A1").Value
End Sub