Pulling Data from Master Table Dynamically Into Secondary Workbook Sheets

[retailmessiah]

Good morning, Good Day, Good evening,

I've been searching at this for several hours, and I'm hoping that you
all can help me. I've been away from excel for far too long, and this
new job is resubmerging me, and I'm afraid I'm drowning.

I'm attempting to split out the data in the master table, "Sheet1" in
my sample sheet (here: http://dl.dropbox.com/u/7974125/Misc/DynamicDemo.xls)
into separate sheets. I'd like it to do this dynamically if possible
so that in the future I can add more employees, or subtract them and
it will update. I've created a named range called 'ALL' that I can
update as needed to encompass any new additions as needed. So, I need
the person's ID and Amount from the applicable columns to be
dynamically exported to the sheet tabs. I'd like to use the sheet name
lookup formula I have on the 'A' sheet so that I can hopefully expand
this in the future, and keep the same formulas. I hope that makes
sense.. I know how it is to read what makes sense to one person, but
is absolute gibberish when read by me!

I guess to restate, I'd like a dynamic list that is automated on sheet
tabs A - F that pulls the ID number and amount of each ROW in the
named range 'ALL' that matches the column header (row 1) of the given
sheet. Please see my sample sheet, I think it may be more clear!

Thanks in advance for anyone who has the time to look at this. It
really is appreciated more than you know. If you're in Chicagoland I'd
love to buy a drink for whomever can help!

Thanks,
-John

 

[Jerry Beaucaire]

I would use a workbook level event macro to handle this. This macro
will activate every time you change sheets. As long as the sheet
isn't named "Sheet1", it will grab all the data for that sheet from
sheet1 instantly every time. Just scrolling through the tabs will
update them automatically.

Open the VBEditor and put this code into the THISWORKBOOK module:

Option Explicit

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
'update all secondary sheets when they are activated
Dim COL As Long, LR As Long

If Sh.Name <> "Sheet1" Then
Application.ScreenUpdating = False
Sh.UsedRange.Clear
With Sheets("Sheet1")
.AutoFilterMode = False
On Error Resume Next
COL = .Rows(1).Find(Sh.Name, LookIn:=xlValues,
LookAt:=xlWhole).Column
If COL = 0 Then GoTo ErrorExit

.Rows(1).AutoFilter
.Rows(1).AutoFilter COL, "<>"
LR = .Range("B" & .Rows.Count).End(xlUp).Row
If LR = 1 Then GoTo ErrorExit

.Range("B1:B" & LR).Copy [A1]
.Range(.Cells(1, COL), .Cells(LR, COL)).Copy [B1]
[B1] = "Amount"
End With
Application.ScreenUpdating = True
End If
Exit Sub

ErrorExit:
[A1] = "no values found"
Application.ScreenUpdating = True

End Sub