Auto Fill Row

November 18, 2010 at 08:24:45
Specs: Windows Vista
I would like to Auto fill a new row if I type in a duplicate cell info in the new row. If I type in 'Five' in the new row and 'Five' is in another row I would like to copy and fill the new row with info from the old row.

See More: Auto Fill Row

Report •

November 24, 2010 at 10:01:54
Try this code in a backup copy of your workbook since macros can not be undone.

Right click the sheet tab for where you want this to happen and paste the code into the pane that opens.

The code assumes that you are entering your data (e.g. 'Five') in Column A and that you are searching for it in Column A. It will find the first occurrence of your data (e.g. 'Five') in Column A and copy that row if the data is found.

Option Explicit
Private Sub Worksheet_Change(ByVal Target As Range)
Dim c As Range
Application.EnableEvents = False
'Was change made to column A?
 If Target.Column = 1 Then
'If yes, can value be found above it in Column A?
  With Range("A1:A" & Target.Row - 1)
   Set c = .Find(Target, lookat:=xlWhole)
'If Yes, then copy other row
    If Not c Is Nothing Then
     c.EntireRow.Copy Destination:=Range(Target.Address)
    End If
   End With
 End If
 Application.EnableEvents = True
End Sub

Posting Tip: Before posting Data or VBA Code, read this How-To.

Report •
Related Solutions

Ask Question