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
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 06:50 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