マクロについて
マクロについて
VBAは色々な事が自動でできます
<script async src="https://pagead2.googlesyndication.com/pagead/js/adsbygoogle.js?client=ca-pub-4649626006891634"
crossorigin="anonymous"></script>
google.com, pub-4649626006891634, DIRECT, f08c47fec0942fa0
別シートにコピーする:使用例10選
【エクセルマクロ】別シートにコピーする:使用例10選
エクセルマクロで、データをコピーして別シートに貼り付けるExcelVBAコードを記載しています。
条件書式、罫線、背景色も一緒に別シートへコピーします。
※すべて「Sheet1」を開いた状態でマクロを実行しています。
別シートにコピー
以下のマクロを実行すると、セルを別シートにコピーします。
マクロ実行後
アクティブシートのセル [B1] コピーして、「Sheet2」に貼り付けます。
別シートにコピー(指定範囲)
以下のマクロを実行すると、指定範囲を別シートにコピーします。
マクロ実行後
アクティブシートのセル範囲の [B1:D6] コピーして、「Sheet2」に貼り付けます。
別シートにコピー(Cells使用)
以下のマクロを実行すると、指定範囲を別シートにコピーします。
マクロ実行後
アクティブシートのセル範囲の [B1:D6] コピーして、「Sheet2」に貼り付けます。
別シートにコピー(シートを変数格納)
以下のマクロを実行すると、指定範囲を別シートにコピーします。
マクロ実行後
[Sht1] のセル範囲の [B1:D6] コピーして、[Sht2] に貼り付けます。
別シートにコピー(特定の列)
以下のマクロを実行すると、A列を別シートへコピーします。
マクロ実行後
アクティブシートのA列を「Sheet2」にコピーします。
別シートにコピー(特定の複数列)
以下のマクロを実行すると、A~C列を別シートへコピーします。
マクロ実行後
アクティブシートのA~C列を「Sheet2」にコピーします。
別シートにコピー(離れた特定複数列)
以下のマクロを実行すると、離れた列を別シートへコピーします。
マクロ実行後
アクティブシートのA列とG列を「Sheet2」にコピーします。
別シートにコピー(特定の行)
以下のマクロを実行すると、2行目を別シートへコピーします。
マクロ実行後
アクティブシートの2行目を「Sheet2」にコピーします。
別シートにコピー(特定の複数行)
以下のマクロを実行すると、2~4行目を別シートへコピーします。
マクロ実行後
アクティブシートの2~4行目を「Sheet2」にコピーします。
別シートにコピー(離れた特定複数行)
以下のマクロを実行すると、離れた行を別シートへコピーします。
マクロ実行後
アクティブシートの1行目と7行目を「Sheet2」にコピーします。
この記事がお役に立ちますと幸いです。
VBA一度に複数の条件判断を行う
一度に複数の条件判断を行う
条件は○か×かのようにどちらかだけを選択する場合だけではありません。例えば変数に格納された文字列を色々な値を比較する場合など複数の条件判断を行う場合もあります。このような時には1つのif文の中に複数の条件分岐を記述することが出来ます。
構文は下記の通りです。
If 条件式1 Then (条件式1がTrueの時に行う処理1) (条件式1がTrueの時に行う処理2) ElseIf 条件式2 Then (条件式2がTrueの時に行う処理1) (条件式2がTrueの時に行う処理2) ElseIf 条件式3 Then (条件式3がTrueの時に行う処理1) (条件式3がTrueの時に行う処理2) Else (どの条件式もFalseの時に行う処理1) (どの条件式もFalseの時に行う処理2) End If
複数の条件を記述する場合、最初の条件については「If」の後ろに記述、それ以降の条件については「ElseIf」の後ろに記述します。また最後に「Else」を記述した場合は全ての条件式で「False」になった場合の処理を記述します。条件式は何個書いても構いません。
複数の条件を記述する場合、上から順番に条件の確認が行われて行きます。
まず「条件式1」が確認され「True」であればその下に書かれた処理を実行します。実行後は「End If」の次の行に処理が移ります。
次に「条件式1」が「False」の場合には、次の条件式である「条件式2」が確認され「True」であればその下に書かれて処理を実行します。実行後は「End If」の次の行に処理が移ります。
次に「条件式1」と「条件式2」が共に「False」の場合には、次の条件式である「条件式3」が確認され「True」であればその下に書かれて処理を実行します。実行後は「End If」の次の行に処理が移ります。
最後に「条件式1」「条件式2」「条件式3」が全て「False」の場合にはその下に書かれて処理を実行します。
サンプルプログラム
では簡単なサンプルで試してみましょう。
Sub テスト() Dim x As String x = "大阪" If x = "東京" Then Range("A1").Value = "お住まいは東京です" ElseIf x = "大阪" Then Range("A1").Value = "お住まいは大阪です" ElseIf x = "名古屋" Then Range("A1").Value = "お住まいは名古屋です" Else Range("A1").Value = "お住まいは分かりません" End If End Sub
上記のマクロを実行すると次のようになります。
今回は文字列型の変数に格納された値を、色々な値と比較しています。結果として変数に格納されている値は「大阪」ですので「Range("A1").Value = "お住まいは大阪です"」という処理だけが実行されています。
VBA セルをコピーする
VBA セルをコピーする (Range.Copy)
はじめに
Excel VBA マクロでセルをコピーして貼り付ける方法を紹介します。
Range("B1").Copy または Cells(1, 2).Copy メソッドから、セル「B1」をコピーできます。
Range("B1").PasteSpecial メソッドから、セルに貼り付けできます。形式を選択して貼り付けと同じように、値や書式だけを貼り付けられます。
-
目次
- セルをコピーする
- セルの範囲をコピーする
- 別シートのセルをコピーする
セルをコピーする
Range の引数にセル名を指定すると、そのセルをコピーして貼り付けできます。
セル「A1」をコピーするには Range("A1").Copy を入力します。
セル「B1」に貼り付けるには Range("B1").PasteSpecial(xlPasteAll) を入力します。
Range("A1").Copy ' セルをコピー
Range("B1").PasteSpecial (xlPasteAll) ' 貼り付け
Application.CutCopyMode = False ' コピーモードを解除
Cells の引数にセルの行と列の番号を指定すると、そのセルをコピーして貼り付けできます。
行「2」、列「A」をコピーするには Cells(2, 1).Copy を入力します。
行「2」、列「B」に貼り付けるには Cells(2, 2).PasteSpecial(xlPasteAll) を入力します。
Cells(1, 1).Copy ' A1
Cells(1, 2).PasteSpecial (xlPasteAll) ' B1
Cells(2, 1).Copy ' A2
Cells(2, 2).PasteSpecial (xlPasteAll) ' B2
Application.CutCopyMode = False
PasteSpecial の引数に、貼り付ける形式を指定できます。
定数名 | 値 | 説明 |
xlPasteAll (既定) | -4104 | すべて |
xlPasteFormulas | -4123 | 数式 |
xlPasteValues | -4163 | 値 |
xlPasteFormats | -4122 | 書式 |
xlPasteComments | -4144 | コメントとメモ |
xlPasteValidation | 6 | 入力規則 |
xlPasteAllExceptBorders | 7 | 罫線を除くすべて |
xlPasteColumnWidths | 8 | 列幅 |
xlPasteFormulasAndNumberFormats | 11 | 数式と数値の書式 |
xlPasteValuesAndNumberFormats | 12 | 値と数値の書式 |
xlPasteAllUsingSourceTheme (Excel 2007 以降) |
13 | コピー元のテーマを使用してすべて貼り付け |
xlPasteAllMergingConditionalFormats (Excel 2010 以降) |
14 | すべての結合されている条件付き書式 |
Range("A1").Copy
Range("B1").PasteSpecial ' すべて
Range("B2").PasteSpecial (xlPasteFormulas) ' 数式を貼り付け
Range("B3").PasteSpecial (xlPasteValues) ' 値を貼り付け
Range("B4").PasteSpecial (xlPasteFormats) ' 書式を貼り付け
Application.CutCopyMode = False
Application.CutCopyMode に False を設定しているのは、Copy メソッドを実行すると、そのセルがコピーモードになるためです。これを解除しています。
クリップボードにコピーしない
Copy メソッドを実行するとクリップボードにもコピーされます。Copy の引数にセルを指定すると、クリップボードにコピーしないでそのセルに貼り付けできます。すべての形式が貼り付けられ、コピーモードにもなりません。
セル「A1」を「B2」にコピーするには Range("A1").Copy(Range("B2")) を入力します。
Call Range("A1").Copy(Range("B2")) ' A1 → B2 へ貼り付け
スポンサーリンク
セルの範囲をコピーする
セルの範囲「A1」~「B2」をコピーするには Range("A1:B2").Copy または Range("A1", "B2").Copy を入力します。
Range("A1:B2").Copy
Range("A1", "B2").Copy
Range("A3").PasteSpecial
' A1 → A3 に貼り付け
' A2 → A4 に貼り付け
' B1 → B3 に貼り付け
' B2 → B4 に貼り付け
Application.CutCopyMode = False
セルの範囲「A1」~「B2」を「A3」に貼り付けると、セルの範囲「A3」~「B4」に貼り付けられます。これは Excel の操作と同じです。
セル「A1」をコピーして、セルの範囲「A3」~「B4」に貼り付けるには Range("A3:B4").PasteSpecial を入力します。
Range("A1").Copy
Range("A3:B4").PasteSpecial
Range("A3", "B4").PasteSpecial
' A1 → A3 に貼り付け
' A1 → A4 に貼り付け
' A1 → B3 に貼り付け
' A1 → B4 に貼り付け
Application.CutCopyMode = False
行列を入れ替えて貼り付け
PasteSpecial の Transpose 引数に True を指定して、コピーしたセルの範囲の行と列を入れ替えて貼り付けできます。
Range("A1:B2").Copy
Call Range("A3").PasteSpecial(xlPasteAll, Transpose:=True)
' A1 → A3 に貼り付け
' A2 → B3 に貼り付け
' B1 → A4 に貼り付け
' B2 → B4 に貼り付け
Application.CutCopyMode = False
クリップボードにコピーしない
Call Range("A1:B2").Copy(Range("A3"))
' A1 → A3 に貼り付け
' A2 → A4 に貼り付け
' B1 → B3 に貼り付け
' B2 → B4 に貼り付け
Call Range("A1").Copy(Range("A3:B4"))
' A1 → A3 に貼り付け
' A1 → A4 に貼り付け
' A1 → B3 に貼り付け
' A1 → B4 に貼り付け
別シートのセルをコピーする
シートを指定するには Worksheets の引数にシート名またはインデックスを指定します。
Sheet1 を指定するには Worksheets("Sheet1") または Worksheets(1) を入力します。
' Sheet1 のセル「A1」をコピー
Worksheets("Sheet1").Range("A1").Copy
Worksheets("Sheet1").Range("B1").PasteSpecial
Worksheets(1).Range("A1").Copy
Worksheets(1).Range("B1").PasteSpecial
' Sheet2 のセル「A1」をコピー
Worksheets("Sheet2").Range("A1").Copy
Worksheets("Sheet2").Range("B1").PasteSpecial
Worksheets(2).Range("A1").Copy
Worksheets(2).Range("B1").PasteSpecial
Sheet1 に Range や Cells を入力すると、自身のシートのセルになります。自身を表す Me. が省略されています。
' どちらも同じコード
Range("A1").Value = "Sheet1"
Me.Range("A1").Value = "Sheet1"
標準モジュールに Range や Cells を入力すると、開いているシートのセルになります。アクティブシートを表す ActiveSheet. が省略されています。
' どちらも同じコード
Range("A1").Value = "アクティブ"
ActiveSheet.Range("A1").Value = "アクティブ"
ForとIfのネスト
ForとIfのネストこそがVBAの要点
VBA習得で最も肝心なものは、For文とIf文を確実に習得することです。
For文とIf文の基本をしっかりと覚え、
そして、
For文とIf文をネストさせるプログラミング技術を習得してください。
For~Nextステートメントは、繰り返し処理
If~End Ifステートメントは、条件分岐
つまり、
条件により分岐しつつ繰り返し処理を行う。
マクロVBAによる自動化の大部分が、For繰り返しとIf条件分岐によって記述されるという事です。
以下の表で解説していきます。
上表の、昨対比を計算し、昨対比が100%未満の場合は文字を赤色にします。
考え方を順に解説していきます。
第1に考える事・・・大外の繰り返しを作成
第1に考えることは、この縦のデータ処理の繰り返しになります。
Sub sample() Dim i As Long For i = 3 To 14 Next End Sub |
3行目から14行目まで処理する繰り返しだけを書きました。
細部の処理など気にせずに、とにかくこれだけ書いてしまいましよう。
Cells(Rows.Count, 1).End(xlUp).Row
このように、最初からかければより良いですが、
長い文があると。目がちらついて理解しづらい場合は、
このように、最初は固定数値で書いておいて、
全体が出来上がってから、
14
↓
Cells(Rows.Count, 1).End(xlUp).Row
と置き換えすれば良いのです。
第2に考える事・・・1支店だけを作成
思いますというより、実際にここで手が止まる人を多く拝見してきました。
そこで今回は、まずA支店だけの処理を考えてみましょう。
Cells(3, 4) = Cells(3, 3) / Cells(3, 2)
D3セルだけなら、これで良いですよね。
Cells(3,
この3が行数なのですから、
Cells(i,
このように書き直せば良いことが分かります。
Sub sample() Dim i As Long For i = 3 To 14 Cells(i, 4) = Cells(i, 3) / Cells(i, 2) Next End Sub |
これを実行すれば、A支店の昨対比だけが計算されます。
第3に考える事・・・昨対比の判定
先に、昨対比が100%未満の場合は文字を赤色にします。
つまり、
If文で1未満かを判定し、1未満の場合にフォントの色を赤色にします。
フォントの色を赤色にするVBAは、
セル.Font.Color = VbRed
Sub sample() Dim i As Long For i = 3 To 14 Cells(i, 4) = Cells(i, 3) / Cells(i, 2) If Cells(i, 4) < 1 Then Cells(i, 4).Font.Color = vbRed End If Next End Sub |
これを実行すれば、A支店の昨対比だけが計算され、
昨対100%未満が赤色になります。
第4に考える事・・・3支店の繰り返し
3支店(3回)の繰り返しは、
Dim c As Long For c = 1 To 3 Next |
このVBA自体は問題なく書けるでしょう。
問題は、実際の列数は、
A支店:2,3,4
B支店:5,6,7
C支店:8,9,10
となっていることです。
これを、2,5,8と変化させたいのです。
(c - 1) * 3 + 2
このような計算式で求まるのですが、ちょっと難しい感じがします。
少し詳しく解説すると、
(変数 - 1) * 1データの列数 + 最初の列位置
これが書ければ、もちろんこれで良いのですが、
今回は、少し違う方法で書いてみましょう。
変数を1~3ではなく、昨年の列位置の数値で変化させます。
Dim c As Long For c = 2 To 8 Step 3 Next |
これで、変数cは、
2,5,8
と変化することになります。
昨年は、c
今年は、c + 1
昨対比は、c + 2
という事になります。
第5に考える事・・・3支店の繰り返しを全体の中に組み込む
さて、どこに入れるかです。
Sub sample() Dim i As Long For i = 3 To 14 Cells(i, 4) = Cells(i, 3) / Cells(i, 2) If Cells(i, 4) < 1 Then Cells(i, 4).Font.Color = vbRed End If Next End Sub |
このVBAコードは、
・計算
・昨対比判定
これを、
・横に繰り返し
・計算
・昨対比判定
Sub sample() Dim i As Long Dim c As Long For i = 3 To 14 For c = 2 To 8 Step 3 Cells(i, 4) = Cells(i, 3) / Cells(i, 2) If Cells(i, 4) < 1 Then Cells(i, 4).Font.Color = vbRed End If Next Next End Sub |
ネストを追加した時は、必ずTabでインデントを正しくしておきましょう。
マクロVBAが正しく動かないという相談者の多くが、このインデントがぐちゃぐちゃになっています。
インデントが乱れていたのでは、思考が乱れてしまいます。
今年は、3 → c + 1
昨対比は、4 → c + 2
Sub sample() Dim i As Long Dim c As Long For i = 3 To 14 For c = 2 To 8 Step 3 Cells(i, c + 2) = Cells(i, c + 1) / Cells(i, c) If Cells(i, c + 2) < 1 Then Cells(i, c + 2).Font.Color = vbRed End If Next Next End Sub |
これで一応の完成です。
最後に考える事・・・最後の仕上げ
Sub sample() Dim i As Long Dim c As Long For i = 3 To Cells(Rows.Count, 1).End(xlUp).Row For c = 2 To 8 Step 3 Cells(i, c + 2) = Cells(i, c + 1) / Cells(i, c) If Cells(i, c + 2) < 1 Then Cells(i, c + 2).Font.Color = vbRed End If Next Next End Sub |
必要に応じて、
シート指定や、メッセージの表示を追加します。
Sub sample() Dim i As Long Dim c As Long With Worksheets("Sheet1") For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row For c = 2 To 8 Step 3 .Cells(i, c + 2) = .Cells(i, c + 1) / .Cells(i, c) If .Cells(i, c + 2) < 1 Then .Cells(i, c + 2).Font.Color = vbRed End If Next Next End With MsgBox "終了" End Sub |
Withを使う場合は、
Cellsの先頭に付ける.(ピリオド)の漏れに注意してください。
目指すべきVBAの書き順・・・VBAが上達したら
以下の手順でVBAを書けるように練習しましょう。
Sub sample() Dim i As Long Dim c As Long With Worksheets("Sheet1") End With End Sub |
Sub sample() Dim i As Long Dim c As Long With Worksheets("Sheet1") For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row Next End With End Sub |
Sub sample() Dim i As Long Dim c As Long With Worksheets("Sheet1") For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row For c = 2 To 8 Step 3 Next Next End With End Sub |
Sub sample() Dim i As Long Dim c As Long With Worksheets("Sheet1") For i = 3 To .Cells(.Rows.Count, 1).End(xlUp).Row For c = 2 To 8 Step 3 .Cells(i, c + 2) = .Cells(i, c + 1) / .Cells(i, c) If .Cells(i, c + 2) < 1 Then End If Next Next End With End Sub |
これは、あくまで理想論です。
今回の例題であれば、上から順に書くこともできますが、
もっと複雑な処理のVBAを書く場合、上から順に全てを書くことはほぼ無理な事です。
上に行ったり下に行ったりしながら、少しずつ書き足していくことでVBAを完成させます。
ForとIfのネストをスムーズに書けるようになれば、自在にマクロVBAを書き進められるようになります。
まずは、
ForとIfのネストの習得に注力してVBA学習を進めてみてください。
セルの移動(offset)
これだけはおさえるセル操作(1)-Offsetで自由自在
セル(範囲)に関する基本事項で、RangeとCellsをマスターしたら、Offsetの使い方をぜひおさえてください。
【基礎編】
Offsetは簡単にいえば、セル(範囲)の移動です。使い方は次のとおりです。
Rangeオブジェクト.Offset(行方向, 列方向)
例えば・・・
ActiveCell.Offset(1, 0).Select
このコードはアクティブセルから行方向に1、つまり1つ下のセルを選択します。
また、移動しない方向については省略可能ですから・・・
ActiveCell.Offset(1).Select
でも同じです。ただし、移動しない方向の0を省略すると可読性が下がるというデメリットがあるので、Offset(1)やOffset(, 1)と書くこともできることだけ覚えておき、省略しないで書く方がよいでしょう。
下方向へ1つ移動の指定がOffset(1, 0)であるならば、上方向へはどうするのでしょうか?
答えは素直に考えて、-(マイナス)表記にします。
ActiveCell.Offset(-1, 0).Select
今度はActiveCellをSelectionに置き換えてみましょう。
Selection.Offset(0, 2).Select
このコードは選択範囲を列方向に2、つまり右側へ2列選択範囲をずらします。
このとき、SelectionがセルA1からD5までのセル範囲をあらわしているとすれば、次のように書くことができます。
Range("A1:D5").Offset(0, 2).Select
⇒ 実行するとセルC1からF5までのセル範囲を選択します。
【応用編】
基礎編でOffset(行方向,列方向)の指定の仕方がわかったら、今度はRangeの基本的な使い方と組み合わせてみます。
Rangeは、Range("A1") や Range("A1:D5")のような使い方のほかに
Range(左上端, 右下端) ※左上端、右下端ともにセル、もしくはセル範囲
というように、引数として2つのセル、またはセル範囲を指定することでセル範囲をあらわすことができました。これをOffsetと組み合わせて使う方法を検討します。
次のコードはアクティブセルから右方向へ5列移動したセルまでのセル範囲を結合します。
Range(ActiveCell, ActiveCell.Offset(0, 5)).Merge
アクティブセルがA1のときは、セルA1からF1までのセルを結合します。
次のコードは選択範囲を1行下まで拡張します。
Range(Selection, Selection.Offset(1, 0)).Select
選択範囲がB5からE10までのセル範囲だったとすると、B5からE11までに拡張します。
例えばデータ入力シートに名前つきセル範囲があったとします。データ入力をするのですから当然範囲を拡張しなければならないケースが出てくるでしょう。
セル範囲につけられた名称が”データ”で、名前の範囲を5行拡張する場合・・・
Range(Range("データ"), Range("データ").Offset(5, 0)).Name = "データ"
となります。
ただし、Rangeオブジェクトにはセル範囲のサイズを変更するResizeプロパティが用意されているので、セル範囲の拡張はResizeプロパティで行うこともできます。
ここまでの知識を応用すれば、さまざまなことができますが、さらにRnageオブジェクトのプロパティやメソッドなどをマスターすることにより、より高度なことを行うことができるようになります。
例えば、特定の値をFindメソッドで検索し、検索されたセルの右隣へ指定の値を書き込む、などということも可能となるのです。
一度に複数の条件判断を行う VBA
一度に複数の条件判断を行う
条件は○か×かのようにどちらかだけを選択する場合だけではありません。例えば変数に格納された文字列を色々な値を比較する場合など複数の条件判断を行う場合もあります。このような時には1つのif文の中に複数の条件分岐を記述することが出来ます。
構文は下記の通りです。
If 条件式1 Then (条件式1がTrueの時に行う処理1) (条件式1がTrueの時に行う処理2) ElseIf 条件式2 Then (条件式2がTrueの時に行う処理1) (条件式2がTrueの時に行う処理2) ElseIf 条件式3 Then (条件式3がTrueの時に行う処理1) (条件式3がTrueの時に行う処理2) Else (どの条件式もFalseの時に行う処理1) (どの条件式もFalseの時に行う処理2) End If
複数の条件を記述する場合、最初の条件については「If」の後ろに記述、それ以降の条件については「ElseIf」の後ろに記述します。また最後に「Else」を記述した場合は全ての条件式で「False」になった場合の処理を記述します。条件式は何個書いても構いません。
複数の条件を記述する場合、上から順番に条件の確認が行われて行きます。
まず「条件式1」が確認され「True」であればその下に書かれた処理を実行します。実行後は「End If」の次の行に処理が移ります。
次に「条件式1」が「False」の場合には、次の条件式である「条件式2」が確認され「True」であればその下に書かれて処理を実行します。実行後は「End If」の次の行に処理が移ります。
次に「条件式1」と「条件式2」が共に「False」の場合には、次の条件式である「条件式3」が確認され「True」であればその下に書かれて処理を実行します。実行後は「End If」の次の行に処理が移ります。
最後に「条件式1」「条件式2」「条件式3」が全て「False」の場合にはその下に書かれて処理を実行します。
サンプルプログラム
では簡単なサンプルで試してみましょう。
Sub テスト() Dim x As String x = "大阪" If x = "東京" Then Range("A1").Value = "お住まいは東京です" ElseIf x = "大阪" Then Range("A1").Value = "お住まいは大阪です" ElseIf x = "名古屋" Then Range("A1").Value = "お住まいは名古屋です" Else Range("A1").Value = "お住まいは分かりません" End If End Sub
上記のマクロを実行すると次のようになります。
今回は文字列型の変数に格納された値を、色々な値と比較しています。結果として変数に格納されている値は「大阪」ですので「Range("A1").Value = "お住まいは大阪です"」という処理だけが実行されています。