This macro seems to work ok for me:
Option Explicit
Sub testme01()
Dim curWks As Worksheet
Dim newWks As Worksheet
Dim myRng As Range
Dim LastRow As Long
Dim LastCol As Long
Dim iCol As Long
Dim myInputRng As Range
Dim myCell As Range
Application.ScreenUpdating = False
Set curWks = Worksheets("sheet1")
Set newWks = Worksheets.Add
With curWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myInputRng = .Range("a1", .Cells(LastRow, LastCol))
Application.StatusBar = "determining Region headers"
With myInputRng.Columns(2)
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=newWks.Range("A1"), Unique:=True
End With
End With
With newWks
With .Range("a:a")
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes
End With
Set myRng = .Range("a2", .Cells(.Rows.Count, "a").End(xlUp))
If myRng.Rows.Count > 250 Then
MsgBox "too many Training classes to fit on the worksheet!"
GoTo ExitNow:
End If
myRng.Copy
.Range("b1").PasteSpecial Transpose:=True
.Range("a:a").ClearContents
End With
With curWks
Application.StatusBar = "Copying departments"
With .Range("a:a")
.AdvancedFilter Action:=xlFilterCopy, _
CopyToRange:=newWks.Range("a1"), Unique:=True
.Cells.Sort Key1:=.Columns(1), Order1:=xlAscending, _
Header:=xlYes
End With
End With
With newWks
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
LastCol = .Cells(1, .Columns.Count).End(xlToLeft).Column
Set myRng = .Range("B2", .Cells(2, LastCol))
Application.StatusBar = "Populating lots of formulas"
For Each myCell In myRng.Cells
With myCell
.FormulaArray _
= "=INDEX(" & myInputRng.Columns(3).Address _
(external:=True, ReferenceStyle:=xlR1C1) & "," _
& "match(1,(" & myInputRng.Columns(1).Address _
(external:=True, ReferenceStyle:=xlR1C1) _
& "=rc1)*(" _
& myInputRng.Columns(2).Address _
(external:=True, ReferenceStyle:=xlR1C1) _
& "=r1c),0))"
End With
Next myCell
Application.StatusBar = "Filling the formulas down"
myRng.AutoFill _
Destination:=myRng.Resize(LastRow - 1)
Application.StatusBar = "Cleaning up"
With myRng.Resize(LastRow - 1)
.Value = .Value
.Replace what:="#n/a", replacement:="", lookat:=xlWhole, _
MatchCase:=False
End With
Application.Goto .Range("a1"), scroll:=True
.Range("b2").Select
ActiveWindow.FreezePanes = True
With .UsedRange
.Columns.AutoFit
End With
End With
ExitNow:
With Application
.ScreenUpdating = True
.StatusBar = False
End With
End Sub
If you're new to macros, you may want to read David McRitchie's intro at:
http://www.mvps.org/dmcritchie/excel/getstarted.htm