他のブックにデータを自動転記する − GoGoマクロのWebサイト

HOME > レッスン > データ転記


今回は、他のブックへデータを自動で転記するマクロの作り方の手順を詳しく解説します。 Excelに入力する同じデータを1回で済ませる(重複入力作業の防止)などの効率化になります。 特に「他のブックへ転記する」という所に重点を置いて話を進めていきます。


◆「こんなマクロが作りたい!」リクエスト紹介

投稿者:かずさん

> 営業職をしています。
> 受注金額等の他のブックへの2重入力をなんとかしたいです。
> 予算表のブックと経理提出用のブックなど



今回のリクエストのような2重入力の問題というのは、どんな会社においても 多々あることだろうと思いますが、本来的には同じデータは一元管理をする、 というのが理想的な姿であるわけです。



0)
では、今回はまずその前準備として転記先のブックを予め作成する
ところから始めます。
──────────────+
1.エクセルを起動させます。(空のエクセルを立ち上げてください)

2.まずは、シート[Sheet2]を開いてください。
(※シートが1つしかない場合は新規にシート作成し、
 必ず[Sheet2]を開いた状態で次へ進んでください。)

3.次に、B1のセルに「 ↓転記場所 」との文字を入力してください。

4.次に、一旦いま開いている[Sheet2]のA1のセルを選択して
 から、[Sheet1]の方を開き直してください。

5.そうしたら、このブックに名前を付けて保存をしてこのファイル
 を閉じます。

 ※保存する名前は「data.xlsx」として(このファイルはマクロを
 含みませんので「マクロ有効ブック」である必要はありません。)
 適当なフォルダにファイル保存してください。(以降はデフォルト
 のフォルダにて解説していきます。)
──────────────+




1)
それでは、いつものようにマクロの記録を使ってその基となる
プログラムを作っていきます。
──────────────+
1.エクセルを起動させます。(再び空のエクセルを立ち上げてください)

2.ここでマクロの記録を開始します。
 [開発]タブの[コード]で [マクロの記録] をクリック →
「マクロの記録」画面が出るので、そのまま[OK]をクリック。

3.まず、いま選択中のセルに(セル移動はせずに)、111 と打って
 ください。

4.打ち終わったら一度[Enter]キーを押して(入力モードを解除して)、
 次に、先ほど作成しておいたデータのブックを以下の操作にて
 開きます。

 [ファイル]タブの [開く] →「ファイルを開く」画面で、
 先ほど保存したブック「data.xlsx」を開く。

(※ここではWindowsのフォルダからダブルクリックしてファイル
  を開くのではなく、上記の様に必ずExcelの[ファイル]タブから
  開いてください。)

5.次に、シート[Sheet2]を開きます。

6.B2のセルを選択し、222 と打ってください。

7.打ち終わったら一度[Enter]キーを押して入力モードを解除して、
 [開発]タブの[コード]で [■記録終了] をクリックして、
 マクロの記録を終了させます。

8.最後に、いま開いたブック「data.xlsx」を保存はせずに
 閉じます。
 [ファイル]タブの[閉じる] →「dataへの変更を保存しますか?」
 →「いいえ」を選択する。
──────────────+




2)
では、いま作ったプログラムの中身をのぞいてみます。
──────────────+
1.まず、いつもの様にプログラム用の画面を表示させます。
 [開発]タブの[コード]で [Visual Basic]をクリック。

2.この画面の左上半分の[+標準モジュール]という所の+の部分を
 クリックすると、そのすぐ下に[Module1]と表示されるので、
 その[Module1]をダブルクリックします。
──────────────+


++++++++++++─
Sub Macro1()
'
  ActiveCell.FormulaR1C1 = "111"
  Range("A2").Select
  Workbooks.Open Filename:= _
    "C:\Documents and Settings\My Computer\data.xlsx"
  Sheets("Sheet2").Select
  Range("B2").Select
  ActiveCell.FormulaR1C1 = "222"
  Range("B3").Select
End Sub
++++++++++++─

※上記プログラムの4行目「 "C:\Documents and Settings ・・・」の部分は、 個々のPC環境やファイル保存先によって異なります。


ここのファイル名は通常、フルパス名で指定します。ファイル名が確かに合っ ているにも関わらず「ファイル名が見つかりません。」とのエラーが出る場合 がよくありますが、それはそのファイルがある場所のフォルダ名(パス指定) の問題になるわけです。

目的のブックがカレントの(現在の)フォルダにある場合には、ここは短く、 (以下のようにパス名は外して)ファイル名だけを指定すればよいのですが、


"C:\Documents and Settings\My Computer\data.xlsx"
 ↓
"data.xlsx"


カレントフォルダ(現在の位置)というのは、ファイルの保存場所を変えたり するとその都度変わっていく(移動する)ものですので、固定の場所ではあり ません。

なので、マクロを実行した時点に何処がカレントフォルダなのかは基本 予測 は不能なものなので、フルパス名にて指定する必要があります。(マクロの 記録を使って作ればどんなに長いフルパス名でも間違うことはありませんので、 大変に便利ですね。)


上記のプログラムは、現在選択中のセルからデータを取得して、 その転記先( ブック → シート → セル )を指定し、書き込む先に目印(222 という数字)をつけた。その操作を、いつものようにマクロの記録でマクロを 自動作成したというものになります。

「マクロの記録」を使えば、ここまでプログラムは1文字も書く必要がありま せんので、初心者にはとってもやさしい方法ですね。あとは、これをちょっと 手直しするだけです。^^



3)
それでは、このプログラムにちょっと手を加えていきます。
──────────────+
1.まず、印をつけておいた行

  ActiveCell.FormulaR1C1 = "111"

 を、下記に修正してください。

  a = ActiveCell.Value


2.次に、同じく印をつけておいた行

  ActiveCell.FormulaR1C1 = "222"

 を、下記に修正してください。

  ActiveCell.FormulaR1C1 = a
──────────────+


修正した後のプログラムは下記の様になります。


++++++++++++─
Sub Macro1()
'
  a = ActiveCell.Value
  Range("A2").Select
  Workbooks.Open Filename:= _
    "C:\Documents and Settings\My Computer\data.xlsx"
  Range("B2").Select
  ActiveCell.FormulaR1C1 = a
  Range("B3").Select
End Sub
++++++++++++─



4)
それでは、さっそく実行してみましょう。
──────────────+
1.エクセルの画面に戻って、

2.まず、実行する前にテストデータを準備します。
 以下のテキスト(計4行)をコピーして、セルA1の位置に
 そのまま貼り付けてください。

転記するデータ
あいうえお
かきくけこ
さしすせそ


3.データがセットできたら、その中の2番目のデータ「かきくけこ」
(セルA3)を選択してください。

4.では、実行します。
 [開発]タブの[コード]で [マクロ] をクリック → 「マクロ」
 画面が出るのでそのまま[実行]をクリック。
──────────────+


実行した結果、「data.xlsx」が開き、その転記場所(Sheet2のセルB2)に 「かきくけこ」と表示されていればOKということになります。


要するに、Excelの場合のデータの保管先というのは

3つの階層 (ブック → シート → セル)

となっていますので、この3階層の指定方法さえ分かっていれば事足りるわけ です。(今回は、特にこの内の「ブック操作」について勉強していますが、他 のセルやシートの指定方法については、過去にこの講座で色々と勉強してきま したのでそちらご参考ください。時間の関係で今回は割愛します。)


あとは、ついでに(ファイルを開きっぱなしでは何ですので・・)開いた ブックを閉じるというところまでマクロで自動処理させたいと思います。



5)
それでは、再びマクロの記録を行います。
──────────────+
1.まず始めに、タスクバーからブック「Book1」の方を開いてください。

2.ここで、マクロの記録を開始します。
 [開発]タブの[コード]で [マクロの記録] をクリック →
「マクロの記録」画面が出るので、そのまま[OK]をクリック。

3.そうしたら、今度はタスクバーからブック「data.xlsx」の方を
 開いてください。

4.次に、いま開いたブック「data.xlsx」を下記の操作で閉じます。
 [ファイル]タブの[閉じる] →「dataへの変更を保存しますか?」
 →「保存」

 ※ここでは必ず「保存」(保存する)を選んでください。

5.[開発]タブの[コード]で [■記録終了] をクリックして、
 マクロの記録を終了させます。
──────────────+


これで、下記のようなマクロ(Macro2)が記録されたと思いますので、

++++++++++++─
Sub Macro2()
'
  Windows("data.xlsx").Activate
  ActiveWorkbook.Save
  ActiveWorkbook.Close
End Sub
++++++++++++─


あとは、これを先ほどのMacro1の最後で呼んで(Call Macro2 して)あげれば よいだけですので、完成マクロは下記のようになります。

なお、Macro2の最初にあった Windows("data.xlsx").Activate の行は特に要ら ない(ファイル名の2重の指定になってしまう)ので削除してあります。


++++++++++++─
Sub Macro1()
'
  a = ActiveCell.Value
  Range("A2").Select
  Workbooks.Open Filename:= _
    "C:\Documents and Settings\My Computer\data.xlsx"
  Sheets("Sheet2").Select
  Range("B2").Select
  ActiveCell.FormulaR1C1 = a
  Call Macro2
End Sub

Sub Macro2()
'
  ActiveWorkbook.Save
  ActiveWorkbook.Close
End Sub
++++++++++++─


転記したい元のデータを入力してあるセルを選択した状態でこのMacro1を実行 してください。 (今回は、保存して閉じるところまで自動でされますので、実行後に転記先の ブック「data.xlsx」を普通に開いて確認をしてみてください。)


今回のブック操作でちょっと注意が必要なのは、2つのブックを同時に開いて いる時にどちらのブックを開いて置くのか?という点ですが、

マクロの記録を開始する場合は、必ず記録している元ブックの方(プログラム のある方)を開いてから・・という点だけ注意してください。


今回勉強したブック操作をまとめますと、複数ブックを扱う場合のポイントは、

・ファイル操作は必ずExcelのメニューから行うこと。(フォルダから  ファイル名をダブルクリックして開く操作は、Windowsの操作であって  Excelの操作ではないからマクロは記録されません!)

・マクロの記録開始前には必ず記録させる方のブックを前面に表示させる  こと。(自動作成されるマクロは、マクロの記録開始時に開いている  ブック(前面のブック)に記録保存されます。)

という2点です。それだけご注意いただければバッチリです。^^


今回はこれで終了です。


NEXT >>
データのある範囲だけを自動印刷するマクロ
メディア掲載(日経xTECH)記事「入出金の自動集計表を作成するコツ」



マクロ初心者(入門者)の皆さんへ

こんにちは、「Go!Go! エクセルマクロをはじめよう!」筆者の三太郎です。
私はこの道25年、現役バリバリのベテランSE(システムエンジニア)をしています。 なので、業界にどっぷり染まってしまった IT業界人 です。(笑)

マクロ(エクセルのプログラミング)がどれだけ便利なものなのか・・・ ということは、 皆さんもうよくお分かりいただけてる(!?)ものと思いますが、でも 「やっぱ、中々取っつき難い、ハードルが高い、素人には難しい。」 そう感じている方も多いかと思います。

エクセルをほぼ1日中使ってる人が多い職場であっても、マクロを使える人はほとんど居ません。 (30人の職場で精々1人か2人居ればいいほうかと思います。。) 私は、そのような現状(PC仕事の非効率)を改善して、日本にもっと効率良いIT化の機運を 高めて行きたい!そう願って、今から十数年前にこのようなメルマガ講座を始めました。

マクロを始める為の条件は、エクセル上級者ではありません! 頻繁にエクセルを使ってる人、 ただそれだけです。エクセルの操作レベルはまったく関係がありませんので、 エクセルユーザーの全ての皆さんが当メルマガ講座の参加対象者です。

パソコン仕事で この上なく便利なエクセルのマクロ というものを、もっと多くの人に知って欲しい、使って貰いたい。その想いだけで、長年メルマガの 無料配信を続けてきました。今では、この分野では異例とも言える1万人を超える大勢の皆さんに ご登録いただいているメルマガ講座に成長いたしました。 読者の皆さんからのご声援のお陰です。本当、ありがとうございます。

当メルマガ講座では、簡単に出来るマクロ作成法のコツとその手順を教えています。 とにかく作って、動かす。だから楽しくなってきます。 VBAのカタカナ用語や難しい仕組みの理解、構文暗記といった従来型の不必要な勉強は一切しません! なぜなら、エクセル作業の自動化にその必要は一切ないからです。 初心者がすぐに挫折するカタカナ用語羅列のマクロ勉強なんて、殆どの人には役に立ちません!

マクロとは、エクセルの作業を自動化する為の道具に過ぎません! マクロを組む為に難しいプログラムの仕組みや わけのわからないカタカナ専門用語を覚える必要など毛頭ないわけです。 私はこれまで十数年間、大勢の読者に教えてきて、Excel自動化に成功した沢山の人を生み出してきた 経験で、そう断言します! (本屋に並ぶVBA参考書のライターレベルの人の言うことを真に受けて、 あなたに必要のない”勉強の為の勉強”をしてしまわぬよう、 くれぐれもご注意ください。)

ここでは、「これからマクロを始めてみようかな?」と思ってる方や、 すでにどこかで勉強して「すぐに挫折してしまった・・(>_<)」という方に、 安心して勉強のできる方法とその実習環境とが用意されています。 もし、あなたが過去に挫折した経験者であれば、きっと私が常々言っている 勉強すべき事とすべきでない事 その違い(ここの初心者学習環境の素晴らしさ・・)というものがすぐに分かっていただけるだろうと思います。

マクロ(VBA)というのは多義に渡ります。アマチュアのサンデープログラマーから ベテランの上級者やプロに至るまで、実に幅が広いものです。 初学者には到底 必要のない 難しい部分まで勉強してしまうから当然、 必ずずぐに挫折する事になります。

残念ながら、「まだピカピカの小学一年生(初心者)に、いきなり掛け算や割り算はおろか、 三角関数や微分積分までも教えてしまうような痛ましい光景」を、ネットでも参考書でもセミナーでも、 VBAの世界ではたくさん目にします。

だから、「何を勉強するか」ではなく「何を勉強しないか」 初心者にはその正しい選択が重要なんです。勉強の範囲を 初心者の領域(Excel業務の効率化)だけに絞ってやりさえすれば 、さほど難しく考える必要はありません。それで、難しく、さっぱりちんぷんかんぷんだと思っていたマクロが 楽しく、 どんどん楽しく、勉強できるようになります。

→ エクセルマクロを10分で理解する!(YouTube動画)


あなたもこの三太郎式マクロ勉強法で、面倒なExcel仕事の自動化を ぜひ、この他にはない画期的な方法で実現してください。あなたの 参加を(下記無料メルマガへのご登録を)お待ちしてます!! (少々、力説し過ぎて話長くなりました。すみません。m(__)m)




 
最新号 は、下記にアドレス登録すると無料配信されます。

▼マクロ講座の登録はこちら((無料)) まぐまぐ
Go!Go! エクセルマクロをはじめよう! (マガジンID:0000135169)   
メルマガ登録
  メールアドレス: