Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste using VBA.
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste using VBA.
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Copy and Paste using VBA.
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Can't Copy and Paste or Paste Special between Excel Workbooks | Excel Discussion (Misc queries) | |||
Automating copy/paste/paste special when row references change | Excel Programming | |||
help w/ generic copy & paste/paste special routine | Excel Programming | |||
Excel cut/Paste Problem: Year changes after data is copy and paste | Excel Discussion (Misc queries) | |||
Copy and Paste macro needs to paste to a changing cell reference | Excel Programming |