Go Back   Access World Forums > Apps and Windows > Excel

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 07-17-2017, 07:55 AM   #1
cocowomble
Newly Registered User
 
Join Date: May 2014
Posts: 25
Thanks: 4
Thanked 0 Times in 0 Posts
cocowomble is on a distinguished road
Mirror cells in multiple worksheets

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

cocowomble is offline   Reply With Quote
Old 07-29-2017, 02:40 AM   #2
ashleedawg
"Here for a good time"
 
ashleedawg's Avatar
 
Join Date: Jun 2017
Location: Canada
Posts: 152
Thanks: 18
Thanked 37 Times in 34 Posts
ashleedawg is on a distinguished road
Re: Mirror cells in multiple worksheets

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.
Attached Files
File Type: xlsx multisheets.xlsx (18.8 KB, 18 views)
__________________
_______________________________________
Back in the Fire
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
ashleedawg is offline   Reply With Quote
Old 07-29-2017, 11:34 PM   #3
arnelgp
Newly Registered User
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 4,499
Thanks: 47
Thanked 1,509 Times in 1,436 Posts
arnelgp has a spectacular aura about arnelgp has a spectacular aura about arnelgp has a spectacular aura about
Re: Mirror cells in multiple worksheets

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.

__________________
"Never stop learning, because life never stops teaching"
arnelgp is online now   Reply With Quote
The Following User Says Thank You to arnelgp For This Useful Post:
Rx_ (09-24-2017)
Old 09-24-2017, 11:34 AM   #4
Rx_
Nothing In Moderation
 
Rx_'s Avatar
 
Join Date: Oct 2009
Location: Denver, Colorado
Posts: 2,745
Thanks: 605
Thanked 327 Times in 299 Posts
Rx_ has a spectacular aura about Rx_ has a spectacular aura about
Thumbs up Re: Mirror cells in multiple worksheets

https://www.ablebits.com/office-addi...-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...heet-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.

Rx_ is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Multiple workbook and multiple worksheets into one MS Access table yasuka808 Modules & VBA 3 08-27-2011 03:37 AM
Import multiple worksheets wpryan Modules & VBA 1 05-09-2010 04:55 AM
Consolidate Multiple Worksheets lhooker Excel 2 09-17-2009 12:32 PM
Excel Help - Why does a 0 appear when copying cells between worksheets? optimus_1 Excel 3 02-16-2009 02:23 AM
Locking cells in protected worksheets from cut/paste/drag tas666 Excel 1 09-01-2008 07:29 AM




All times are GMT -8. The time now is 02:51 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Sponsored Links

How to advertise

Media Kit


Powered by vBulletin®
Copyright ©2000 - 2017, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World