Prev: Using Cell.Replace
Next: Advanced Filtering top values
From: spreadsheetlady on 15 Apr 2010 16:39
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
From: B Lynn B on 15 Apr 2010 17:08
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" wrote:

> 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
From: spreadsheetlady on 15 Apr 2010 20:31
Thank-you B Lynn B. It works perfectly and is exactly what I wanted.
Amy



"B Lynn B" wrote:

> 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" wrote:
>
> > 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
 | 
Pages: 1
Prev: Using Cell.Replace
Next: Advanced Filtering top values