Copy and Paste using VBA.

[spreadsheetlady]

Hi,

If A1 is not blank then Copy A1 and Paste in A2:A7.

I originally had simple links in cells A2:A7 that connected to A1. But
sometimes one of the cells in range A2:A7 may manually have a value typed in
them, which erases the link to A1.

I thought trying this using VBA would eliminate the cell linkage loss.

I'm new to VBA.

Any suggestions would be appreciated.
Amy

 

[B Lynn B]

Right-click the tab for the sheet where you want this to apply, select "View
Code", then paste in the code below. If you really want it to behave the
same as a link, then I'd take out the part about range("a1") <> "" (including
the underscore character and the line break before "Then"). That way,
whatever is in A1 will always be in A2:A7.

Private Sub Worksheet_Change(ByVal Target As Range)

If Not Intersect(Target, Range("A1")) Is Nothing _
And Range("A1") <> "" Then
Range("A2:A7").Value = Range("A1").Value
End If

End Sub

 

[spreadsheetlady]

Thank-you B Lynn B. It works perfectly and is exactly what I wanted.
Amy