シートの全データを２次元のテーブルに格納したい

質問

シートにある全データを２次元のテーブルに格納したいです

EXCEL VBA 初心者です。
シートにあるデータを一時的に同じ状態で二次元のテーブルに格納するにはどのようにしたら良いでしょうか
変数宣言から教えてください。
なお、このデータの中身は文字、数字、日付と様々で、行数も列数も随時変わります。
宜しくお願い致します。

回答No.2

DOUGLAS_ · ベストアンサー

#1 DOUGLAS_ です。

＞ただし、セルの書式まで保持したいということになれば、
＞配列を使うことは煩わしいだけになろうかと存じます
とは書きましたが、
Dim myTbl As Range
とすれば、さほど煩わしくもなさそうです。

   ただし、この場合、
Set myTbl = ActiveSheet.UsedRange
してから、「ActiveSheet.UsedRange」の書式等を変更した場合、
myTbl.Copy Sheets("Sheet2").Range("A1")
のところでは、元々のシートの状態ではなくて、現在（変更後）のシートの状態がコピーされますので、「<<一時的に>>同じ状態で二次元のテーブルに格納」する意味がないですかね。

Sub Macro1()
     Dim myTbl As Range
     Set myTbl = ActiveSheet.UsedRange
     Debug.Print "テーブルの行数：" _
         & myTbl.Rows.Count
     Debug.Print "テーブルの列数：" _
         & myTbl.Columns.Count
     Debug.Print "テーブルの行見出し：" _
         & Join(Application.Transpose(Application.Transpose(myTbl.Rows(1))), "/")
     Debug.Print "テーブルの列見出し：" _
         & Join(Application.Transpose(myTbl.Columns(1)), "/")
     Debug.Print "テーブルの２行目３列目の値：" _
         & myTbl.Cells(2, 3).Value
     '別のシートの A1 に復元する
     myTbl.Copy Sheets("Sheet2").Range("A1")
End Sub

お礼

DOUGLASさん

早速の丁寧な回答ありがとうございます。
今回シートのデータは計算式は無いのですが非常に参考になりました。
自分の知識不足が恥ずかしいです。
ありがとうございましたｍ（ｖｖ）ｍ

回答No.1

DOUGLAS_

＞シートの全データ
   範囲は、
ActiveSheet.UsedRange
で取得できるのではないかと存じます。
　場合によっては、
Range("a1").CurrentRegion
の方がよいかも知れません。

＞シートにあるデータを一時的に同じ状態で二次元のテーブルに格納する
  「同じ状態」とお書きなのが、計算式等を度外視した単なるデータなのか、数式バーに表示されているものと「同じ状態」なのかによって変わりますが、下記の様なことでよろしいでしょうか？

Sub Macro1()
     Dim myTbl As Variant
     myTbl = ActiveSheet.UsedRange
     Debug.Print "テーブルの行数：" _
         & UBound(myTbl)
     Debug.Print "テーブルの列数：" _
         & UBound(myTbl, 2)
     Debug.Print "テーブルの行見出し：" _
         & Join(Application.Index(myTbl, 1), "/")
     Debug.Print "テーブルの列見出し：" _
         & Join(Application.Index(Application.Transpose(myTbl), 1), "/")
     Debug.Print "テーブルの２行目３列目の値：" _
         & myTbl(2, 3)
     '別のシートの A1 に復元する
     Sheets("Sheet2").Range("A1").Resize(UBound(myTbl), UBound(myTbl, 2)) = myTbl
End Sub

   見た目のデータではなくて数式を保持したい場合は、
myTbl = ActiveSheet.UsedRange
のところを
myTbl = ActiveSheet.UsedRange.Formula
に変えてください。

   ただし、セルの書式まで保持したいということになれば、配列を使うことは煩わしいだけになろうかと存じますので、この場合は、シートごとコピーすることをお勧めいたします。