 |
| FREE Excel STUFF |
Search |
| PRODUCTS |
| Development |
| Contact |
|
Complete Excel Excel Training Course. Instant Buy/Download, 30 Day Money Back Guarantee & Free Question Support 24/7 FREE DOWNLOADS SmartVBA | SmartVB6 | CodeVBA | CodeVB6 | Code Generator Pro | TraderXL Pro Package | Catalog | MORE.. |
It's fairly ordinary to have the same data appear in multiple worksheets simultaneously, you can use Excel's tool for grouping so data in one Workbook can be entered into multiple Worksheets at the same time. We also have a quicker and more flexible approach that uses a couple of lines of VBA code.
Excel's mechanism for making data go to multiple places at once is a feature known as Group. It works by grouping the Worksheets together so that they're all linked within the Workbook. To use Group, simply click the sheet that you will be entering the data into, hold down your Ctrl key, and click on the name tabs of the other Worksheets where you want the same data to go.
When you enter data into any Cells on your Worksheet, they will automatically be entered in the other grouped Worksheets. Mission accomplished.
To ungroup your Worksheets, either select one Worksheet that is not part of the group, or right click on any name tab and select Ungroup Sheets.
When your Worksheets are grouped together, you can look up to the title bar and you will see the word Group in square brackets. This is letting you know that your Worksheets are still grouped. Unless you have eagle eyes and a mind like a steel trap, however, it is very likely that you won't notice this or forget that you have your Worksheets grouped. For this reason, we gently suggest that you should ungroup your sheets as soon as you're done doing what you need to do.
While this method is easy, it does mean you need to remember to Group/Ungroup your sheets as needed or else you will inadvertently overtype data from another worksheet. It also means that the simultaneous data entries will occur regardless of the cell you are in at the time. For example, you may only want the simultaneous entries to occur whenever you are in a particular range.
There is a way we can overcome these shortcomings by using some very simple Visual Basic for Applications (VBA) code. For this code to work, it must reside within the Private Module for the Sheet Object. To quickly go to the Private Module, right click on the Sheet Name tab and select View Code. We can then use one of Excel's Sheet Events, which are very much as the name suggests: they are events that take place within your Worksheet, such as changing a cell, selecting a range, activating, deactivating, and many others.
The first thing to do to make grouping work is to name the range of Cells (MyRange)that you want to have grouped so the data automatically show on other worksheets.
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range("MyRange"), Target) Is Nothing Then
Sheets(Array("Sheet5", "Sheet3", "Sheet1")).Select
Else
Me.Select
End If
End Sub
See Also: Group Excel Worksheets/Sheets by Color | Sort Sheets In Excel | Sheet/Worksheet CodeNames | Add Sheets | Add Sheets in Numeric Order | Add Sheets in Weekday Order | Add Sheets in Month Order
of
DownloaderXL and
AnalyzerXl
This Month we have some great VBA code from one of our Excel Help Forum Gurus - Krishnakumar or Kris for short. Kris not only freely shares his vast knowledge of Excel on our help forum, he also maintains a Blog dedicated to Excel It's WELL worth the visit.
Anyway, Kris has posted and a few example of how to fill a UserForm ListBox with Workbook & Worksheet Names from a chosen Folder. The examples can be downloaded here This Thread is ONLY 1 of many in our "Hey, That is Cool" forum.
Hundreds more free Excel add-ins, templates, tutorials & demos.
Special! Free Choice of Complete Excel Training Course OR Excel Add-ins Collection on all purchases totaling over $64.00. ALL purchases totaling over $150.00 gets you BOTH! Purchases MUST be made via this site. Send payment proof to [email protected] 31 days after purchase date.
Microsoft � and Microsoft Excel � are registered trademarks of Microsoft Corporation. OzGrid is in no way associated with Microsoft
