vlookup in a VBA code?

[tony0710]

Hi

I've got the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

    With Target
        If .Column = 1 Then
            .Offset(, 1) = Now
            .Offset(, 2) = "1"
    
        Dim Cell As Range
        For Each Cell In Target
            If Cell.Column = 1 Then
                If Application.CountIf(Columns(7), Cell) = 0 Then
                    Cells(Cells.Rows.Count, 7).End(xlUp).Offset(1, 0) = Cell
                    Cells(Cells.Rows.Count, 7).End(xlUp).Offset(0, 1).FormulaR1C1 = "=SUMIF(C[-7],RC[-1],C[-5])"
                End If
            End If
        Next Cell
        End If
     End With
End Sub

Plus the follwing vlookup function:

=VLOOKUP(G2,'REF PAGE'!$A$1:$L$997,2,FALSE) 

What this does is to:

1. enter barcode into col a
2. Automatically enters todays date in col b
3. pops up inpt box and asks for quantity, which is then put into col c
4. barcode is automatically entered in col G
5. total of the quatities for the barcode are automatically calculated in col H
6. Col I: looks up barcode from col G, from another worksheet called "ref page", and inserts the value from the second column (i.e. col B) of the "ref page" worksheet

The trouble is that i have to copy the vlookup formula all the way to the end of the column for this to work - so i was hoping that if using VBA, perhaps the vlookup function could be incorporated into the VBA - thus excel will automatically do the vlookup when Col G is populated - is thsi possible?

Also, is it possible for the data located in Col G and H to be automatically copied to a completely new sheet?