Thanks to Ron de Bruin, I've got code that copies data from a named range to
a "database" worksheet. I want to loop through all named ranges (which
represent a dynamic range in each worksheet) and copy them to the "database"
worksheet.

Here's what I've got:

Sub Consolidate()
Dim SourceRange As Range, DestRange As Range
Dim DestSheet As Worksheet, Lr As Long
Dim rName As Variant
Dim i As Long

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Define the named range array
rName = Array("FBU1", "FBU2", "FBU3", "FBU4", "FBU5", "FBU6", "STAT1")

'Clear the Destination sheet
Sheets("Consolidated").Range("2:2", Selection.End(xlDown)).Delete
Shift:=xlUp

'fill in the Source Sheet and range
For i = LBound(rName) To UBound(rName)

Set SourceRange = Range(rName)

'Fill in the destination sheet and call the LastRow
'function to find the last row
Set DestSheet = Sheets("Consolidated")
Lr = LastRow(DestSheet)

'With the information from the LastRow function we can create a
'destination cell
Set DestRange = DestSheet.Range("A" & Lr + 1)

'Copy the source range and use PasteSpecial to paste in the destination
cell
SourceRange.Copy
DestRange.PasteSpecial xlPasteValues, , False, False
Application.CutCopyMode = False
Next i

With Application
.ScreenUpdating = True
.EnableEvents = True
End With

End Sub

When running this, I get a method Range of object _Global failed.