Solved How to change a range of cells to the same data

March 16, 2017 at 10:52:08
Specs: Windows 7
I have 5 cells in different Sheets, I need that if one of those cells change, the other ones change to the same vale.

e.j. if i write in cell A1 "red", then A2, A3, B1, B2, B3 change to "red". But if then, I write "blue" in B2, then A1, A2, A3, B1, B3 change to "blue"

Thanks


See More: How to change a range of cells to the same data

Reply ↓  Report •

#1
March 16, 2017 at 11:34:15
You cannot do what you want to do with formulas in those cells. I'll use 2 cells as an example to show why it won't work with formulas in the target cells...

A1 ---> Manual Entry
B1 ---> =A1

A manual entry in A1 will show in B1. That's good.
A manual entry in B1 will replace the formula in B1. That's bad.

Now you are left with 2 manual entry cells.

I see 2 choices:

1 - Have another cell outside of your range as the "manual entry cell" for the entire range. Then set all cells in your range equal to that cell.

2 - Use a macro which monitors all cells in the range and if one of them changes, change them all.

I could offer the code for that macro, but I'd need to know a little bit more about what you mean by "5 cells in different Sheets"

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •

#2
March 16, 2017 at 11:54:12
The idea from the beginning was a Macro, I know that with formulas would be impossible. To explain a litlle of what I'm doing

I have a workbook with with 8 Sheets, 5 of them consult data based on the other 3, specifically calculations based on the date. For this I want to be able that in every sheet I have, if I change the date, the other 8 sheets change as well and Excel can calculate based on the new date. For these I need that if I change the date on Sheet 1, Sheet 6 changes. But if i go to sheet 2 and change the date, then sheet 6 changes to the new date and calculate accordingly


Reply ↓  Report •

#3
March 16, 2017 at 12:14:23
✔ Best Answer
Well, without specifics as to sheet names/number and cell references the best I can do is offer some generic code. If the following macro is pasted into the sheet module for all sheets, the specified range will reflect a change to any cell in that range on any sheet.

I know it is not exactly what you asked for (e.g. 5 out 8 sheets, etc.) but it's the best I can offer without more details.


Private Sub Worksheet_Change(ByVal Target As Range)
'Determine if change was made to specified range
  If Target.Cells.Count = 1 Then
    If Not Intersect(Target, Range("A2, A3, B1, B2, B3")) Is Nothing Then
      Application.EnableEvents = False
'Loop through Sheets setting all cells equal to the changed cell
        For sht = 1 To Sheets.Count
          Sheets(sht).Range("A2, A3, B1, B2, B3") = Target
        Next
       Application.EnableEvents = True
    End If
  End If
End Sub

Click Here Before Posting Data or VBA Code ---> How To Post Data or Code.


Reply ↓  Report •
Related Solutions


Ask Question