VBA Code To Transfer Numerical Value To Another Group Of Cells On Another Sheet

[data808]

This one is a bit complicated but I hope someone knows how to do the VBA to get this done.

Example
Sheet1 - I want the user to type a numeric value into cell A1. Lets say its 12345678 for example.

Sheet2 - I then want this numeric value (12345678) to be broken down by each number and entered into 8 different cells. One cell for each number. So for example, the first number 1 will be transferred to cell A5. Number 2 will go into B5. Number 3 will go into C5 and so forth...

Let me know if this is possible. Any help is appreciated. Thank you.

 

[Joe4]

No VBA is necessary. This can be done with a simple formula.

Put this formula in cell A5 on Sheet 2, and then drag across the row to all the other columns:

=MID(Sheet1!$A$1,COLUMN(),1)

 

[BigBeachBananas]

VBA option:

Sub BreakDownNumber()
    Dim sourceCell As Range
    Dim targetCell As Range
    Dim i As Integer
    Dim lastRowSource As Long
    Dim lastRowTarget As Long

    lastRowSource = Sheets("Sheet1").Cells(Sheets("Sheet1").Rows.Count, "A").End(xlUp).Row
    Set sourceCell = Sheets("Sheet1").Cells(lastRowSource, "A")
    lastRowTarget = Sheets("Sheet2").Cells(Sheets("Sheet2").Rows.Count, "A").End(xlUp).Row
    Set targetCell = Sheets("Sheet2").Cells(lastRowTarget + 1, "A")
    
    For i = 1 To Len(sourceCell.Value)
        targetCell.Offset(0, i - 1).Value = Mid(sourceCell.Value, i, 1)
    Next i
End Sub

 

[bebo021999]

This code is in sheet1's module, trigger any change in cell A1

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim i&
If Target.Address(0, 0) = "A1" Then
    For i = 1 To Len(Target)
        Sheets("sheet2").Cells(5, i).Value = Mid(Target, i, 1)
    Next
End If
End Sub