Mirror cells in multiple worksheets (1 Viewer)

cocowomble

Registered User.
Local time
Today, 14:43
Joined
May 25, 2014
Messages
25
hi all, still very much learning to use VBA, so any help would be appreciated.

I have a sheet called "Overview", Sheet1, sheet2.

"Overview" column A is a list of names. I want each name in the list to mirror cell A1 in each sheet.

eg
Overview A1 = Sheet1 A1
Overview A2 = Sheet2 A1 and so on,

but if change the name in a Sheet, I want this to reflect on the overview.
I hope that makes sense.
I have managed to put some VBA together that will make it work for one sheet (below), but struggling to put the code together to update the multiple sheets. so now need to add in "range in A2 - sheet2 A1"

Code in "Overviews is currently:

Private Sub Worksheet_Change(ByVal Target As Range)
Dim r1 As Range, r2 As Range
Set r1 = Range("A1")
Set r2 = Sheets("Sheet1").Range("A1")
If Intersect(Target, r1) Is Nothing Then Exit Sub
Application.EnableEvents = False
r2.Value = r1.Value
Application.EnableEvents = True
End Sub

I have also added code in each of the sheets to make sure it updates the overview, which works fine.

I hope that all makes sense and appreciate any help you can provide.
ta coco
 

ashleedawg

"Here for a good time"
Local time
Today, 14:43
Joined
Jun 22, 2017
Messages
154
You get an A for effort, but you don't need VBA for that! :)

On Sheet 1 or Sheet2, cell A1, you can enter a formula like =Overview!A2, where 'Overview' is the name of the first worksheet.

Example attached. :)
 

Attachments

  • multisheets.xlsx
    18.8 KB · Views: 126

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 05:43
Joined
May 7, 2009
Messages
19,169
if the other sheets are named "sheet1", "sheet2","sheet3", and so on, then you can put a formula on the first column of "overview" worksheet:


on A1:


=INDIRECT("Sheet"&ROW()&"!A1")


then just copy this formula down column A.
 

Rx_

Nothing In Moderation
Local time
Today, 15:43
Joined
Oct 22, 2009
Messages
2,803
https://www.ablebits.com/office-add...cel-indirect-function/#INDIRECT-another-sheet
Indirect is a wonderful tool!
In the site above, reference:
INDIRECT formula to dynamically refer to another worksheet
When building Dynamic Sheets for Excel Dashboards:
Example: https://exceljet.net/formula/dynamic-worksheet-reference

Imagine downloading multiple Regions data into multiple Worksheets.
On the main Dashboard, chose the worksheets with a formula.
The choice can be built into the dashboard interface (e.g. List box selector).

The Indirect provides many levels of additional variables to be used.
Start learning to use it for the simple task. It allows for growth.
 

Users who are viewing this thread

Top Bottom