Auto populate cell value from Sheet 1 to Sheet 2 based on cell condition in sheet 1

[PAPPU]

Please help : in this sheet1, the highlighted cells are with alpha numeric pattern, as it required to be in that order.

On the next tab, in sheet 2 , i have one table shows the name, date and numeric value that needed to be filled in from sheet1.

I want whenever i enter any of this alpha-numeric value in any of the cells in sheet1, it should auto populate in sheet2 against with respective name ,date and the numeric value only. That is, instead of 1-16 with n or m , i need 1-16 as numeric only .I hope ,it reaches. I am using Microsoft 365 MSO -64bit version. Thanks

 

[RobVos]

Not sure if it is the best way to accomplish what you want, but this seems to work for me.
I used I1 and J1 for helpers (you can hide those columns if you want).

In J1

=SUBSTITUTE(I1,RIGHT(I1,LEN(I1)-MAX(IFERROR(FIND({0,1,2,3,4,5,6,7,8,9},I1),""))),"")

The in VBA for Sheet1 Change Event:

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("B2:H5")) Is Nothing Then
  Dim myVal As String, myNumber, lastRow As Long
  myVal = Target.Value
  Range("I1") = myVal
  If IsNumeric(Range("J1").Value) = True Then
    myNumber = Range("J1").Value
      With Sheet2
        lastRow = .Cells(Rows.Count, 2).End(xlUp).Row + 1
       .Range("A" & lastRow).Value = Range("A" & Target.Row).Value
       .Range("B" & lastRow).Value = Cells(1, Target.Column).Value
       .Range("C" & lastRow).Value = myNumber
     End With
  End If
End If

End Sub

Also, for conditional formatting on Sheet 1:

=ISNUMBER(LEFT(B2)*1)=TRUE

Applies to:

=$B$2:$H$5

 

[PAPPU]

Hi Sir Robvos, Thanks for the quick reply.?
However I end up with error. In code, as i bit doubt due to my awareness in this field. If you don't mind , can it be shown on bit more elaborative?
Will be helpful if you could post it as per the cell name posted above in image. Please consider the word shown above in both sheet is at A1 cell.

Excuse me please if i wrote it incorrectly, even though you did it perfect.

 

[PAPPU]

Sorry typo error: Please consider the word NAME shown above in both sheet is at A1 cell

 

[RobVos]

Here I replicated your Sheet 1. The formula is in J1 - I used Excel2016 so after typing the formula, I hit CTRL-SHIFT-ENTER - then { } appear around it (don't type those).

Press ALT-F11 to open the VBA editor. Select Sheet1 and put the code in the Worksheet Change (highlighted below).
Now whenever you change a value in Sheet1 B2:H5, if it starts with a number, it will put the Name, Date, and Number on Sheet 2.

I changed some of the data in Sheet 1 - This is what went to Sheet 2 as I changed things.

 

[PAPPU]

Thank You very much Robvos, Perfectly working.
As it was shown as sample notification the number of referenced cell was few only.
My actual cell range is bit wide, shown below please:

Sheet 1 actual name: 2022
In Sheet 1 (2022) Apple is on B22 and it continues up to B64. So it ranges from B22:B64 in tab 2022.
In Sheet 1 above image like the range B2:H5 , the actual representation in tab 2022 is G22:KZ64

Sheet 2 actual name: TO
So in TO tab : Name, Date and Numeric value start from B12, E12 and K12 and continues up to B1373, E1373 and K1373 respectively.

In this case should i change any values or ranges to be modified please?

Again thanks for your kind help

 

[RobVos]

The code needs to go in the sheet with your data (2022) - it will be listed in the VBA editor as Sheet# (2022), where # could be anything, but you will see it.
In the Code Change With Sheet2 to With Sheets("TO")
Wherever you put the two helper cells is not important, but you would need to change any references to those cells both in the code and in the formula.
For example, if you put helper cells in AA1 and AB1, in the code, change I1 to AA1 everywhere and J1 to AB1 everywhere, and in the formula, change all instances of I1 to AA1.
In the code, change the range in the If Not Intersect line to your entire range - B2:H5 to G22:KZ64
In the code change the three lines where I have A B and C to the columns you want the data to go into (B, E, and K).
The code always finds the last row used on the sheet (looks like I had it column 2) - so should be ok, unless you have data below elsewhere on the sheet in column B.

 

[PAPPU]

Awesome Robvos?.Thanks a lot. You made my day fulfilled ??.

And also i could able to learn and understand few things after reading through your instructions. Much appreciated. I will try to pick it up slowly. Wish you a blessed day ahead.

 

[RobVos]

Glad it works for you.

 

[PAPPU]

Hi Robvos: One more time Please help

I have already one code is there in Sheet1(2022). If i insert one module in 2022 sheet, the heading in it is General only , it doesn't change after.

I insert another module and insert below code: I add the image for you to better catch, below

HERE 2 THINGS I FORGET TO ASK YOU:

My dates in sheet 1 is from 2nd raw and starts from G2:ME2.
So is the code correct: .Range("E" & lastRow).Value = Cells(2, Target.Column).Value
I have 1J and 1I cells free, so insert the formula there only, so change

After doing it all , it still not working

Thanks