Excelのテーブル形式のデータからタイトル行以外のデータ部分のみコピーして値のみ貼り付けるマクロ

2022年1月30日2022年4月1日

Excelのテーブル形式にて入力したデータ部分のみをコピーして他のシートや別の場所に「値のみ」貼り付ける作業をするにはどうすればよいのか調べたので備忘記録として記事にしておきます。

テーブル形式で入力すると数式が自動でコピーされたり、デザインが崩れないので便利。

個人的な意見ですが、Excelで何かデータを入力する際にはテーブル形式でデータを入力することが良いと考えています。その理由としては

合計行（集計行）を作成する必要がない。
1行おきにデータに色を自動でつけてくれる
数式が自動でコピーされる

といったメリットがあるからです。

合計行（集計行）に関してはテーブルデザインタブの「集計行」をクリックすると自動で集計行が作成されます↓

テーブルデータの一番下に集計行が表示されます

なお、1行おきにデータに色をつける方法は「条件付き書式」を使用してもできるのですが、テーブルを作成するやりかたのほうが手軽です。

数式のコピーに関しても同様で、1つのセルに対して数式を入力してエンターで確定させると、

他のテーブル内のデータに関しても同様の数式を入力してくれます↓

以上のことから現金出納帳などのデータをExcelで作成する際にはテーブル形式で作成するのがお勧めです。

テーブル内のタイトル行以外のデータ部分のみを扱うにはActiveSheet.ListObjects(1).DataBodyRange～

前置きはさておき、今回の本題であるテーブル内のデータのみ、つまり、タイトル行を除いた部分を取得するには「DataBodyRange」を使用します。

なので、マクロを作成するとこんな感じになります。下記のマクロはアクティブシートのテーブル内の「データ部分のみ」をG２セルを起点として「値のみ」貼り付けます。

Sub test1()

'■テーブルを番号で指定する場合

ActiveSheet.ListObjects(1).DataBodyRange.Copy

Range("g2").PasteSpecial xlPasteValues

Application.CutCopyMode = False

End Sub

このマクロを実行するとこうなります↓

上のマクロはテーブルを「ListObjects(1)」とすることでいわゆる「番号」で指定していますが、テーブルには「名前」がある（名前をつけることができる）ので、マクロで操作したいテーブルを「名前」で指定することができます。

例えば「テーブルデザイン」タブを選択してこちらのテーブル「作業日報」という名前をつけたとします↓

このようにテーブルに対して名前を付けた場合、さきほどのマクロは下記のような書き方をしても同じ結果になります。こちらのマクロはアクティブシートのテーブル内のタイトル行以外の「データ部分のみ」をG１０セルを起点として値のみ貼り付けます↓

Sub test2()

'■テーブルに「作業日報」という名前をつけた場合

ActiveSheet.ListObjects("作業日報").DataBodyRange.Copy

Range("g10").PasteSpecial xlPasteValues

Application.CutCopyMode = False

End Sub

マクロを実行するとこんな感じになります↓

シートに１つしかテーブルのデータがないのであれば番号によってテーブルを指定しても問題ないと思いますが、同じシートに2以上の複数のテーブルが存在する場合には「名前」を付けてテーブルを指定するほうがわかりやすいのかもしれません。

この辺は完全に使い方、好みの問題かと思いますが。

同一シート内にある、複数のテーブルからデータ部分のみ集計させるマクロ

ここまでの考え方を使って少しだけ応用してみます。

同一シート内にテーブルを１つ追加して合計2つにします。

で、新しく追加したテーブルの名前を「作業日報２」と定義しておきます。

G列～K列にかけてタイトル行を設置しておきます↓

この状態で青とオレンジの2つのテーブルからデータ部分のみをG列～K列に集計させてみます。

雑な感じのマクロですがこんな感じで作ってみました。↓

Sub test3()

'■複数のテーブルのデータを集計させる

'変数宣言

Dim lastrow As Long


'■処理実行部分

Range("g1").CurrentRegion.Offset(1, 0).ClearContents '前回集計データのクリア

lastrow = Cells(Rows.Count, 8).End(xlUp).Row  '最終行の取得


ActiveSheet.ListObjects("作業日報").DataBodyRange.Copy

Range("g" & lastrow + 1).PasteSpecial xlPasteValues

Application.CutCopyMode = False

'最終行を再取得して処理続行

lastrow = Cells(Rows.Count, 8).End(xlUp).Row

ActiveSheet.ListObjects("作業日報２").DataBodyRange.Copy

Range("g" & lastrow + 1).PasteSpecial xlPasteValues

Application.CutCopyMode = False


End Sub

このマクロを実行するとこんな感じになります↓

まとめ

Excelのテーブル形式で作成されたデータに関して、タイトル行を除いた「データ部分」のみを値のみ貼り付けするマクロを記事にしました。

使いかたはそこまで難しくないので、複数のシートにあるテーブル形式のデータを別シートに集計させたり等々応用をきかせることができるかと思います。

【本日の近況報告】

新しいガジェットとしてドローンを購入しようか検討中です。

今までドローン検定などの資格・免許をもっていないとドローンは操縦できないと思いこんでいたのですが、航空法の対象外になるドローン（トイドローンといわれるもの）であれば特に免許は必要ないとのことです。
買うなら当然カメラ付きのドローンを狙います。

【本日の１曲】

Bobby Ramone/Today One Love,Tomorrow The World