How to copy cells/rows with formula?

[GeekyGav]

Hi all,

I hope you can help?

This is my code... I'm not sure where I'm going wrong but the row that is being inserted does not include any formulas that should be copied from the initial row.

Column B has a VLOOKUP formula and Column C has a =NOW() formula and I'd like these copied down when the new row is automatically inserted.

Any advice would be fantastic!

Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Target, Me.Columns(9)) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value <> " " Then
Me.Rows(Target.Row + 1).Insert
Me.Range(Me.Cells(Target.Row, 1), Me.Cells(Target.Row, 9)).Copy Destination:=Sheets(Me.Cells(Target.Row, "A").Value).Cells(Sheets(Me.Cells(Target.Row, "A").Value).Cells(Rows.Count, 1).End(xlUp).Row + 1, 1)
Application.CutCopyMode = False
Me.Cells(Target.Row + 1, 1).Activate
End If
End If
End If

End Sub

[mgm05267]

Dear GeekyGav,

Have you placed this code in Sheet or module....!!??

Because, worksheet_Change will have no effect on the code written above..

Please can you be more specific on the requirement.

Regards,

MGM

[Aflatoon]

You appear to be trying to copy the cells to a different worksheet to the one where you inserted the row - is that correct?

[GeekyGav]

Hi,

Thanks for the quick reply!

This code was placed in sheet... Is that where I have gone wrong?

I have attached a copy of my workbook...

As you will see... The first row (Row 11) contains a number of list boxes, along with a vlookup and the =NOW() formula...
When a value is entered into the last cell (I11) the row is copied and pasted to another sheet based on the room number and a new row is inserted on the sheet titled 'FrontPage' but without the vlookup formula and the =NOW() formula.

I would like any newly inserted rows to contain those formulas automatically.

[Aflatoon]

The code is in the correct location. Try this:
Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo excuse_me
If Not Intersect(Target, Me.Columns(9)) Is Nothing Then
If Target.Cells.Count = 1 Then
If Target.Value <> "" Then
Application.EnableEvents = False
Me.Rows(Target.Row + 1).Insert
Me.Cells(Target.Row, "B").Resize(1, 2).Copy Me.Cells(Target.Row + 1, "B")
Me.Cells(Target.Row + 1, "A").Value = "-"
With Sheets(Me.Cells(Target.Row, "A").Value)
Me.Cells(Target.Row, 1).Resize(1, 9).Copy Destination:=.Cells(.Rows.Count, 1).End(xlUp).Offset(1)
End With
Application.CutCopyMode = False
Me.Cells(Target.Row + 1, 1).Activate
End If
End If
End If

leave:
Application.EnableEvents = True
Exit Sub
excuse_me:
Resume leave
End Sub

[GeekyGav]

Thank you so much!! That works perfectly!

I have another question regarding my vlookup...

I'd like a list box to display a list of computer names in Column F which is relevant to the selection made in Column A... So... Say 'G8' was selected from the list in Column A... I'd like a list of all of the computer names in G8 to appear in the list box in column F...

Any advice on how to achieve this would be great!!