This routine will do the data entry for you and capture the results. To put
the code into your workbook, open it and press [Alt]+[F11] to open the VB
Editor. Use Insert --> Module in it to open a new code module, then copy and
paste the code into it. Select the proper worksheet and then run the Macro.
Sub FillAndCapture()
Dim homeCell As Range
Dim baseCell As Range
Dim LC As Integer
Dim MLC As Integer
Set homeCell = Range("H8")
For LC = 10 To 150
homeCell = LC
Set baseCell = Range("AF" & Rows.Count). _
End(xlUp).Offset(1, 0)
For MLC = 0 To 3
baseCell.Offset(0, MLC) = _
homeCell.Offset(0, MLC)
Next
Next
Set baseCell = Nothing
Set homeCell = Nothing
End Sub
Now, if you want to simply capture what you type into H8 when it changes,
then use the following worksheet code (don't use the code above). To put
this to use, open the workbook, select the appropriate sheet and then
right-click on its name tab and choose [View Code] from the list and copy the
code and paste it into the code module. Any time you type an entry into H8,
it's contents and those of I8, J8 and K8 will be captured into a new row at
AF, AG, AH and AI.
Private Sub Worksheet_Change(ByVal Target As Range)
Dim baseCell As Range
Dim LC As Integer
If Target.Address <> "$H$8" Then
Exit Sub
End If
'change was made in H8
'prevent reentry while we're
'doing the work
Application.EnableEvents = False
'set up a reference to the next
'available cell in column AF on the sheet
Set baseCell = Range("AF" & Rows.Count). _
End(xlUp).Offset(1, 0)
For LC = 0 To 3
baseCell.Offset(0, LC) = _
Target.Offset(0, LC)
Next
Set baseCell = Nothing
Application.EnableEvents = True
End Sub
