VBA Codes Needed for Excel 1

[bayuga]

I have a workbook with about 10 sheets. 1 sheet is the main table and the other 9 are for teams. Let me back up. The main sheet has the team members name and their team manager next to their name. The other 9 sheets is by Team Manager. Now the main sheet needs to be protected at all times so that noone can use that sheet to make changes. I want them to make changes on individual sheet.

Here is my question, What code should I use so that when I make a change (Add or delete a name, etc..) to the individual sheet,the master sheet will automatically have the updated information?

I hope this makes sense.

 

[SkipVought]

Hi,

Not a good design. You drastically LIMIT the capabilities of using Excel's plethora of data analysis and reporting features when you chop up similar data into different sheets.

Put ALL your team data in one sheet and add TeamManager as a new field.

It is harder to maintain.

It will make life SOoooooooo much easier.

Skip,

_[red][/red]

 

[bayuga]

Unfortunately, This is how the company wants to spreadsheet to look like. So do you have any VBA codes that I can use?

 

[SkipVought]

You can still make the REPORTS look like that, but the Table Source ought to be consolidated.

Report using AutoFilter, PivotTable, MS Query via Data/Get External Data...

Skip,

_[red][/red]

 

[bayuga]

If I us the Pivot table, would the report stay the same when I make updates to the master list sheet

 

[SkipVought]

name your source table range Database

How can I rename a table as it changes size faq68-1331

This has a special meaning in Excel.

Point each of your PivoTables to Database in the Source Data Window of the PT Wizard.

In the workbook_SheetActivate event...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   select case sh.name
      case "pivot sheet 1","pivot sheet 2" 'list all your pivot table sheets 
         sh.pivottables(1).pivotcache.refresh
   end select
End Sub

or by exception...

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
   select case sh.name
      case "MasterSheetName"  'master sheetsheets 
      case else
         sh.pivottables(1).pivotcache.refresh
   end select
End Sub

Skip,

_[red][/red]