Option Explicit
Sub Test()
Dim tbl As Variant
Dim cel As Range
' Définir la première cellule de la plage de données à lire
Set cel = ThisWorkbook.Worksheets(1).Range("A1")
'ou bien n'importe quelle première cellule d'un autre classeur par exemple :
' Set cel = Workbooks("MonClasseur.xlsx").Worksheets("MaFeuille").Range("B3")
' Lire le tableau VBA
tbl = LireTableauVBA(cel, , 7)
' Et ensuite on fait ce qu'on veut de ce tableau VBA, par exemple :
Dim wbk As Workbook
Set wbk = Workbooks.Add(xlWBATWorksheet)
Set cel = wbk.Worksheets(1).Range("B12")
cel.Resize(UBound(tbl), UBound(tbl, 2)).Value = tbl
End Sub
Public Function LireTableauVBA(celDebut As Range, Optional nbLignes, Optional nbColonnes) As Variant
Dim rng As Range, tbl(1 To 1, 1 To 1) As Variant, drL As Long, drC As Long
With celDebut.Parent
' dernière ligne
If IsMissing(nbLignes) Then
Set rng = .Cells.Find("*", , , , xlByRows, xlPrevious)
If rng Is Nothing Then
drL = celDebut.Row
Else
drL = IIf(rng.Row < celDebut.Row, celDebut.Row, rng.Row)
End If
Else
If Val(nbLignes) = 0 Then Err.Raise 380
drL = celDebut.Row + Val(nbLignes) - 1
End If
' dernière colonne
If IsMissing(nbColonnes) Then
Set rng = .Cells.Find("*", , , , xlByColumns, xlPrevious)
If rng Is Nothing Then
drC = celDebut.Column
Else
drC = IIf(rng.Column < celDebut.Column, celDebut.Column, rng.Column)
End If
Else
If Val(nbColonnes) = 0 Then Err.Raise 380
drC = celDebut.Column + Val(nbColonnes) - 1
End If
Set rng = .Range(celDebut, .Cells(drL, drC))
If rng.Count = 1 Then
tbl(1, 1) = celDebut.Value
LireTableauVBA = tbl
Else
LireTableauVBA = rng.Value
End If
End With
End Function