Level5 VBA|クエスト9

元データのExcelからデータ抽出したい!
これまでの記事では、Rangeによるセル指定や、ブック・ワークシートを変数へ代入する方法を学びました。
これらを組み合わせると、あるセルの値を別のシートやブックへ転記できます。
転記先シート.Range("A1").Value = 転記元シート.Range("A1").Value
さらに、For Nextと最終行の取得を組み合わせれば、データ件数が増減しても、最後のデータ行までまとめて転記できます。
この記事では、別シートや別ブックへ値だけを転記する方法と、最終行まで複数行を転記する方法を解説します。
転記元と転記先のブック・ワークシート・セルを区別し、最後のデータ行まで値だけを転記できるようになりましょう。
VBAの転記とはデータを別の場所へ移すこと
この記事における転記とは、転記元のセルに入っている値を、別のセルへ入力する処理です。
たとえば、A1セルの値をD1セルへ転記するコードは、次のとおりです。
Range("D1").Value = Range("A1").Value
このコードでは、A1セルの値を取得し、D1セルへ入力します。
Range("D1").Value:転記先Range("A1").Value:転記元
コードは次の順番で記述します。
転記先 = 転記元
日本語では「A1セルからD1セルへ転記する」と読みますが、コードでは転記先を左側、転記元を右側に記述します。
代入に利用される「=」は「←」と考えましょう。
右から左へと値を入れているイメージです。
別シートへ1つのセルを転記する
同じブック内の別シートへ転記するときは、転記先と転記元のワークシートをそれぞれ指定します。
次のコードでは、「売上表」シートのA2セルから、「集計表」シートのA2セルへ値を転記しています。
Worksheets("集計表").Range("A2").Value = _
Worksheets("売上表").Range("A2").Value
左側から順番に確認しましょう。
Worksheets("集計表").Range("A2").Value:転記先Worksheets("売上表").Range("A2").Value:転記元
つまり、コード全体では次のように読みます。
「売上表」シートのA2セルにある値を、「集計表」シートのA2セルへ転記する。
_を記述1行のコードが長くなる場合は、行末に半角スペースと_を記述すると、次の行へ続けられます。
この記号を行継続文字と呼びます。
_の前に半角スペースが必要なので、削除しないようにしましょう。
ワークシートを変数へ代入して転記する
ワークシート名を繰り返し記述すると、コードが長くなります。
そこで、転記元と転記先のワークシートを変数へ代入します。
Dim 転記元シート As Worksheet
Dim 転記先シート As Worksheet
Set 転記元シート = ThisWorkbook.Worksheets("売上表")
Set 転記先シート = ThisWorkbook.Worksheets("集計表")
代入後は、変数名を使って転記できます。
転記先シート.Range("A2").Value = _
転記元シート.Range("A2").Value
変数名から、それぞれの役割を判断できます。
転記元シート:値を取得するシート転記先シート:値を入力するシート
同じブック内で別シートへ転記する完成コードは、次のとおりです。
Sub 別シートへ転記する()
Dim 転記元シート As Worksheet
Dim 転記先シート As Worksheet
Set 転記元シート = ThisWorkbook.Worksheets("売上表")
Set 転記先シート = ThisWorkbook.Worksheets("集計表")
転記先シート.Range("A2").Value = _
転記元シート.Range("A2").Value
End Sub
このコードでは、マクロが保存されているブック内のシート同士で値を転記しています。
セル範囲をまとめて転記する
1つのセルだけでなく、複数のセルをまとめて転記することもできます。
転記先シート.Range("A2:C10").Value = _
転記元シート.Range("A2:C10").Value
このコードでは、転記元シートのA2セルからC10セルまでの値を、転記先シートの同じ範囲へ転記します。
転記元と転記先には、基本的に同じ大きさのセル範囲を指定します。
正しい組み合わせの例は、次のとおりです。
転記先シート.Range("D2:F10").Value = _
転記元シート.Range("A2:C10").Value
転記元と転記先のセル番地は異なりますが、どちらも3列、9行の範囲です。
セル範囲をまとめて転記するときは、転記元と転記先の行数・列数をそろえましょう。範囲の大きさが異なると、意図した結果にならない場合があります。
Valueを使うと値だけを転記できる
この記事では、転記元と転記先のValueを=でつないでいます。
転記先シート.Range("A2").Value = _
転記元シート.Range("A2").Value
この方法では、セルに表示されている値を転記できます。
主に転記される内容は、次のとおりです。
- 文字列
- 数値
- 日付
- 数式の計算結果
一方、次のような書式は基本的に転記されません。
- 背景色
- 文字色
- 罫線
- 表示形式
- 列幅や行の高さ
転記元に数式が入力されている場合、Valueで転記されるのは、基本的に数式そのものではなく計算結果です。
たとえば、A2セルに=B2+C2という数式があり、計算結果が100なら、転記先には100が入力されます。
書式や数式も含めてコピーする場合はCopyなどを使用します。
別ブックへ値を転記する
別ブックへ転記するときは、転記元と転記先のブックをそれぞれ指定します。
今回は、マクロが保存されているブックを転記元にします。
Dim 転記元ブック As Workbook
Dim 転記先ブック As Workbook
Set 転記元ブック = ThisWorkbook
Set 転記先ブック = Workbooks.Open("C:\売上管理\転記先.xlsx")
転記元ブック:VBAコードが保存されているブック転記先ブック:指定したフルパスから開いたブック
続いて、それぞれのワークシートを変数へ代入します。
Dim 転記元シート As Worksheet
Dim 転記先シート As Worksheet
Set 転記元シート = 転記元ブック.Worksheets("売上表")
Set 転記先シート = 転記先ブック.Worksheets("集計表")
これで、転記元と転記先のブック・ワークシートを区別できます。
1つのセルを別ブックへ転記するコードは、次のとおりです。
転記先シート.Range("A2").Value = _
転記元シート.Range("A2").Value
転記コード自体は、別シートへ転記したときと同じです。
違うのは、転記元シートと転記先シートが、それぞれ別のブックに所属している点です。
別ブックへ転記して保存する
別ブックへ1つのセルを転記し、保存して閉じる完成コードを見てみましょう。
Sub 別ブックへ1セル転記する()
Dim 転記元ブック As Workbook
Dim 転記先ブック As Workbook
Dim 転記元シート As Worksheet
Dim 転記先シート As Worksheet
Set 転記元ブック = ThisWorkbook
Set 転記先ブック = Workbooks.Open("C:\売上管理\転記先.xlsx")
Set 転記元シート = 転記元ブック.Worksheets("売上表")
Set 転記先シート = 転記先ブック.Worksheets("集計表")
転記先シート.Range("A2").Value = _
転記元シート.Range("A2").Value
転記先ブック.Save
転記先ブック.Close
End Sub
このマクロでは、次の順番で処理します。
- ブックとワークシートを保存する変数を用意する
- マクロが保存されているブックを転記元にする
- フルパスを指定して転記先ブックを開く
- 転記元と転記先のワークシートを指定する
- 転記元のA2セルから転記先のA2セルへ値を転記する
- 転記先ブックを保存する
- 転記先ブックを閉じる
転記処理が終わる前にブックを閉じないよう、SaveとCloseは転記コードの後ろに記述します。
最終行まで複数行を転記する
データ件数が変わる表では、転記元シートの最終行を取得します。
Dim 最終行 As Long
最終行 = 転記元シート.Range("A" & _
転記元シート.Rows.Count).End(xlUp).Row
このコードでは、転記元シートのA列を基準に、最後に値が入力されている行番号を取得しています。
A列以外を基準にする場合は、"A"を目的の列記号へ変更します。
最終行を取得するコードの仕組みや、基準列の選び方は、「Excel VBAのFor Nextの使い方|最終行まで繰り返し処理する方法」で詳しく解説しています。
取得した最終行をFor Nextの終了値へ指定します。
For 行番号 = 2 To 最終行
転記先シート.Range("A" & 行番号).Value = _
転記元シート.Range("A" & 行番号).Value
Next 行番号
このコードでは、2行目から最終行まで、A列の値を同じ行番号へ転記します。
最終行が10なら、次の範囲が処理されます。
- 転記元:A2セルからA10セル
- 転記先:A2セルからA10セル
また同じ処理として、繰り返しではなく範囲をまとめて転記する方法もあります。
転記先シート.Range("A2:C" & 最終行).Value = _
転記元シート.Range("A2:C" & 最終行).Value
各行で条件分岐などさせていなければ、これで十分です。
複数列を最終行まで転記する
各行のA列からC列までを転記する場合は、同じ行にあるセル範囲を指定します。
For 行番号 = 2 To 最終行
転記先シート.Range("A" & 行番号 & ":C" & 行番号).Value = _
転記元シート.Range("A" & 行番号 & ":C" & 行番号).Value
Next 行番号
行番号が2の場合は、次の範囲を転記します。
転記先シート.Range("A2:C2").Value = _
転記元シート.Range("A2:C2").Value
行番号が3へ進むと、A3セルからC3セルまでを転記します。
この処理を、取得した最終行まで繰り返します。
最終行まで別ブックへ転記する完成例
ここまでの内容を組み合わせてみましょう。
Sub 条件に一致する行を別ブックへ転記する()
Dim 転記元ブック As Workbook
Dim 転記先ブック As Workbook
Dim 転記元シート As Worksheet
Dim 転記先シート As Worksheet
Dim 行番号 As Long
Dim 最終行 As Long
Set 転記元ブック = ThisWorkbook
Set 転記先ブック = Workbooks.Open("C:\売上管理\転記先.xlsx")
Set 転記元シート = 転記元ブック.Worksheets("売上表")
Set 転記先シート = 転記先ブック.Worksheets("集計表")
最終行 = 転記元シート.Range("A" & _
転記元シート.Rows.Count).End(xlUp).Row
For 行番号 = 2 To 最終行
If 転記元シート.Range("D" & 行番号).Value = "対象" Then
転記先シート.Range("A" & 行番号 & ":C" & 行番号).Value = _
転記元シート.Range("A" & 行番号 & ":C" & 行番号).Value
End If
Next 行番号
転記先ブック.Save
転記先ブック.Close
End Sub
このマクロでは、転記元シートのA列を基準に最終行を取得し、2行目から最終行まで処理を繰り返します。
繰り返しの中では、転記元シートのD列に「対象」と入力されているかをIf文で判定します。
条件を満たした場合だけ、同じ行にあるA列からC列までの値を転記先シートへ転記します。
最後に、転記先ブックを上書き保存してから閉じます。
転記が完了してから保存をし、最後に閉じる処理をしましょう。
順番が前後するとエラーが発生します。詳細はこちらをご覧ください。
まとめ|転記先と転記元を区別して値を転記しよう
ブック間やシート間で転記をする場合、ブックとシートを指定して=で結びましょう。
- 転記コードは
転記先.Value = 転記元.Valueの順番 - =は←と考える
- 別シートへ転記するときは、両方のワークシートを指定する
- 別ブックへ転記するときは、ブックとシートを変数で区別する
Valueを使うと値だけを転記できる- セル範囲をまとめて転記するときは、範囲の大きさをそろえる
- 最終行は転記元シートを基準に取得する
For Nextで2行目から最終行まで転記できる- 転記後に転記先ブックを保存して閉じる
次のクエストへ
今回は、転記元と転記先のブック・ワークシートを変数で区別し、別ブックへ値を転記する方法を学びました。
次のクエストでは、VBAで保存・印刷・PDF変換を自動化しましょう。
Level5の学習順序を確認したい方は、Level5攻略フローをご覧ください。






コメント