－ちょっと使えるネタを紹介－
Excel VBAで、別ブックや別シートに、条件別で転記する方法について、ご紹介します。別シートや別ブックに転記するには、「Worksheets」や「Workbooks」を使います。条件別で、転記する際には、オートフィルタを使ったり、重複しないリストの作成をすると便利です。
この記事では、条件別で、別シートや別ブックに転記する方法について、ご紹介します。
別シートに転記する場合は、「Worksheets(シート名)」を使います。
別ブックに転記する場合は、「Workbooks(ブック名)」を使うとできます。
オートフィルタや、重複しないリストを作成すると、うまく条件別で、転記することができます。
では、条件別で、別シートや別ブックに転記する方法について、解説していきます。
この記事で紹介すること
目次から見たい項目へ移動すると便利ですよ。
目次
VBAで、別シートに転記する方法について、ご紹介します。
セルの値を、別シートに転記する場合は、「Worksheets(シート名)」を使うとできます。
「Sheet1」の値を、「Sheet2」に転記するVBAコードです。
Sub TEST1()
'別シートに転記
Worksheets("Sheet1").Range("A1:C1").Copy Worksheets("Sheet2").Range("A1")
End Sub
「Sheet1」に次のように値を入力しました。
すでに、「Sheet2」はあることとしています。
では、VBAコードを実行してみます。
「Sheet2」に値を、転記できました。
次は、シートを追加して、追加したシートに値を転記してみます。
シートの追加は、「Worksheets.Add」を使うとできます。
シートを追加して、追加したシートに値を転記するVBAコードです。
Sub TEST2()
'別シートを追加
Worksheets.Add after:=ActiveSheet
'別シートに転記
Worksheets("Sheet1").Range("A1:C1").Copy ActiveSheet.Range("A1")
End Sub
手順は、
という流れです。
「Sheet1」に値を入力しました。
では、VBAコードを実行します。
まず、シートを追加すると、「Sheet2」が作成されます。
追加したシートに、「Sheet1」の値が転記されます。
追加したシートに、値が転記されました。
こんな感じで、シートを追加して、値を転記する場合は、「Worksheets.Add」を使います。
次は、条件を指定して別シートに転記してみます。
次の表を用意しました。
この表で、支店が「東京」の値だけを、別シートに転記してみます。
VBAコードは、こんな感じになります。
Sub TEST3()
'支店を東京でフィルタ
Worksheets("Sheet1").Range("A1").AutoFilter 2, "東京"
'シートを追加
Worksheets.Add after:=ActiveSheet
ActiveSheet.Name = "東京" 'シート名を変更
'フィルタしたデータを、別シートに転記
Worksheets("Sheet1").Range("A1").CurrentRegion.Copy ActiveSheet.Range("A1")
'フィルタを解除
Worksheets("Sheet1").Range("A1").AutoFilter
End Sub
手順は、
という流れです。
では、VBAコードを実行します。
まず、支店が「東京」でフィルタされます。
次に、シートが追加されます。
最後に、フィルタしたデータが、追加したシートに貼り付けられます。
条件を指定して、別シートにデータを転記できました。
こんな感じで、条件を指定する場合は、オートフィルタを使うと便利です。
次は、条件別で、複数の別シートに転記する、というのをやってみます。
先ほどと同じ表を使います。
支店は、「東京」、「大阪」、「名古屋」、「福岡」の4つがあります。
この表の支店ごとに、別シートに転記します。
VBAコードは、こんな感じになります。
Sub TEST4()
'配列を作成
Dim A
A = Array("東京", "大阪", "名古屋", "福岡")
For i = 0 To 3
'支店でフィルタ
Worksheets("Sheet1").Range("A1").AutoFilter 2, A(i)
'シートを追加
Worksheets.Add after:=ActiveSheet
ActiveSheet.Name = A(i) 'シート名を変更
'フィルタしたデータを、別シートに転記
Worksheets("Sheet1").Range("A1").CurrentRegion.Copy ActiveSheet.Range("A1")
'フィルタを解除
Worksheets("Sheet1").Range("A1").AutoFilter
Next
End Sub
手順は、
という流れです。
では、VBAコードを実行します。
まず、「支店」のリストを作成します。
「Array」を使って、「東京」、「大阪」、「名古屋」、「福岡」を配列に入力します。
作成した「支店」リストの「東京」でフィルタされます。
シートが追加されて、フィルタしたデータが転記されます。
作成した「支店」リストの「東京」でフィルタされたデータが、別シートに転記されます。
同じ手順が、「大阪」、「名古屋」、「福岡」で繰り返されます。
では、実行した結果をみてみます。
結果は、次のような感じで、条件別で別シートに転記されます。
条件ごとに、別シートに転記したい場合は、こんな感じでできます。
条件別に、別シートに転記したい場合は、「For～Next」と「オートフィルタ」を使いこなすと、便利です。
次は、「重複しないリスト」を作成して、条件別で複数シートに転記する、というのをやってみます。
先ほど作成した「支店」リストを、元の表から作成します。
先ほどと同じ表を使います。
「東京」、「大阪」、「名古屋」、「福岡」の4つを、元の表から作成して、条件別にシートに転記していきます。
VBAコードは、こんな感じになります。
Sub TEST5()
Dim A
'「支店」で重複しないリストにフィルタする
Set A = Worksheets("Sheet1").Range("A1").CurrentRegion.Columns(2)
A.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'重複しないリストを別セルにコピー
A.Copy Worksheets("Sheet1").Range("E1")
Worksheets("Sheet1").ShowAllData 'フィルタ解除
'重複しないリストを、配列に格納
Dim Data
With Worksheets("Sheet1")
Data = .Range(.Range("E2"), .Range("E2").End(xlDown))
End With
'重複しないリストをクリア
Worksheets("Sheet1").Range("E1").CurrentRegion.Clear
'重複しないリスト分ループ
For i = 1 To UBound(Data, 1)
'支店を、フィルタする
Worksheets("Sheet1").Range("A1").AutoFilter 2, Data(i, 1)
'別シートを追加
Worksheets.Add after:=Worksheets(Worksheets.Count)
ActiveSheet.Name = Data(i, 1) '名前を変更
'フィルタしデータを、追加したシートに転記
Worksheets("Sheet1").Range("A1").CurrentRegion.Copy ActiveSheet.Range("A1")
'フィルタ解除
Worksheets("Sheet1").Range("A1").AutoFilter
Next
End Sub
手順は、
という流れです。
重複しないリストの作成については、こちらで詳細を解説しています。
では、VBAコードを実行します。
まず、重複しないリストがフィルタされます。
次に、重複しないリストが別セルに入力されます。
作成した重複しないリストの「東京」でフィルタされます。
シートが追加されて、フィルタしたデータが転記されます。
作成した重複しないリストの「東京」でフィルタされたデータが、別シートに転記されます。
同じ工程が、「大阪」、「名古屋」、「福岡」で繰り返されます。
では、実行した結果をみてみます。
結果は、次のような感じで、条件別で別シートに転記されます。
条件を元の表から作成して、条件別にシートに転記したい場合は、こんな感じでできます。
条件を作成して、条件別に別シートに転記したい場合は、「オートフィルタ」と「重複しないリストの作成」を使いこなすと、便利です。
次は、別ブックに値を転記する方法について、ご紹介します。
別ブックに値を転記する場合は、「Workbooks(ブック名)」を使います。
別ブックに値を転記するVBAコードは、こんな感じになります。
Sub TEST6()
'別ブックを開く
Workbooks.Open Filename:=ThisWorkbook.Path & "\別ブック.xlsx"
Dim A, B
'別ブックに転記
Set A = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
Set B = Workbooks("別ブック.xlsx").Worksheets("Sheet1").Range("A1")
A.Copy B
'別ブックを保存して、閉じる
Workbooks("別ブック.xlsx").Save
Workbooks("別ブック.xlsx").Close
End Sub
手順は、
という流れです。
次のブックを用意しました。
同じフォルダ内に、「別ブック.xlsx」という空のブックを保存しています。
では、VBAコードを実行します。
まず、「別ブック.xlsx」が開かれます。
次に、値が転記されます。
「別ブック.xlsx」という別ブックに値を転記されます。
最後に別ブックを保存して、閉じます。
では、結果をみてみます。
結果として、「別ブック.xlsx」に値を転記することができます。
「別ブック.xlsx」を開いてみます。
こんな感じで、すでにある別ブックに値を転記する場合は、別ブックを開いて転記します。
次は、新規ブックに値を転記するというのをやってみます。
新規ブックを作成するには、「Workbooks.Add」を使います。
新規ブックに値を転記するVBAコードです。
Sub TEST7()
'別ブックを追加
Workbooks.Add
Dim A, B
'新規ブックに、転記
Set A = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
Set B = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
A.Copy B
'新規ブックを保存して、閉じる
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\別ブック.xlsx"
ActiveWorkbook.Close
End Sub
手順は、
という流れです。
先ほど使った、ブックを使います。
では、VBAコードを実行します。
まず、新規ブックが作成されます。
次に、新規ブックに値が転記されます。
名前を付けて保存したあとに、閉じられます。
では、結果を見てみます。
結果として、「別ブック.xlsx」というブックが作成されて、値が転記されます。
「別ブック.xlsx」を開いてみます。
新規ブックに、値が転記されました。
こんな感じで、新規ブックに値を転記する場合は、「Workbooks.Add」で新規ブックを作成した後に、転記します。
次に、条件別で、別ブックに転記する、というのをやってみます。
次の表を使います。
この表の支店で、「大阪」の値だけを、別ブックに転記してみます。
VBAコードは、次のようになります。
Sub TEST8()
'「大阪」でフィルタ
Worksheets("Sheet1").Range("A1").AutoFilter 2, "大阪"
'新規ブックを追加
Workbooks.Add
Dim A, B
'新規ブックに、転記
Set A = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
Set B = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
A.Copy B
'新規ブックを、保存して閉じる
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\大阪.xlsx"
ActiveWorkbook.Close
'元ブックのフィルタを解除
Worksheets("Sheet1").Range("A1").AutoFilter
End Sub
手順としては、
という流れです。
では、VBAコードを実行してみます。
まず、支店が「大阪」でフィルタでフィルタされます。
次に、新規ブックが追加されます。
次は、新規ブックにフィルタしたデータが転記されます。
そして、名前を使えて保存したあと、新規ブックが閉じられます。
では、結果を見てみます。
結果として、「別ブック.xlsx」というブックに「大阪」のデータが転記されます。
「別ブック.xlsx」に支店が「大阪」だけを転記することができました。
次は、条件別で、複数ブックに転記する方法を、ご紹介します。
次の表を、支店別で、別ブックに転記してみます。
こちらの表で、それぞれ「東京」、「大阪」、「名古屋」、「福岡」の値をそれぞれ、別ブックに転記します。
VBAコードは、こちらです。
Sub TEST9()
Dim A
A = Array("東京", "大阪", "名古屋", "福岡")
For i = 0 To 3
'支店でフィルタ
Worksheets("Sheet1").Range("A1").AutoFilter 2, A(i)
'新規ブックを追加
Workbooks.Add
Dim B, C
'新規ブックに、転記
Set B = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
Set C = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
B.Copy C
'新規ブックを、保存して閉じる
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & A(i) & ".xlsx"
ActiveWorkbook.Close
'元ブックのフィルタを解除
Worksheets("Sheet1").Range("A1").AutoFilter
Next
End Sub
手順は、
という流れです。
では、実行してみます。
「支店」のリストを作成して、変数に入力します。
次に、重複しないリストの1番目のデータである、「東京」でフィルタされます。
新規ブックが追加されます。
フィルタしたデータが、この新規ブックに転記されます。
新規ブックを名前を付けて保存して、閉じます
これらの手順が、それぞれの「支店」で繰り返されます。
それぞれのブックは、次のように値が入力されています。
こんな感じで、条件別で、別ブックに転記することができます。
次は、「重複しないリスト」を作成して、条件別で、複数ブックに転記する、というのをやってみます。
次の表から、「支店」で「重複しないリスト」を作成して、別ブックに転記してみます。
こちらの表で、それぞれ「東京」、「大阪」、「名古屋」、「福岡」だけの値を別ブックに転記します。
VBAコードは、こちらです。
Sub TEST10()
Dim A
'「支店」で重複しないリストをフィルタ
Set A = Worksheets("Sheet1").Range("A1").CurrentRegion.Columns(2)
A.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
'重複しないリストを別セルに入力
A.Copy Worksheets("Sheet1").Range("E1")
'フィルタを解除
Worksheets("Sheet1").ShowAllData
'重複しないリストを、配列に格納
Dim Data
With Worksheets("Sheet1")
Data = .Range(.Range("E2"), .Range("E2").End(xlDown))
End With
'重複しないリストを削除
Worksheets("Sheet1").Range("E1").CurrentRegion.Clear
'重複しないリストの支店の値で、ループ
For i = 1 To UBound(Data, 1)
'重複しないリストの値で、フィルタ
Worksheets("Sheet1").Range("A1").AutoFilter 2, Data(i, 1)
'新規ブックを追加
Workbooks.Add
Dim B, C
'新規ブックに、フィルタしたデータを転記
Set B = ThisWorkbook.Worksheets("Sheet1").Range("A1").CurrentRegion
Set C = ActiveWorkbook.Worksheets("Sheet1").Range("A1")
B.Copy C
'新規ブックを保存して、閉じる
ActiveWorkbook.SaveAs ThisWorkbook.Path & "\" & Data(i, 1) & ".xlsx"
ActiveWorkbook.Close
'オートフィルタを解除
Worksheets("Sheet1").Range("A1").AutoFilter
Next
End Sub
手順は、
という流れです。
重複しないリストの作成については、こちらで詳細を解説しています。
では、実行してみます。
「支店」で、重複しないリストがフィルタされます。
フィルタした重複しないリストが保存されます。
次に、重複しないリストの1番目のデータである、「東京」でフィルタされます。
新規ブックが追加されます。
フィルタしたデータが、この新規ブックに転記されます。
新規ブックを名前を付けて保存して、閉じます
これらの手順が、それぞれの「支店」で繰り返されます。
それぞれのブックは、次のように値が入力されています。
こんな感じで、元の表から条件を作成して、条件別で別ブックに転記することができます。
「重複しないリスト」を作成できると、条件を入力する必要がないので、便利です。
この記事では、条件別で、別シートや別ブックに転記する方法について、ご紹介しました。
別シートに転記する場合は、「Worksheets(シート名)」を使って、別ブックに転記する場合は、「Workbooks(ブック名)」を使います。
オートフィルタや、重複しないリストを作成すると、うまく条件別で、転記することができます。
参考になればと思います。最後までご覧くださいまして、ありがとうございました。
関連する記事から探す
カテゴリから見たい項目を探すと便利ですよ。
↓キーワードを入力する