The following code is very good.
Sub Macro1()
'Convert plain excel data to Excel Table. (Excel workbook consist of 3 sheets)
For i = 1 To 3
Worksheets(i).Select
Worksheets(i).ListObjects.Add SourceType:=xlSrcRange, Source:=Range("A1:C50"), XlListObjectHasHeaders:=xlYes
Next i
'Load data from Excel Table to Variant
Dim Variant1 As Variant
Variant1 = Worksheets(1).ListObjects(1).DataBodyRange
Dim Variant2 As Variant
Variant2 = Worksheets(2).ListObjects(1).DataBodyRange
Dim Variant3 As Variant
Variant3 = Worksheets(3).ListObjects(1).DataBodyRange
'Loop through each item in Third Column of Table
Dim x1 As Long
For x1 = LBound(Variant1) To UBound(Variant1)
Debug.Print Variant1(x1, 3)
Next x1
Dim x2 As Long
For x2 = LBound(Variant2) To UBound(Variant2)
Debug.Print Variant2(x2, 3)
Next x2
Dim x3 As Long
For x3 = LBound(Variant3) To UBound(Variant3)
Debug.Print Variant3(x3, 3)
Next x3
End Sub
I want to shorten the above code.
The following codes need to be repaired.
Sub Macro2()
'Load data from Excel Table to Variant
For i = 1 To 3
'The following codes need to be repaired.
'Dim "Variant" & i As Variant
'"Variant" & i = Worksheets(i).ListObjects(1).DataBodyRange
Next i
End Sub
I have tried an alternate approach using Scripting.Dictionary as BigBen recommended. But the following code needs to be repaired.
Sub Macro3()
Dim dict As Object
Set dict = Nothing
Set dict = CreateObject("Scripting.Dictionary")
dict.RemoveAll
For i = 1 To 3
dict.Add Key:=i, Item:=Worksheets(i).ListObjects(1).DataBodyRange
Next i
End Sub
You can use an array:
Sub Macro3()
Dim myInteger As Integer
Dim data() As Variant, i As Long, r As Long, arr
myInteger = 3
ReDim data(1 To myInteger) 'can't `Dim` using a variable...
For i = 1 To myInteger
data(i) = ThisWorkbook.Worksheets(i).ListObjects(1).DataBodyRange.Value
Next i
For i = 1 To myInteger
arr = data(i)
For r = 1 To UBound(arr, 1)
Debug.Print arr(r, 3)
Next r
Next i
End Sub