Samlig af data fra forskellige ark

Option Explicit

Dim wb As Workbook
Dim MinusArk As Worksheet
Dim ws() As Worksheet, wsOpslag As Worksheet
Dim rCell As Range, rCol As Range, rOmråde As Range
Dim Count As Integer
Function ArkOmråde(Opslag As Integer, Dette_Ark_Tekst As String, Hent_Kolonne As Integer) As String
    Application.Volatile
    Set wb = ThisWorkbook
    Set MinusArk = Sheets(Dette_Ark_Tekst)
    ReDim ws(wb.Sheets.Count)
    For Count = 1 To wb.Sheets.Count
        Set ws(Count) = Sheets(Count)
    Next
    For Count = 1 To wb.Sheets.Count
        If ws(Count).Name <> MinusArk.Name Then
            Set rCol = ws(Count).Range("A1")
            Set rCol = Range(rCol, rCol.End(xlDown))
            For Each rCell In rCol
                If rCell.Value = Opslag Then
                    Set wsOpslag = ws(Count)
                    Exit For
                End If
            Next
        End If
    Next
ArkOmråde = ""
    On Error GoTo Errorhandler
        Set rOmråde = wsOpslag.UsedRange
    On Error GoTo 0
        ArkOmråde = Application.WorksheetFunction.VLookup(Opslag, rOmråde, Hent_Kolonne, False)
    Exit Function
Errorhandler:
    ArkOmråde = ""
End Function