大体でIT

-ちょっと使えるネタを紹介-

大体でIT

“貼付範囲”を指定してテーブルへ貼り付ければ、エラーを出さずにテーブルの大きさを自動調整してすべてのテーブルデータを貼付けすることができます。

はじめに

この記事ではテーブルへのデータ入力の際に“一部データしか貼付けできない”や“貼付けデータがエラーになる”といった問題を解決します。

これは、貼付元と貼付先のデータ配列数が違うからですが、“貼付範囲”を指定して貼りつけるとこれらの問題点が解消されます。

“貼付範囲”指定した場合の結果と方法についてご紹介していきます。

目次から使いそうな説明のところへ移動すると便利ですよ。

目次

【問題】一部データしか貼付けできない、貼付けデータがエラーになる

テーブルへのデータ貼付けで問題が起こるのは貼付元と貼付先の配列数の違いのためです。次のような問題があります。

一部データしか貼付けできない問題(大→小へ貼付け)

貼付元が大きく貼付先が小さい場合、すべてのデータを貼付けすることができません。

貼付先のテーブル配列分しか貼付けされません。

一部データしか貼付けできない問題(大→小へ貼付け)

また、逆の場合でも同じで問題があります。

貼付けデータがエラーになる問題(小→大へ貼付け)

テーブルの貼付元が小さく貼付先が大きい場合、貼付元の行数を超えた分はエラーは入力されてしまいます。

貼付けデータがエラーになる問題(小→大へ貼付け)

データはすべて入力されましたが、エラーはあまり気持ちいいものではありませんよね。

これらの問題点を“貼付範囲”を指定してあげると解決することができます。

【解決】“貼付範囲”を指定して貼付け

改善したのは貼付ける際に“貼付元の範囲”を指定して貼付けたことです。

貼付先のテーブルを自動で大きくして全データ貼付可能です(大→小へ貼付け)

貼付先へ“貼付元”と同じ範囲を指定して貼付けるとテーブルの大きさを自動調整し全データを入力することができます。

貼付先のテーブルを自動で大きくして全データ貼付可能です(大→小へ貼付け)

貼付先のテーブルの大きさを自動で大きくして全データを貼り付けられています。

貼付け先のデータ配列が小さい場合でも同様に問題を解決できます。

貼付先テーブルにエラーなく貼付けられます(小→大へ貼付け)

解決方法は同じで“貼付元”と同じ範囲を指定して貼付先へ貼付けます。

貼付先テーブルにエラーなく貼付けられます(小→大へ貼付け)

エラー表示なく貼付元のデータ分だけ貼付けることができましたね。

VBAコードは貼付先が大きいもしくは小さいに限らず同じコードで対応することができますので便利です。

“貼付範囲”指定して貼り付けるVBAコード

貼付元の右下のセルを取得するために“Ubound”を使っています。

Ubound(Hozon,1)で貼付元の行数、Ubound(Hozon,2)で貼付元の列数を取得しています。

ここで、Hozonは貼付元のデータを格納した配列です。

“貼付範囲”指定して貼付けるVBAコード

テーブルを扱うコードは“構造化参照”と“ListObjects”がありますのでそれぞれで記述したコードを書いておきます。どちらを使っても同じ結果になります。

“構造化参照”を使った場合です。

VBAコード:“貼付範囲”を指定して貼りつける(構造化参照)

Sub Paste1()
    
  '貼付元のテーブル1のデータを配列へ格納
  Dim Hozon As Variant
  With ActiveSheet
      Hozon = .Range("テーブル2")
  End With
  
  'テーブル2へ“貼付範囲”指定で貼り付けます
  With ActiveSheet
      Set rg = .Range("テーブル1")
      .Range(rg.Cells(1, 1), rg.Cells(UBound(Hozon, 1), UBound(Hozon, 2))) = Hozon
  End With
  
End Sub

“ListObjects”を使った場合です。

VBAコード:“貼付範囲”を指定して貼りつける(ListObjects)

Sub Paste2()
  
  '貼付元のテーブル1のデータを配列へ格納
  Dim Hozon As Variant
  With ActiveSheet
      Hozon = .ListObjects("テーブル1").DataBodyRange
  End With
  
  'テーブル2へ“貼付範囲”指定で貼り付けます
  With ActiveSheet
      Set rg = .ListObjects("テーブル2").DataBodyRange
      .Range(rg.Cells(1, 1), rg.Cells(UBound(Hozon, 1), UBound(Hozon, 2))) = Hozon
  End With
   
End Sub

同じように“貼付範囲”を指定して列データのみの貼付けも可能です。

“貼付範囲”を指定した列のみの貼付けも可能です

特定列の場合は見出しの“身長”を使って列の範囲を指定しています。

“貼付範囲”を指定した列のみの貼付けも可能です

“構造化参照”と“ListObjects”の両方がありますのでお好きな方を使うといいかと思います。

“構造化参照”の場合です。

VBAコード:“貼付範囲”を指定して列のみを貼り付ける(構造化参照)

Sub Paste3()
  
  '貼付元のテーブル1から“身長”の列データのみ配列へ格納
  Dim Hozon As Variant
  With ActiveSheet
      Hozon = .Range("テーブル1[身長]")
  End With
  
  'テーブル2へ“貼付範囲”指定で貼り付けます
  With ActiveSheet
      Set rg = .Range("テーブル2[身長]")
      .Range(rg.Cells(1, 1), rg.Cells(UBound(Hozon, 1), UBound(Hozon, 2))) = Hozon
  End With
  
End Sub

“ListObjects”の場合です。

VBAコード:“貼付範囲”を指定して列のみを貼り付ける(ListObjects)

Sub Paste4()
  
  '貼付元のテーブル1から“身長”の列データのみ配列へ格納
  Dim Hozon As Variant
  With ActiveSheet
      Hozon = .ListObjects("テーブル1").ListColumns("身長").DataBodyRange
  End With
  
  'テーブル2へ“貼付範囲”指定で貼り付けます
  With ActiveSheet
      Set rg = .ListObjects("テーブル2").ListColumns("身長").DataBodyRange
      .Range(rg.Cells(1, 1), rg.Cells(UBound(Hozon, 1), UBound(Hozon, 2))) = Hozon
  End With
  
End Sub

おわりに

テーブルをExcel VBAで操作していると“貼付けできない”や“エラー表示になる”といった問題点がでてきました。

“貼付範囲”を指定してあげるとテーブルの大きさも自動調整してくれて全データを確実に貼り付けることができます。

テーブルでExcel VBAを操作する場合はこの方法を覚えておくと大変便利です。ご参考になればと思います。

最後までご覧くださいましてありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

アーカイブから探す