[[20161230111936]] 『リンクして貼り付けられるようにしたい』(ヨッシー) ページの最後に飛ぶ

[ 初めての方へ | 一覧(最新更新順) | 全文検索 | 過去ログ ]

 

『リンクして貼り付けられるようにしたい』(ヨッシー)

ブックのシートには、「名簿」「書式」「出席簿」の3つがあります。
Option Explicit

Sub Sample()

    Dim r As Range
    Dim ws As Worksheet

    For Each r In Selection
        If r.Value <> "" Then
            On Error Resume Next
                Set ws = Worksheets(r.Value)
            On Error GoTo 0
            If ws Is Nothing Then
                Worksheets("書式").Copy after:=Worksheets(Worksheets.Count)
                Worksheets(Worksheets.Count).Name = r.Value

            End If
        End If
    Next
 End Sub

< 使用 Excel:Excel2016、使用 OS:Windows10 >


それで質問はどんなことでしょう。

シートがなければ、シートは作成されたようですが。

(γ) 2016/12/30(金) 11:43


「リンクして貼り付け」の意味がわかりませんが
それを手操作で実行するととんでもないことになりませんか?

あと本題とは違いますが、
今のコードは、変数wsに一度セットされると
以降nothingになりませんが、問題ありませんか。

(マナ) 2016/12/30(金) 11:47


すいません。質問の途中でボタンを押してしまいました。
上のマクロを教えてもらい、「書式」が「名簿」分作られるようになっています。
            C D E F G H
1 小田和正     72 0 0 0 0 0    
2 安室奈美恵 72 2 1 1 2 2
という形の出席簿をフリーソフトで別のブックに作ってあり、このシートの「出席簿」としてリンクして貼り付けてあります。この出席簿の値を、名簿分作られた書式一部の表にリンクして貼り付けられるようにしたいのですが、どうしたらよいですか。貼り付けたい表は、「書式」の一部に次のようになっています。
出欠の記録
項目   授業 忌引・出停 事欠 病欠 遅刻 早退
1学期
2学期
3学期
合計

ごめんなさい。
何回読んでもよくわかりません。

その出席簿の値を「リンクして貼り付け」する操作を
手作業でする場合の手順を教えていただけますか。
できれば、箇条書きで。

(マナ) 2016/12/30(金) 12:33


 > 今のコードは、変数wsに一度セットされると 
 > 以降nothingになりませんが、問題ありませんか。 
 すごいです。瞬時に間違いを判別された。
 私は、質問は何? ということだけ気になっただけでした。

 で、ご指摘のとおり、
             Set ws = Nothing
             On Error Resume Next
                 Set ws = Worksheets(r.Value)
             On Error GoTo 0
 としないとだめでしょうね。
 ====================
 さて、まずリンク貼付は、簡単な動作をマクロ記録してみるといいですよ。
 コードの素(もと)が得られます。

 次に、コードの骨格を考えていきます。
 (1)提示されたコードでは、A列の名前の列を選択しているんですか?(きちんと書かないとわからない)
 (2)同じ行のC列からH列をコピーし、
 (3)ws シートの、どの行に貼り付けるのですか?
    学期の判定はどこでするのですか?
    授業の列をみて、最終行の次の行に書き込むのですか?
 (4)それが決まったら、授業の列の適切な行にリンク貼付します。
 (5)次の人に進みます。
 といったことです。

 ご自分で、少しトライしてみますか?            
 =====================
 投稿にあたっては、編集からではなく、「コメント」に記入して、
 「コメント・プレビュー」を経由してください。
 =====================
 うーん、少し被ったが、せっかく書いたので、そのまま挙げます。

(γ) 2016/12/30(金) 12:36


ありがとうございます。マナさんとγさんの指摘の通り、まず、この学校で教えてもらったマクロを修正して名簿分のシートができるかやってみましたが、きちんとできました。「Set ws=Nothing」を加えれば正しいものになるということですね。
まず、マナさんの質問の答えになるかどうかわかりませんが、書いてみます。
〇このブック(A)のほかに、フリーソフトで作った別のブック・出席簿(B)があります。Bの中のシートに「通知表貼付集計表」があります。
〇「通知表貼付集計表」の中のAにリンクさせて貼り付けたい範囲をドラッグしてコピーします。
〇Bは格納した状態から、Aの「出席簿」シートに「リンクして貼り付け」を選択して貼り付けます。
〇Aの「出席簿」の例えば、1学期分のI〜N列の数字・値を、マクロで作成した名簿分のシートの「出欠の記録」という表の各々に、リンクさせて数字・値を反映させたい。
ということなのですが、理解できますか。
(ヨッシー) 2016/12/30(金) 14:09

>出席簿をフリーソフトで別のブックに作ってあり、
>このシートの「出席簿」としてリンクして貼り付けてあります。

ブックBとのリンクはすでにおわっているのですよね。
ならば、ブックAのことだけ説明いただければよいです。

手作業だとこういう流れで間違いないですか。
まずは、「小田和正」だけについて考えてください。

1)「出席簿」シートを選択
2)B2の値を調べる・・・「小田和正」
3)「書式」シートを同じブック内にコピー
4)コピーしたシートの名前を2)で調べた「小田和正」に変更する
5)「出席簿」シートを選択
6)「小田和正」の行のC列からH列(???)をコピー
7)「小田和正」シートを選択
8)セル○○を選択
9)リンク貼り付け

コピー元と貼り付け先があいまいです。
教えていただけますか。

(マナ) 2016/12/30(金) 15:39


マナさんありがとうございます。
手作業で行う、「小田和正」だけについて考えます。
1)「出席簿」シートを選択
2)「出席簿」シートのA5行に並ぶ、「授業・I5」「停/忌・J5」「事欠・K5」「病欠・L5」「遅刻・M5」「早退・N5」の6つの値を調べる。(確認する)
3)と4)ですが、「小田和正」の「書式」と同じシートは、一番初めに書いたマクロで自動的に出来上がり、シートの名前も自動的に「小田和正」になるようになっています。
5)「出席簿」シートを選択
6)「小田和正」の2)で書いたI5〜N5までの値をコピーします。
7)「小田和正」シートを選択
8)リンクして貼り付けたいセルは、「授業・I214」「停/忌・O214」「事欠・U214」「病欠・AA214」「遅刻・AG214」「早退・AM214」です。
9)リンク貼り付け
という流れになると思います。これでいいですか。よろしくお願いします。
(ヨッシー) 2016/12/30(金) 17:36

>という流れになると思います。

その手順で実際に操作してみていただけますか。
うまく貼り付けできないはずです。
実際に手作業で期待通り貼り付けできる手順に修正お願いします。

マクロでないですよ。手作業ならどうするかを教えてください。

(マナ) 2016/12/30(金) 18:31


マナさんありがとうございます。マナさんの言う通り、うまく貼り付けできません。6)でコピーして8)で貼り付けすると、「授業」のセルにだけ値は反映されます。6)で1つずつコピーして、1つずつリンクして貼り付けていくと全部入りますが、6)ですべての項目をコピーしてリンクして貼り付けさせるとうまく貼り付けできません。
「実際に手作業で期待通り貼り付ける手順」がわからないと、この作業は無理ですか。
(ヨッシー) 2017/01/01(日) 04:23

>1つずつコピーして、1つずつリンクして貼り付けていくと全部入りますが、

ならば、それでよいです。その操作を手順に反映させて下さい。
手順は1行にしないで、出きるだけ複数行に分割でお願いします。
手作業の場合の1操作を1行で説明するのが理想です。

>「小田和正」の「書式」と同じシートは、一番初めに書いたマクロで自動的に出来上がり、シートの名前も自動的に「小田和正」になるようになっています。

面倒かも知れませんが、ここも省略せず、手順に含めてください。

>「実際に手作業で期待通り貼り付ける手順」がわからないと、この作業は無理ですか。

マクロについては、手順が決まってから。

(マナ) 2017/01/01(日) 09:46


手順ができたなら、その通り実際に操作してみてください。
期待通りの結果が得られない場合は、手順を見直してください。
操作者がヨッシーさんでなくてもできる手順にすることが重要です。

ここまでできたら、次は1行ずつマクロに変換していきます。
わからないところは、「マクロの記録」を利用するとよいです。

こんな感じで、まずは「小田和正」についてできるようにします。

念のため、言っておきますが、
マクロは、ヨッシーさん自身に作って欲しいです。
わたしができるのは、そのためのお手伝いです。

(マナ) 2017/01/01(日) 11:57


マナさんありがとうございます。「マクロは自身で作って欲しいです」というアドバイスが身に染みています。実は、私「ヨッシー」は教員です。これまでずっと連絡票を一人一人手書きで作成してきたのですが、この欄に書いたマクロをこの学校で教えてもらい、劇的に仕事が楽になりました。もう少し、同僚の先生方が楽になるように、と今回投稿しました。マクロについては、自分で書けませんが、「マクロの記録」については少しできます。ですので、マナさんのおっしゃる通り、手順を明確にして、少しずつチャレンジしてみようと思います。
そこでさっそく質問させてください。
〇「手順は1行にしないで、出きるだけ複数行に分割でお願いします。
 手作業の場合の1操作を1行で説明するのが理想です。」のアドバイスをもう少し教えてください。
〇「小田和正」の「書式」と同じシートは、一番初めに書いたマクロで自動的に出来上がり、シートの名前も自動的に「小田和正」になるようになっています。
面倒かも知れませんが、ここも省略せず、手順に含めてください。というアドバイスですが、この投稿の初めに書いて、間違いを指摘されたマクロを利用することはできないのでしょうか。
〇次は1行ずつマクロに変換していきます。 
わからないところは、「マクロの記録」を利用するとよいです。 というアドバイスですが、マクロについてはほとんど素人の55歳の私が、「マクロの記録」だけでできるでしょうか。 

(ヨッシー) 2017/01/01(日) 14:15


> 手作業の場合の1操作を1行で説明するのが理想です。」のアドバイスをもう少し教えてください。

例えば、駄目なのはこんな感じ

 ・「出席簿」シートのI5〜N5までの値をコピーして
 ・「小田和正」シートのI214、O214、U214、AA214、AG214、AG214にリンク貼り付けする

こんな感じで面倒でも、1操作ずつ書き出して欲しいという意味です。

 ・「出席簿」シートを選択
・I5セルを選択
・セルのコピー
・「小田和正」シートを選択
・I214を選択
・リンク貼り付け
・「出席簿」シートを選択
・J5セルを選択
・セルのコピー
・「小田和正」シートを選択
・O214を選択
・リンク貼り付け
・「出席簿」シートを選択
・K5セルを選択
・セルのコピー
・「小田和正」シートを選択
・U214を選択
・リンク貼り付け
・「出席簿」シートを選択
・L5セルを選択
・セルのコピー
・「小田和正」シートを選択
・AA214を選択
・リンク貼り付け
・「出席簿」シートを選択
・M5セルを選択
・セルのコピー
・「小田和正」シートを選択
・AG214を選択
・リンク貼り付け
・「出席簿」シートを選択
・N5セルを選択
・セルのコピー
・「小田和正」シートを選択
・AM214を選択
・リンク貼り付け
  

この作業は、「マクロの記録」をとるときにも役立ちます。

>この投稿の初めに書いて、間違いを指摘されたマクロを利用することはできないのでしょうか。

最終的には、現在のマクロを利用することになると思います。
だからこそ、現在できているシートのコピー部分も含めて手順を作成お願いします。
現在のコードのどこに何を追加すればよいか理解しやすいと思います。

>「マクロの記録」だけでできるでしょうか。

残念ながら「マクロの記録」だけでは無理です。
足りないところは、お手伝いできると思います。

まずは、ヨッシーさん以外でもできる手順を作成してください。

(マナ) 2017/01/01(日) 15:30


 >マクロについてはほとんど素人の55歳の私が、「マクロの記録」だけでできるでしょうか。

 回答者によって答は違ってきますが、出来ると言う人が多いように感じます。

 私は、まず無理と思います。
 人に使って貰うレベルのものはそんなに簡単じゃないです。

 自分用に作って、使って、色々不具合が出て、それをつぶして、数十回使ってみて
 ようやく人様に使ったらどうですか、って言えるレベルになるんです。

 それでも、使ってみた人から「おかしい!」なんて文句を言われたりします。
 何たって、使うだけの人は、とんでもない使い方していたりするんです。

 そんな使い方をするなって言ったろう、と諭しても、
 そんな使い方は当たり前だと言い返され兼ねないです。

 すると、自分だけが使っていた時より、ずーっと慎重なプログラムを作る必要に迫られます。
 これも結構負担ですよ。ただ、そんな経験が腕を上げくれる切っ掛けになるとは言えます。

 >    For Each r In Selection ←

 1.人様に使って貰うプログラムの場合、Selectionなんて記述は普通やりません。
 何故なら、処理範囲がチャンと選択されていないことが珍しくないからです。

 もっと確実に処理範囲を特定できる方法があるなら、それを使う必要があります。
 後述のサンプルが正しければ、B2以下、データがある行までが処理範囲じゃないですか?

 2.話の前提として、入力データと出力データを明確にする必要があります。

 (1)出席簿のレイアウトは以下ですか?

  行  __A__  _____B_____  __C__  _____D_____  __E__  __F__  __G__  __H__
   1  名簿   氏名         授業   忌引・出停   事欠   病欠   遅刻   早退 
   2     1   小田和正       72            0      0      0      0      0 
   3     2   安室奈美恵     72            2      1      1      2      2 
   4     4   太郎           71            1      2      4      5      9 

 (2)書式シートのレイアウトは以下ですか?

  行  __A__  __B__  _____C_____  __D__  __E__  __F__  __G__
   1  項目   授業   忌引・出停   事欠   病欠   遅刻   早退 
   2  1学期                                                
   3  2学期                                                
   4  3学期                                                
   5  合計                                                 

 (3) 安室奈美恵の結果図はどうなりますか?
  ※出席簿のデータに学期情報がないのに、どうして個人別書式シートが正しく埋められるのですか?

(半平太) 2017/01/01(日) 16:44


マナさんありがとうございます。シートのコピー部分の手順を考えてみました。
・児童名簿を選択する
・A1セルにある「小田和正」を選択する
・「書式」を選択する
・「書式」のコピーを、シート「出席簿」の後に作成する
・コピーしたシートを選択する
・シートの名前を「小田和正」に変更する
これでいいのでしょうか。いいとしたら、「マクロの記録」をとればいいですか。

リンクのはりつけをする手順は、マナさんに教えてもらった手順でやっていこうと思います。
とりあえず、「マクロの記録」だけでやっていくしかないと思いますが、やってみた結果を投稿すればいいですか。

(ヨッシー) 2017/01/03(火) 10:04


すでにできている部分の「マクロの記録」は必要ないです。
わからない部分だけ、利用すればよいです。
「マクロの記録」だけでやっていくなんて無理だし、できたとしても無駄です。

いずれにしてもマクロのことは、まだ考えなくてもよいです。
「小田和正」の手順もできていませんので。

>シートのコピー部分の手順を考えてみました。
>これでいいのでしょうか。

もう少し操作者が迷わなくてよい手順をお願いします。
シート名も正確にしてください。

以下の手順の間違いを修正して下さい。

  
・「出席簿名簿」シートを選択する
・A列の最初のセル(A1セル)を選択する
・そのセルの値を調べる・・・「小田和正」(後から使うので覚えておくこと)
・「小田和正」シートがすでに存在するか調べる
・存在しなければ、「書式」のコピーを、シート「出席簿名簿」の後に作成する 
・コピーしたシートを選択する 
・シートの名前を「小田和正」に変更する 
  

(マナ) 2017/01/03(火) 11:14


マナさん、単純な質問があります。今、作業しているブックのシート名は、順番に「児童名簿」、「書式」、「出席簿」(これは別のブックの出席簿とリンクができています)となっています。それで、今使っているマクロは、「児童名簿」のA列の最初のセル(A1セル)を選択して、「児童名簿」シートの中に作った「連絡票作成」ボタンで「小田和正」シートができるようになっています。マナさんが「以下の手順の間違いを修正して下さい」の最初に指摘している、『「出席簿名簿」シートを選択する』は、『「児童名簿」を選択する』ではだめということでしょうか。
(ヨッシー) 2017/01/03(火) 13:07

なるほど、勘違いしていました。
出欠記録をリンク貼り付けするマクロは
・現在のマクロに組み込むのではなく
・別の独立したマクロを作成したい
ということでしょうか?

でも、だめです。
勘違いはしていましたが、それでも作るマクロは同じです。

「出席簿」シートに「小田和正」の「出欠記録」データがあるなら、
それを「小田和正」シートにリンク貼り付けしますよね。
勿論、「小田和正」シートが事前に作成されていない場合は、自動で作成した上でです。

「児童名簿」シートに「小田和正」があっても、なくても
リンク貼り付けするのですよね。

ならば「出席簿」シートで「小田和正」を確認することが必要です。
操作者は「児童名簿」シートを見なくても、リンク貼り付けはできますよね。

(マナ) 2017/01/03(火) 14:14


すると、「出席簿」シートから入らなければいけないということですかね。
担任氏名 0 1学期
0 0 授業 出・忌  欠 席 遅刻 早退
0 0 0 0 事 病 0 0
1小田和正 72 2 1 2 4 5
という形式になっていますが、1学期のセルから調べていかなけれはならないということですかね。

(ヨッシー) 2017/01/04(水) 07:16


       __A__  _____B_____  __C__  _____D_____  __E__  __F__  __G__  __H__
 1                                     1学期(C〜Hまで結合セル)
   2                      欠席(EとF結合セル)
   3          氏名       授業   忌引・出停   事欠   病欠   遅刻   早退 
   4     1   小田和正       72            0      0      0      0      0 
というスタイルに「出席簿」シートはなっています。この「出席簿」シートから選択しなければいけないということですか。すると、1行目の「1学期」のセルを指定し、調べるという流れになるのでしょうか。

(ヨッシー) 2017/01/04(水) 09:14


どちらに回答をつけたらよいのか悩む。
 
最初のコードでは Selectionを前提にしていました。
しかし、それでは他人が使う場合、戸惑うでしょう。
 
InputBoxくらいで学期を指定して、それに見合うデータを対象にしたほうがよい。
 
そのためには、「出席簿」シートのレイアウトをもう少し説明したほうがよいでしょう。
 
(1)3つの学期がひとつのシートにあるのですね?(念のため)
(2)その場合、フォーマットは統一されていますか?
  ・学期の位置は同じか。
  ・学期の位置の3行下からデータが始まるか
(3)(データの終わりの判断に関して)学期と学期の間には、空白行はあるか。
  また、データの途中に空白行は無いと保証できるか。
などを明確にする必要があるでしょう。

(γ) 2017/01/04(水) 14:43


もう一度確認させてください。

「出席簿」シートの「小田和正」の出欠記録を
「小田和正」シートにリンク貼り付けする手順を教えてください。

今は、「小田和正」で「1学期」限定でよいです。
どのセルから、どのセルにリンク貼り付けするのか具体的に教えてください。

「安室奈美恵」や「2学期」「3学期」のデータのことは、
「小田和正」で「1学期」のデータをリンク貼り付けするマクロができてからで、わたしは結構です。

(マナ) 2017/01/04(水) 21:13


マナさん、γさんありがとうございます。
まず、マナさんにこたえたいと思います。手順ですが
・「出席簿」シートを選択
・C4セルを選択
・セルのコピー
・「小田和正」シートを選択
・I214を選択
・リンク貼り付け
・「出席簿」シートを選択
・D4セルを選択
・セルのコピー
・「小田和正」シートを選択
・O214を選択
・リンク貼り付け
・「出席簿」シートを選択
・E4セルを選択
・セルのコピー
・「小田和正」シートを選択
・U214を選択
・リンク貼り付け
・「出席簿」シートを選択
・F4セルを選択
・セルのコピー
・「小田和正」シートを選択
・AA214を選択
・リンク貼り付け
・「出席簿」シートを選択
・G4セルを選択
・セルのコピー
・「小田和正」シートを選択
・AG214を選択
・リンク貼り付け
・「出席簿」シートを選択
・H4セルを選択
・セルのコピー
・「小田和正」シートを選択
・AM214を選択
・リンク貼り付け
です。別ブックの出席簿の値が変わると、このブックにリンクして貼り付けた「出席簿」シートの値が同じく変わり、さらに「小田和正」シートの出欠表の値も変わることを確認しています。

次にγさんに答えたいと思います。
〇3つの学期が同じシートにあります。
〇フォーマットは統一されています。
・学期の位置は同じで、1行目にあります。
〇学期と学期の間には、空白行はありません。また、データの途中にも空白行はありません。

よろしくお願いします。
(ヨッシー) 2017/01/06(金) 04:31


質問1:以下の手順で実行した場合に、期待通りの結果になりますか。

 1)「小田和正」シートが存在するか確認し、なければ作成する
 ・「出席簿」シートを選択 
 ・B4セルを選択(B列氏名の最初のセル)
 ・そのセルの値を調べる・・・「小田和正」(後から使うので覚えておくこと)
 ・「小田和正」シートがすでに存在するか調べる
 ・存在しなければ、「書式」のコピーを、「出席簿」シートの後に作成する 
 ・コピーしたシートを選択する 
 ・シートの名前を「小田和正」に変更する 

 2)「小田和正」1学期のデータをリンク貼り付け
 ・「出席簿」シートを選択 
 ・C4セルを選択 (「小田和正」の行のC列)
 ・セルのコピー 
 ・「小田和正」シートを選択 
 ・I214を選択 
 ・リンク貼り付け 
 ・「出席簿」シートを選択 
 ・D4セルを選択  
 ・セルのコピー 
 ・「小田和正」シートを選択 
 ・O214を選択 
 ・リンク貼り付け 
 ・「出席簿」シートを選択 
 ・E4セルを選択 
 ・セルのコピー 
 ・「小田和正」シートを選択 
 ・U214を選択 
 ・リンク貼り付け 
 ・「出席簿」シートを選択 
 ・F4セルを選択 
 ・セルのコピー 
 ・「小田和正」シートを選択 
 ・AA214を選択 
 ・リンク貼り付け 
 ・「出席簿」シートを選択 
 ・G4セルを選択 
 ・セルのコピー 
 ・「小田和正」シートを選択 
 ・AG214を選択 
 ・リンク貼り付け 
 ・「出席簿」シートを選択 
 ・H4セルを選択 
 ・セルのコピー 
 ・「小田和正」シートを選択 
 ・AM214を選択 
 ・リンク貼り付け 
 3)「小田和正」2学期データのリンク貼り付け
 4)「小田和正」3学期データのリンク貼り付け

 5)「安室奈美恵」シートが存在するか確認し、なければ作成する
 6)「安室奈美恵」1学期データのリンク貼り付け
 7)「安室奈美恵」2学期データのリンク貼り付け
 8)「安室奈美恵」3学期データのリンク貼り付け

 9)すべての児童について同様に実行

質問2:リンク貼り付けする理由は? 単なる値貼り付けではだめなのでしょうか?

質問3:現在使っている各児童のシートを作成するマクロを、ほとんど理解できていないと思ってよいですか?

(マナ) 2017/01/06(金) 21:54


>実は、私「ヨッシー」は教員です。これまでずっと連絡票を一人一人手書きで
>作成してきたのですが、この欄に書いたマクロをこの学校で教えてもらい、
>劇的に仕事が楽になりました。

>マクロについてはほとんど素人の55歳の私が、「マクロの記録」だけでできるでしょうか。

人に何かを教えてる人ですよね?
そうしたら、
どうしたら目的を達成できるか、論理的な説明をするのは得意ですよね?たぶん。

プログラムは所詮、手順の説明を書くだけです。
なのでまずは、日本語で手順を説明するところから始めましょう^^

マクロの記録では、汎用的に使用できるマクロは作れません。
(特に操作者が独自に判断した部分は記録されません。
単に操作がVBAのコードとして記録されるだけです。)
しかし、自分でした操作がどのようなコードになったのかはわかりますよね?
それを参考に応用してVBA語で文章を組み立ててください。
とりあえず、1行分の1学期分を何とかしましょう。
あとは、同じことを繰り返すだけなので。

とりあえず、
マクロ≒作業の自動化と思ってください。
まずは手作業で求めている結果が得られる手順を確立してください。
それができればあとはVBA語に翻訳するだけです。

手順としては、

小田和正シートのI214セルに、出席簿シートの小田和正の行のC列のリンク式を入れる
小田和正シートのO214セルに、出席簿シートの小田和正の行のD列のリンク式を入れる
小田和正シートのU214セルに、出席簿シートの小田和正の行のE列のリンク式を入れる
 ・
 ・
 ・
以下続く

みたいな感じですよね?

そうしたら、マクロの記録をしたら、
========================
Sub Macro1()
'
' Macro1 Macro
'

'

    Sheets("出席簿").Select
    Range("A5").Select
    Selection.Copy
    Sheets("小田和正").Select
    Range("B6").Select
    ActiveSheet.Paste Link:=True
    Range("D21").Select
End Sub
===================
こんな感じですね^^;

でも、結果は、
=出席簿!$A$5
こんな数式が入っているだけなので、
そういう文字列を作ってやってもいいですね。

数式を入力したマクロの記録
=====================
Sub Macro5()
'
' Macro5 Macro
'

'

    Sheets("小田和正").Select
    Range("B20").Select
    ActiveCell.FormulaR1C1 = "=出席簿!R5C1"
    Range("B21").Select
End Sub
===========================

これを整理すると
Sub Macro5()

    Sheets("小田和正").Range("I214").Formula = "=出席簿!C4"
End Sub
となります。

参考URL>>
http://officetanaka.net/excel/vba/cell/cell03.htm
それから、
掲示板で聞いてその通りにやるだけでなく、
「VBA セルに数式を入れる」とか、
検索を上手になってください。
幸いにもエクセルVBAの情報はたくさんあります。
掲示板でいつ回答してくれるか解らないものを待つより、
速く解決する場合もありますよ^^

それから、
貼付先が飛び飛びの上、規則性も見えないですが、
見た目有線で結合セル使いまくりですか?

あと、マクロで作業が自動化できるなら、
いつでもすぐに結果が得られるので、
もしかしたらリンク式は必要ないかもですね?

(まっつわん) 2017/01/06(金) 23:41


マナさん、まっつわんさんありがとうございます。
まず、マナさんに答えたいと思います。
〇1)から9)まですべて、期待通りの結果になりました。
〇「単なる値貼り付けではだめでしょうか」については、別ブックの出席簿に「通知用貼り付け用集計表」があり、そこに、「コピー」→「形式を選択して貼り付け」→「値貼り付け」と書いてあります。ですが、やってみたのですが、「同じ大きさのセルがないと実行できない」とエラーが出てしまいます。私としては、やはり仕事を効率化するために、別ブックの出席簿に記入していくだけで、リンクして連絡票の出欠表に値が自動的に入るようにしたいです。
〇「各児童のシートを作成するマクロをほとんど理解していないと思ってよいか」についてですが、はずかしいですが、理解していません。ただ、図書館からVBAとマクロについての本を借りてきて、勉強してみようと思っています。

次に、まっつわんさんに答えたいと思います。
〇検索でも調べてみたいと思います。
〇マクロで作業を自動化したいのですが、リンク式は必要ないのですか。
(ヨッシー) 2017/01/08(日) 14:55


そろそろマクロを作っていこうと思いますが。もう少しだけ確認です。

質問1:毎回マクロを実行しデータを貼り付け直せば、
リンク貼り付けは不要で「値貼り付け」でよい気がします。
「値と数値の書式」を選んでもエラーになりますか?

質問2:リンク貼り付けにしたい(セルに式を入れたい)ということは、
作りたいのは「ひな形ブックを作成するマクロ」ということでしょうか。
つまり、マクロを使用するのは1回だけ。
次からは、リンク先のデータを更新するだけ?

(マナ) 2017/01/08(日) 16:32


マナさんよろしくお願いします。
質問に対しての回答です。
質問1
〇例えば「小田和正」の1学期の「授業・忌引/出停・事欠・病欠・遅刻・早退」の6つの値をコピーして「値と数値の書式」を選んで、マクロで作成した「小田和正」シートの出欠表に張り付けたら、「授業」の値しか貼り付けられず、ほかは空白のままになります。
〇「毎回マクロを実行しデータを貼り付け直せば」という指摘ですが、今あるマクロでは、「名簿」シートから児童全員を選択して、ボタンで「書式」と同じシートが全員分できて、シートの名前も児童名になります。
そうして出来上がった連絡票に、成績を入れ、所見を書き、出欠表に数字を入れます。マナさんが指摘している「マクロを実行」とは、私が考えている、成績と所見はそのままで出欠表の値だけマクロで児童分入ると言うのとは違いますか。
質問2
○「書式」と同じシートを全員分作るというマクロは1回で、次からはリンク先のデータを更新するだけと考えています。

(ヨッシー) 2017/01/09(月) 11:02


説明ありがとうござます。
1年に1回だけの「ひな形ブック」を作成する作業、
でも手作業では面倒ということですか。
マクロを作るのも、マクロの実行結果が問題ないか確認するのも面倒な気が…

では、今のマクロでシートは作成済みを前提として
必要最小限の作業(リンク貼り付けする)だけのマクロを考えます。

まずは、「小田和正」1学期のリンク貼り付けについて
マクロの記録を実行してみてください。
その結果(記録されたコード)をここに貼り付けてください。

(マナ) 2017/01/09(月) 12:38


マナさん、マクロの記録をしてみました。
Option Explicit

Sub Macro1()
'
' Macro1 Macro
' 小田和正の出席表・1学期の作成
'

'

    Sheets("出席簿").Select
    Range("C4").Select
    Selection.Copy
    Sheets("小田和正").Select
    Range("I214:N215").Select
    ActiveSheet.Paste Link:=True
    Sheets("出席簿").Select
    Range("D4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("小田和正").Select
    Range("O214:T215").Select
    ActiveSheet.Paste Link:=True
    Sheets("出席簿").Select
    Range("E4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("小田和正").Select
    Range("U214:Z215").Select
    ActiveSheet.Paste Link:=True
    Sheets("出席簿").Select
    Range("F4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("小田和正").Select
    Range("AA214:AF215").Select
    ActiveSheet.Paste Link:=True
    Sheets("出席簿").Select
    Range("G4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("小田和正").Select
    Range("AG214:AL215").Select
    ActiveSheet.Paste Link:=True
    Sheets("出席簿").Select
    Range("H4").Select
    Application.CutCopyMode = False
    Selection.Copy
    Sheets("小田和正").Select
    Range("AM214:AR215").Select
    ActiveSheet.Paste Link:=True
End Sub

(ヨッシー) 2017/01/09(月) 14:49


当然ですが同じ操作を繰り返しているだけなので
コードも同じようなのが繰り返されていますよね

ここがコピーしているところ

    Sheets("出席簿").Select
    Range("C4").Select
    Selection.Copy

ここがリンク貼り付けしているところ

    Sheets("小田和正").Select
    Range("I214:N215").Select
    ActiveSheet.Paste Link:=True

あとは、「コピーセル」と「貼り付けセル」を変えて繰り返し。

小田和正だけでも、2学期、3学期までだと3倍の長さになるので現実的でありません。

なので、「コピーセル」と「貼り付けセル」を指定すれば
リンク貼り付けできるマクロを作ります。

記録したマクロの最小単位は、こうなっています。

 Sheets("出席簿").Select	→コピーセルのシートを選択
 Range("C4").Selectt		→コピーセルを選択
 Selection.Copy			→選択セルをコピー
 Sheets("小田和正").Select	→貼り付けセルのシートを選択
 Range("I214:N215").Select	→貼り付けセルを選択
 ActiveSheet.Paste Link:=True →リンク貼り付け

で、こんな感じのマクロを作ってみました。
ここは、飛躍しすぎで理解できないかもしれません。
わからなけれな記録マクロと比較して何となくやっていることが想像できればよいです。

 Sub リンク貼り付けマクロ(コピーセル As Range, 貼り付けセル As Range)

    コピーセル.Worksheet.Select
    コピーセル.Select
    Selection.Copy
    貼り付けセル.Worksheet.Select
    貼り付けセル.Select
    ActiveSheet.Paste Link:=True

 End Sub

使い方:

 実行するマクロを別に作り、そこから上記のマクロを呼び出して使います。
 呼び出すときの書き方は、こうです。

 Call リンク貼り付けマクロ(ピーセル, 貼り付けセル)

 例えば、こんな感じで、2個のセルをリンク貼り付けするときは、2回呼び出します。
 1回目の呼び出しで、C4をリンク貼り付けしています。
 2回目の呼び出しで、D4をリンク貼り付けしています。

 Sub test()

    Call リンク貼り付けマクロ(Sheets("出席簿").Range("C4"), Sheets("小田和正").Range("I214:N215"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("D4"), Sheets("小田和正").Range("O214:T215"))

 End Sub

このマクロを実行してみて動作確認してみてください。
ここまで、問題なければ、
小田和正の1学期、2学期、3学期をリンク貼り付けするマクロを作ってみて下さい。
繰り返し、呼び出すだけです。

できたら、作ったマクロをここに貼り付けてください。

(マナ) 2017/01/09(月) 16:32


マナさん、次のようなマクロになり、実行したらきちんと値が入りました。
Option Explicit

Sub リンク貼り付けマクロ(コピーセル As Range, 貼り付けセル As Range)

    コピーセル.Worksheet.Select
    コピーセル.Select
    Selection.Copy
    貼り付けセル.Worksheet.Select
    貼り付けセル.Select
    ActiveSheet.Paste Link:=True

 End Sub

Sub test()

    Call リンク貼り付けマクロ(Sheets("出席簿").Range("C4"), Sheets("小田和正").Range("I214:N215"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("D4"), Sheets("小田和正").Range("O214:T215"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("E4"), Sheets("小田和正").Range("U214:Z215"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("F4"), Sheets("小田和正").Range("AA214:AF215"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("G4"), Sheets("小田和正").Range("AG214:AL215"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("H4"), Sheets("小田和正").Range("AM214:AR215"))
     Call リンク貼り付けマクロ(Sheets("出席簿").Range("I4"), Sheets("小田和正").Range("I216:N217"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("J4"), Sheets("小田和正").Range("O216:T217"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("K4"), Sheets("小田和正").Range("U216:Z217"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("L4"), Sheets("小田和正").Range("AA216:AF217"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("M4"), Sheets("小田和正").Range("AG216:AL217"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("N4"), Sheets("小田和正").Range("AM216:AR217"))
     Call リンク貼り付けマクロ(Sheets("出席簿").Range("O4"), Sheets("小田和正").Range("I218:N219"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("P4"), Sheets("小田和正").Range("O218:T219"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("Q4"), Sheets("小田和正").Range("U218:Z219"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("R4"), Sheets("小田和正").Range("AA218:AF219"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("S4"), Sheets("小田和正").Range("AG218:AL219"))
    Call リンク貼り付けマクロ(Sheets("出席簿").Range("T4"), Sheets("小田和正").Range("AM218:AR219"))

 End Sub
(ヨッシー) 2017/01/09(月) 21:09

次の「安室奈美恵」に進む前に
少しだけマクロらしく書き換えてみます。

まずは、Sub test()のほうから

1)繰り返しでてくるものは、変数を使用するとよいです。

 Sheets("出席簿")が何度もでてきます。
 Sheets("小田和正")も何度もでてきます。
 こういうときは、変数を使います。
 こんな感じです。

 Sub test2()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("小田和正")

    Call リンク貼り付けマクロ(データWS.Range("C4"), 氏名WS.Range("I214:N215"))
    Call リンク貼り付けマクロ(データWS.Range("D4"), 氏名WS.Range("O214:T215"))

 End Sub

2)貼り付け先は、先頭セルだけでよいです。

 手作業で、I214セルを選択したら、マクロの記録でI214:N215となっちゃいました。
 これは、結合セルだからですよね。入力したいのはあくまで、I214ならば、
 マクロもそのように修正します。

 Sub test3()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("小田和正")

    Call リンク貼り付けマクロ(データWS.Range("C4"), 氏名WS.Range("I214"))
    Call リンク貼り付けマクロ(データWS.Range("D4"), 氏名WS.Range("O214"))

 End Sub

とりあえずtest3の書き方に変更してみてください。
そうすると、次の改善点が見えてきます。

(マナ) 2017/01/09(月) 21:57


マナさん、マクロを変更してみました。マクロ実行のボタンも作ってみました。きちんと値が入りました。
書式の他の部分にも値を入れて、きちんと値が残りつつ、出欠表に値が反映されることも確認しました。
Sub test3()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("小田和正")

    Call リンク貼り付けマクロ(データWS.Range("C4"), 氏名WS.Range("I214"))
    Call リンク貼り付けマクロ(データWS.Range("D4"), 氏名WS.Range("O214"))
    Call リンク貼り付けマクロ(データWS.Range("E4"), 氏名WS.Range("U214"))
    Call リンク貼り付けマクロ(データWS.Range("F4"), 氏名WS.Range("AA214"))
    Call リンク貼り付けマクロ(データWS.Range("G4"), 氏名WS.Range("AG214"))
    Call リンク貼り付けマクロ(データWS.Range("H4"), 氏名WS.Range("AM214"))
    Call リンク貼り付けマクロ(データWS.Range("I4"), 氏名WS.Range("I216"))
    Call リンク貼り付けマクロ(データWS.Range("J4"), 氏名WS.Range("O216"))
    Call リンク貼り付けマクロ(データWS.Range("K4"), 氏名WS.Range("U216"))
    Call リンク貼り付けマクロ(データWS.Range("L4"), 氏名WS.Range("AA216"))
    Call リンク貼り付けマクロ(データWS.Range("M4"), 氏名WS.Range("AG216"))
    Call リンク貼り付けマクロ(データWS.Range("N4"), 氏名WS.Range("AM216"))
    Call リンク貼り付けマクロ(データWS.Range("O4"), 氏名WS.Range("I218"))
    Call リンク貼り付けマクロ(データWS.Range("P4"), 氏名WS.Range("O218"))
    Call リンク貼り付けマクロ(データWS.Range("Q4"), 氏名WS.Range("U218"))
    Call リンク貼り付けマクロ(データWS.Range("R4"), 氏名WS.Range("AA218"))
    Call リンク貼り付けマクロ(データWS.Range("S4"), 氏名WS.Range("AG218"))
    Call リンク貼り付けマクロ(データWS.Range("T4"), 氏名WS.Range("AM218"))

 End Sub
(ヨッシー) 2017/01/10(火) 03:45

次に、Call リンク貼り付けマクロ(… 
18行も続くと、さすがに多いかもしれませんので何とかしたいと思います。

その準備として、Rangeを使っているところをCellsに書き換えて下さい。
例えば、Range("C4")は、Cells(4, 3)になります。

Cells(行番号、列番号) です。

 Sub test4()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("小田和正")

    Call リンク貼り付けマクロ(データWS.Cells(4, 3), 氏名WS.Cells(214, 9))
    Call リンク貼り付けマクロ(データWS.Cells(4, 4), 氏名WS.Cells(214, 15))

 End Sub

(マナ) 2017/01/10(火) 21:13


マナさん、行ってみました。次のようなマクロになって、正しく値が貼り付けられました。
Sub test4()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("小田和正")

    Call リンク貼り付けマクロ(データWS.Cells(4, 3), 氏名WS.Cells(214, 9))
    Call リンク貼り付けマクロ(データWS.Cells(4, 4), 氏名WS.Cells(214, 15))
    Call リンク貼り付けマクロ(データWS.Cells(4, 5), 氏名WS.Cells(214, 21))
    Call リンク貼り付けマクロ(データWS.Cells(4, 6), 氏名WS.Cells(214, 27))
    Call リンク貼り付けマクロ(データWS.Cells(4, 7), 氏名WS.Cells(214, 33))
    Call リンク貼り付けマクロ(データWS.Cells(4, 8), 氏名WS.Cells(214, 39))
    Call リンク貼り付けマクロ(データWS.Cells(4, 9), 氏名WS.Cells(216, 9))
    Call リンク貼り付けマクロ(データWS.Cells(4, 10), 氏名WS.Cells(216, 15))
    Call リンク貼り付けマクロ(データWS.Cells(4, 11), 氏名WS.Cells(216, 21))
    Call リンク貼り付けマクロ(データWS.Cells(4, 12), 氏名WS.Cells(216, 27))
    Call リンク貼り付けマクロ(データWS.Cells(4, 13), 氏名WS.Cells(216, 33))
    Call リンク貼り付けマクロ(データWS.Cells(4, 14), 氏名WS.Cells(216, 39))
    Call リンク貼り付けマクロ(データWS.Cells(4, 15), 氏名WS.Cells(218, 9))
    Call リンク貼り付けマクロ(データWS.Cells(4, 16), 氏名WS.Cells(218, 15))
    Call リンク貼り付けマクロ(データWS.Cells(4, 17), 氏名WS.Cells(218, 21))
    Call リンク貼り付けマクロ(データWS.Cells(4, 18), 氏名WS.Cells(218, 27))
    Call リンク貼り付けマクロ(データWS.Cells(4, 19), 氏名WS.Cells(218, 33))
    Call リンク貼り付けマクロ(データWS.Cells(4, 20), 氏名WS.Cells(218, 39))
 End Sub
(ヨッシー) 2017/01/11(水) 03:16

Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))

test4の18行における、m、n、i、jに入る数値の規則性について考えます。

 (1) mは4固定
 (2) nは、3から始まり、1ずつ加算
 (3) iは、6個単位で、214、216、218と2ずつ加算
 (4) jは、9から39まで、6ずつ加算

なので、こんな感じでまとめることができます。

 Sub test5()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, j As Long, j As Long

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("小田和正")

    m = 4       '(1)
    n = 3       '(2)

    For i = 214 To 216 Step 2              '(3)
        For j = 9 To 39 Step 6             '(4)

            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))

            n = n + 1                      '(2)

        Next
    Next

 End Sub

慣れるとなんてことない作業ですが、はじめてだと難しいと思います。
それぞれの行の意味を頑張って理解してください。

で、これは、「小田和正」の場合です。
これが、次の「安室奈美恵」だと、
どこをどうしたらよいのか考えてみてください

(マナ) 2017/01/11(水) 22:03


マナさん、作ってみました。「小田和正」で「コンパイルエラー」が出たのと、3学期が入力されなかったので自分で直してみました。そうすると、まず「小田和正」の値がすべて正確に入りました。
「安室奈美恵」は「m」の値だけが変わるだけなので、そこだけ修正して実行してみると、「安室奈美恵」の値もすべて正確に入りました。
Sub test5()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("小田和正")

    m = 4       '(1)
    n = 3       '(2)

    For i = 214 To 218 Step 2              '(3)
        For j = 9 To 39 Step 6             '(4)

            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))

            n = n + 1                      '(2)

        Next
    Next

 End Sub

Sub test6()

    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("安室奈美恵")

    m = 5       '(1)
    n = 3       '(2)

    For i = 214 To 218 Step 2              '(3)
        For j = 9 To 39 Step 6             '(4)

            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))

            n = n + 1                      '(2)

        Next
    Next

 End Sub

(ヨッシー) 2017/01/12(木) 02:48


「小田和正」は4、「安室奈美恵」のときは5にすると正しい結果になることがわかりました。

では、逆に4から「小田和正」を、5から「安室奈美恵」を調べるにはどうしますか。
6のときには誰のデータが貼り付けられるのか、どうしたらわかるのかという質問です。

(マナ) 2017/01/12(木) 18:42


マナさん、今回の質問は少し難しいです。マクロを本を使いながらわかる範囲で読んでいったつもりで書きます。変数mと「出席簿」の名前、「小田和正」や「安室奈美恵」が関連していると思います。変数nは、出席簿の項目「授業」などと関連していて、n=n+1というように変わっていきますと書いているように思います。
だから、変数mもm=m+1というように書いていくと良いと思います。ただ、「Set」のところで「小田和正」と指定しているので、m=変数とこちらも指定しなければならないのではないかと思うので、そのあたりをもっと自由に関連させるとよいのではと考えます。
児童の数が学年によって全部違うので、一人ひとりについて、「Sub test数字()」と書いていくのは、膨大な仕事になってしまいますよね。そのあたりを、マクロで簡単に命令していくという作業が必要ということなのでしょうか。

(ヨッシー) 2017/01/13(金) 02:56


まだマクロでどう書いたらよいかは考えなくてよいです。

すべての児童についてリンク貼り付けを順番に実行すればよいのですよね。

最初は、m=4で、それが「小田和正」のデータだと、どうすればわかりすか。
次のデータは、m=5で、「安室奈美恵」なのは、どこを見ればわかりますか。
そもそも、4とか5とは、何でしょうか。

わからなければ、今度は「1学期」の「授業」のデータだけでよいので
「小田和正」から順番に3人分について
手作業で、リンク貼り付けする手順を書き出してみてください。

(マナ) 2017/01/13(金) 20:02


マナさんに教えてもらった、規則性について3人分紙に手作業で書いて確かめてみました。
「test4の18行における、m、n、i、jに入る数値の規則性について考えます。

 (1) mは4固定
 (2) nは、3から始まり、1ずつ加算
 (3) iは、6個単位で、214、216、218と2ずつ加算
 (4) jは、9から39まで、6ずつ加算」

     1学期(データws)
     「授業」「出停・忌引」 「事欠」 「病欠」 「遅刻」 「早退」
小田和正 (4,3)  (4,4)     (4,5)   (4,6)  (4,7)   (4,8)

小田和正の氏名ws

     「授業」「出停・忌引」 「事欠」 「病欠」 「遅刻」 「早退」
1学期  (214,9)  (214,15)   (214,21) (214,27) (214,33) (214,39)

     1学期(データws)
     「授業」「出停・忌引」 「事欠」 「病欠」 「遅刻」 「早退」
安室奈美恵 (5,3)  (5,4)     (5,5)  (5,6)  (5,7)   (5,8)

安室奈美恵の氏名ws

     「授業」「出停・忌引」 「事欠」 「病欠」 「遅刻」 「早退」
1学期  (214,9)  (214,15)   (214,21) (214,27) (214,33) (214,39)

        1学期(データws)
      「授業」「出停・忌引」 「事欠」 「病欠」 「遅刻」 「早退」
山崎まさよし (6,3)  (6,4)    (6,5)   (6,6)  (6,7)   (6,8)

山崎まさよしの氏名ws

     「授業」「出停・忌引」 「事欠」 「病欠」 「遅刻」 「早退」
1学期  (214,9)  (214,15)   (214,21) (214,27) (214,33) (214,39)

となります。つまり、「(1) mは4固定」というmは、その児童のデータwsに並ぶ値がm行に書いてありますと教えていると思います。mは4から始まって、児童数分の数字を書いていくと、その児童の値が調べることができると思います。ただ、「Set 氏名WS = Worksheets("小田和正")」でもきちんと「小田和正」と書かなけれはいけないといことでしょうかね。
「(2) nは、3から始まり、1ずつ加算」はどの児童の列番号は3から20というように決まっています。

 「(3) iは、6個単位で、214、216、218と2ずつ加算
  (4) jは、9から39まで、6ずつ加算」
は、氏名wsの表のセルの行番号と列番号がどんな児童になっても同じなので書き換えなくてよいということだと思います。
回答になったでしょうか。

(ヨッシー) 2017/01/14(土) 03:56


>「Set 氏名WS = Worksheets("小田和正")」でもきちんと「小田和正」と書かなけれはいけないといことでしょうかね。

はい。今、問題にしているのは、それです。
そのために、どうすれば最初にリンク貼り付けするデータが
「小田和正」であることを調べられるのか教えてほしいのです。

>児童数分の数字を書いていくと、その児童の値が調べることができると思います。

ならば、「その児童の名前」も調べられませんか?

「1学期」の「授業」のデータだけだと、
こういう手順でできませんか。

 1)「出席簿」シートを選択 
 2) B列4行目のセルを選択(B列氏名の最初のセル)
 3) そのセルの値を調べてメモする
 4) 同じ行のC列セルをコピーする
 5) 3)でメモした値の名前のシートを選択する
 6)I214セルを選択
 7) リンク貼り付けする
 8) 「出席簿」シートを選択 
 9) B列5行目セルを選択(B列氏名の2人目のセル)
 10) そのセルの値を調べてメモする
 11) 同じ行のC列セルをコピーする
 12) 10)でメモした値の名前のシートを選択する
 13)I214セルを選択
 14) リンク貼り付けする
 15) 「出席簿」シートを選択
 16) B列6行目セルを選択(B列氏名の3人目のセル)
 17) そのセルの値を調べてメモする
 18) 同じ行のC列セルをコピーする
 19) 17)でメモした値の名前のシートを選択する
 20)I214セルを選択
 21) リンク貼り付けする

規則性がありませんか?

(マナ) 2017/01/14(土) 09:35


マナさん、すいません。手作業でするという意味をこの学習の初めに何回も書いてくださっているのに、マクロばっかりに目がいっていてすいません。
マナさんが書いてくれた、「1学期」の「授業」のデータだけの手順には、きちんと規則性が表れています。まず、
1)「出席簿」シートを選択
 2) B列4行目のセルを選択(B列氏名の最初のセル)
 3) そのセルの値を調べてメモする
 の3つの手順で「小田和正」を調べています。特に「そのセルの値を調べてメモする」という手順で、セルの値が「小田和正」という名前の値で、これをしっかり「メモ」することが大事だとわかります。
次に、「5) 3)でメモした値の名前のシートを選択する」という作業で、「メモ」した「小田和正」と同じ名前のシートを選んで選択するというように、「児童の名前も調べる」ことができると思います。

(ヨッシー) 2017/01/14(土) 10:50


つまり、「出席簿」シートのB4、B5、B6と順番に処理していき
そのセルの値が、「児童名=リンクするシート名」ですね。

マクロでどうするかを考えていきます。

・データは、B列の何行目までありますか?
・B4、B5、B6を"m"を使って表すとどうなりますか?

(マナ) 2017/01/14(土) 11:20


マナさんよろしくお願いします

○データは、B列の25行目まであります。
○式は、m=4

       m=m+1
でいいですか。

(ヨッシー) 2017/01/14(土) 13:51


ごめんなさい質問の仕方が悪かったです。
こんな感じでできそうなのがわかりますか。

 Sub B列の氏名を順番に取得()
    Dim データWS As Worksheet
    Dim m As Long

    Set データWS = Worksheets("出席簿")

    m = 4
    MsgBox データWS.Cells(m, 2).Value
    m = 5
    MsgBox データWS.Cells(m, 2).Value
    m = 6
    MsgBox データWS.Cells(m, 2).Value

 End Sub

 Sub B列の氏名を順番に取得2()
    Dim データWS As Worksheet
    Dim m As Long

    Set データWS = Worksheets("出席簿")

    For m = 4 To 25
        MsgBox データWS.Cells(m, 2).Value
    Next

 End Sub

>「Set 氏名WS = Worksheets("小田和正")」でもきちんと「小田和正」と書かなけれはいけないといことでしょうかね。

これで、

 Set 氏名WS = Worksheets("小田和正")

を、どう書き直したらよいかわかりますよね。

(マナ) 2017/01/14(土) 14:22


マナさんが書いてくれたマクロは、まず「B列の氏名を順番に取得」するものですよね。つまり「 4 To 25」と書いてあるように「出席簿」シートの氏名が書かれてあるセル「.Cells(m, 2)」の1番目の児童から最後の児童を「 MsgBox 」というマクロを使ってメモしたところまでということですね。間違っていますか?
次の手順は、「同じ行のC列セルをコピーする」があって、その次に「メモした値の名前のシートを選択する」時に書くマクロは、
Set 氏名WS = Worksheets("小田和正")ではなく、Set 氏名WS = Worksheets("リンクするシート名")ということなのですかね?

(ヨッシー) 2017/01/14(土) 15:46


何をしようとしているかというと

test5とかtest6の
Set 氏名WS = Worksheets("小田和正")
とか
Set 氏名WS = Worksheets("安室奈美恵")

を児童名を使わずに、mを使って書き直してほしいのです。
考えてみて下さい。

そうすると、mを4から25まで、変化させて実行すれば
すべての児童について、リンク貼り付けできるからです。
完成までもう少しですよ。

(マナ) 2017/01/14(土) 16:10


マナさん、test5やtest6の「小田和正」や「安室奈美恵」・・・は「データWS.Cells(m, 2)」なのでこれを使うと、
Set 氏名WS = Worksheets("データWS.Cells(m, 2)")ということでいいのですか?

(ヨッシー) 2017/01/14(土) 16:41


>Set 氏名WS = Worksheets("データWS.Cells(m, 2)")ということでいいのですか?

試してみましたか

(マナ) 2017/01/14(土) 16:59


マナさん、次のようなマクロにして、実行してみたら見事に失敗しました。
Option Explicit

Sub リンク貼り付けマクロ(コピーセル As Range, 貼り付けセル As Range)

    コピーセル.Worksheet.Select
    コピーセル.Select
    Selection.Copy
    貼り付けセル.Worksheet.Select
    貼り付けセル.Select
    ActiveSheet.Paste Link:=True

 End Sub

Sub test5()

    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long

    Set データWS = Worksheets("出席簿")

    For m = 4 To 25
        MsgBox データWS.Cells(m, 2).Value

    Next
    Set 氏名WS = Worksheets("データWS.Cells(m, 2)")

    m = 4       '(1)
    n = 3       '(2)

    For i = 214 To 218 Step 2              '(3)
        For j = 9 To 39 Step 6             '(4)

            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))

            n = n + 1                      '(2)

        Next
    Next

 End Sub

「実行時 エラー インデックスが有効範囲にありません」と出て、
Set 氏名WS = Worksheets("データWS.Cells(m, 2)")の部分が黄色いマーカーでひかれました。
何か、根本から間違っているきがしました。
そこで、マナさんから提示されたマクロとその説明部分を読み返してみました。

で、こんな感じのマクロを作ってみました。 ここは、飛躍しすぎで理解できないかもしれません。
わからなけれな記録マクロと比較して何となくやっていることが想像できればよいです。

 Sub リンク貼り付けマクロ(コピーセル As Range, 貼り付けセル As Range)

    コピーセル.Worksheet.Select
    コピーセル.Select
    Selection.Copy
    貼り付けセル.Worksheet.Select
    貼り付けセル.Select
    ActiveSheet.Paste Link:=True

 End Sub
何が書かれているか、理解はできていませんが、これが基本となるマクロですよね。

使い方:

 実行するマクロを別に作り、そこから上記のマクロを呼び出して使います。
 呼び出すときの書き方は、こうです。

 Call リンク貼り付けマクロ(ピーセル, 貼り付けセル)

「実行するマクロを別に作り、そこから上記のマクロを呼び出して使います。」と説明されているように、実行するマクロをtest5やtest6として提示されましたよね。それが、次のマクロですよね。

Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))

test4の18行における、m、n、i、jに入る数値の規則性について考えます。

 (1) mは4固定
 (2) nは、3から始まり、1ずつ加算
 (3) iは、6個単位で、214、216、218と2ずつ加算
 (4) jは、9から39まで、6ずつ加算

なので、こんな感じでまとめることができます。

 Sub test5()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, j As Long, j As Long

    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets("小田和正")

    m = 4       '(1)
    n = 3       '(2)

    For i = 214 To 216 Step 2              '(3)
        For j = 9 To 39 Step 6             '(4)

            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))

            n = n + 1                      '(2)

        Next
    Next

 End Sub
ここで、初めて、「リンクして貼り付けることができるマクロ」を教えてもらいました。ただ、「 mは4固定」とありますが、次に提示されたマクロを使うと、もう固定では対応できないことになると思います。また、「Set 氏名WS = Worksheets("小田和正")」の氏名をmを使って書き直す必要が出てくることまでは、わかりました。

Sub B列の氏名を順番に取得2()
    Dim データWS As Worksheet
    Dim m As Long

    Set データWS = Worksheets("出席簿")

    For m = 4 To 25
        MsgBox データWS.Cells(m, 2).Value
    Next

 End Sub
このマクロの「 For m = 4 To 25
        MsgBox データWS.Cells(m, 2).Value」で「小田和正」から始まって最後の児童までの氏名の値をメモしたと理解しました。マナさんが手順で示してくれた  >メモした値の名前のシートを選択する というマクロを別に作って、「B列の氏名を順番に取得」するマクロとともに、test5の「リンク貼り付けするマクロ」にうまく当てはめることが必要になると思いますが、間違っていますか。
長々と書いてしまってすいません。
試してみて失敗しましたが、どうしても「 MsgBox データWS.Cells(m, 2).Value」が気になります。

(ヨッシー) 2017/01/14(土) 22:39


>Set 氏名WS = Worksheets("データWS.Cells(m, 2)")

は、こうしてください。

Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)

test5を書き換えるとこうなります。

 Sub test7() 
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long
    Set データWS = Worksheets("出席簿")

    m = 4       '★test5と順番をかえました。
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3 
    For i = 214 To 218 Step 2 
        For j = 9 To 39 Step 6 
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))
            n = n + 1 
        Next
    Next

 End Sub

3人分を実行するには、こうなります。

 Sub test8() 
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long
    Set データWS = Worksheets("出席簿")

    m = 4       '★test5と順番をかえました。
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3 
    For i = 214 To 218 Step 2 
        For j = 9 To 39 Step 6 
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))
            n = n + 1 
        Next
    Next

    m = 5
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3 
    For i = 214 To 218 Step 2 
        For j = 9 To 39 Step 6 
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))
            n = n + 1 
        Next
    Next

    m = 6
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3 
    For i = 214 To 218 Step 2 
        For j = 9 To 39 Step 6 
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))
            n = n + 1 
        Next
    Next

 End Sub

ここまで理解ですきますか。

(マナ) 2017/01/14(土) 23:13


マナさん、ありがとうございます。3人分の貼り付けが実行されることが理解できました。
mの値を4から25まで設定していけば、全員分ができることも理解しました。
For m = 4 To 25
        MsgBox データWS.Cells(m, 2).Value
    Next
を入れると、一人ひとりmの値を設定しなくても全員分が貼り付けられてしまうという簡単なことではないんでしょうね・・・。
(ヨッシー) 2017/01/15(日) 00:33

それぞれ、どんな結果になるか予想してください。
その後で、実行してみてください。

予想できなかったものでも、結果をみて理解できればOKです。

 Sub 新しいシートで確認1()

    Cells(4, 2).Value = 4
    Cells(4, 5).Value = 1

 End Sub

 Sub 新しいシートで確認2()
    Dim m As Long

    m = 4
    Cells(m, 2).Value = m
    Cells(m, 5).Value = 1

 End Sub

 Sub 新しいシートで確認3()
    Dim m As Long

    m = 4
    Cells(m, 2).Value = m
    Cells(m, 5).Value = 1
    m = 5
    Cells(m, 2).Value = m
    Cells(m, 5).Value = 1
    m = 6
    Cells(m, 2).Value = m
    Cells(m, 5).Value = 1

 End Sub

 Sub 新しいシートで確認4()
    Dim m As Long

    For m = 4 To 6
        Cells(m, 2).Value = m
        Cells(m, 5).Value = 1
    Next

 End Sub

MsgBoxは、動作確認用に入れただけで、
実行しても、どこかに記録されるわけではないです。

 Sub 新しいシートで確認5()
    Dim m As Long

    For m = 4 To 6
        MsgBox m
    Next

 End Sub

 Sub 新しいシートで確認6()
    Dim m As Long

    For m = 4 To 6
        MsgBox m
    Next

    For m = 4 To 10
        Cells(m, 2).Value = m
    Next

 End Sub

 Sub 新しいシートで確認7()
    Dim m As Long

    For m = 4 To 20
        Cells(m, 2).Value = m
    Next

 End Sub

(マナ) 2017/01/15(日) 09:18


マナさん、実行する前に予想を伝えておきます。これを投稿したあと、新しいシートの中で実行して確かめてみます。
○Sub 新しいシートで確認1()

    Cells(4, 2).Value = 4
    Cells(4, 5).Value = 1

 End Sub
4行目・2列目に「4」を入力
4行目・5列目に「1」を入力

○Sub 新しいシートで確認2()

    Dim m As Long

    m = 4
    Cells(m, 2).Value = m
    Cells(m, 5).Value = 1

 End Sub
変数m=4なので、
4行目・2列目に「m」を入力
4行目・5列目に「1」を入力

○Sub 新しいシートで確認3()

    Dim m As Long

    m = 4
    Cells(m, 2).Value = m 
    Cells(m, 5).Value = 1
    m = 5
    Cells(m, 2).Value = m
    Cells(m, 5).Value = 1
    m = 6
    Cells(m, 2).Value = m
    Cells(m, 5).Value = 1

 End Sub
変数m行目・2列目に「m」と、変数m行目5列目に「1」を入力を繰り返し
3回行う。

○ Sub 新しいシートで確認4()

    Dim m As Long

    For m = 4 To 6
        Cells(m, 2).Value = m
        Cells(m, 5).Value = 1
    Next

 End Sub
変数mが「4」から「6」なので、それぞれ、
4行目・5行目・6行目の2列目に「m」を入力
4行目・5行目・6行目の5列目に「1」を入力

○Sub 新しいシートで確認5()

    Dim m As Long

    For m = 4 To 6
        MsgBox m
    Next

 End Sub
たぶん4行目から6行目の1列目を選択する?

○Sub 新しいシートで確認6()

    Dim m As Long

    For m = 4 To 6
        MsgBox m
    Next

    For m = 4 To 10
        Cells(m, 2).Value = m
    Next
たぶん4行目から6行目の1列目を選択する、そして、
4行目から10行目の1列目に「m」を入力

○Sub 新しいシートで確認7()

    Dim m As Long

    For m = 4 To 20
        Cells(m, 2).Value = m
    Next

 End Sub
4行目から20行目の2列目に「m」を入力

と思います。実際に実行して確かめたら報告します。

(ヨッシー) 2017/01/15(日) 11:13


マナさん、確かめました。実行してみて結果を見て、
マクロを読み返すと意味がよくわかりました。
「確認5」は4行目・5行目・6行目の2,3,4,5,6列目
のセルが選択されました。たた、小さな画面が出て「ok」を
押しましたが、何も入力されなかったです。

(ヨッシー) 2017/01/15(日) 11:45


これは理解できますか

 Sub 新しいシートで確認8()
    Dim m As Long

    MsgBox "マクロを開始します"

    For m = 1 To 4
        Cells(m, 3).Value = m
    Next

    For m = 1 To 4
        Cells(m, 4).Value = Cells(m, 3).Value + 3
    Next

    Cells(1, 1).Select
    Selection.Value = 100

    MsgBox "マクロを終了します"

 End Sub

(マナ) 2017/01/15(日) 12:39


マナさん、次のような作業をマクロが実行してくれと予想します。
○「マクロを開始します」とメッセージを表示する。「ok」を押すと次からの作業が始まる。
 1行目から4行目の3列目に、1・2・3・4と入力
 次に
 1行目から4行目の4列目に、1+3、2+3、3+3、4+3の結果を入力(4・5・6・7と入力される)
 次に1行目の1列目に100を入力
 「以上でマクロを終了します」のメッセージが表示されて、「ok」を押すと終了する。
実際に実行してみて、また投稿します。
 
(ヨッシー) 2017/01/15(日) 13:22

マナさん、予想した通りの結果になりました。
(ヨッシー) 2017/01/15(日) 13:26

では、以下の作業のマクロを考えてください

1)D6に8、D7に9、D8に10、D9に11、D10に12を入力
2)その後、D8を選択
3)選択したセルの値をmsgboxで表示
4)選択したセルの値を「abc」にする

(マナ) 2017/01/15(日) 13:36


マナさん、次のように考えてみました。

Sub 新しいシートで確認9()
  Dim m As Long

    MsgBox"マクロを開始します"

  For m= 6 To 10

    Cells(4,m).Value=m+1
    Next

    Cells(4,8).Select
    MsgBox"Selection"
    Selection.Value=abc

    MsgBox"マクロを終了します"

End Sub

このマクロで実行したところエラーが出たので、
次のように変えました。すると作業が実行されました。

Sub 新しいシートで確認9()

    Dim m As Long

    MsgBox "マクロを開始します"

    For m = 6 To 10
    Cells(4, m).Value = m + 1
    Next

    Cells(4, 8).Select
    MsgBox " Cells(4, 8).Select"
    Selection.Value = " abc"

    MsgBox "マクロを終了します"

End Sub

(ヨッシー) 2017/01/15(日) 14:18


マナさん、 MsgBox " Cells(4, 8).Select"は MsgBox " Selection"でもきちんと実行されました。

(ヨッシー) 2017/01/15(日) 14:21


>Cells(4, m).Value = m + 1

m + 2 の勘違いでしょうか。

>選択したセルの値をmsgboxで表示

このとき選択状態になっているセルは、D8ですね。
でD8の値は、10 のはずです。

なので、msgboxで、10と表示させたいときは、どうしますか。

(マナ) 2017/01/15(日) 14:31


マナさん、次のように直しました。どうでしょうか。きちんと小画面で「10」も表示されました。

Sub 新しいシートで確認9()

    Dim m As Long

    MsgBox "マクロを開始します"

    For m = 6 To 10
    Cells(4, m).Value = m + 2
    Next

    Cells(4, 8).Select
    MsgBox " 10"
    Selection.Value = " abc"

    MsgBox "マクロを終了します"

End Sub
(ヨッシー) 2017/01/15(日) 15:06


それでは、だめです。
仮に、D8に、10以外の数字が入力されていても 10 しか表示されません。

前にこう書きました。同じですね。

 -----
 >Set 氏名WS = Worksheets("データWS.Cells(m, 2)") 
 は、こうしてください。 
 Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
 ----- 

""で囲むと、その見たままの文字列になっちゃいます。

次の2つの違いをよく考えてください。
そのうえで、D8の値である 10 をmsgbox で表示させてください。

 Sub 新しいシートで確認10()
    Dim m As Long

    m = 4
    MsgBox m

 End Sub

 Sub 新しいシートで確認11()
    Dim m As Long

    m = 4
    MsgBox "m"

 End Sub

(マナ) 2017/01/15(日) 15:19


マナさん、確認10では「4」が表示され、確認では「m」と表示されました。この違いは理解しました。
それで、次のようにしてみました。まだ、違うでしょうか?

Sub 新しいシートで確認9()

    Dim m As Long

    MsgBox "マクロを開始します"

    For m = 6 To 10
    Cells(4, m).Value = m + 2
    Next

    Cells(4, 8).Select
    MsgBox Selection
    Selection.Value = " abc"

    MsgBox "マクロを終了します"

End Sub
(ヨッシー) 2017/01/15(日) 15:38


それでOKですが、できれば

 >MsgBox Selection

でなくて、選択したセルの"値"なのだから

 MsgBox Selection.Value

がよいです。

 ------

本題に戻る前に、もう1つだけ。
次のマクロを考えてみてください。
すでに、参考になるものは提示してあります。

1)A2を選択
2)選択セルの値を1にする
3)A4を選択
4)選択セルの値を2にする
5)A6を選択
6)選択セルの値を3にする
7)A1を選択

(マナ) 2017/01/15(日) 15:57


マナさん、指摘されたところは直しました。マクロを作り、実行し作業を確認しました。

Sub 新しいシートで確認12()

Cells(2, 1).Select
Selection.Value = 1
Cells(4, 1).Select
Selection.Value = 2
Cells(6, 1).Select
Selection.Value = 3
Cells(1, 1).Select

End Sub
(ヨッシー) 2017/01/15(日) 16:44


正解です。

3セルなのでよいですが、もし数が増えると大変ですね。
これをFor 〜 Next を使って書き直してみてください。

(マナ) 2017/01/15(日) 16:55


マナさん、うまくいきません。次のようにひとまずマクロを作ったのですが、
自分でも間違っていると思いつつ、わからないのでそのままのせます。

Sub 新しいシートで確認13()

 Dim m As Long

For m = 2 To 6
Cells(m, 1).Value = m / 2
Next

Cells(1, 1).Select

End Sub

本当は、「For m = 2 To 6 Step2」としたいのですが、構文エラーが出ます。どうしてですか。教えてください。

(ヨッシー) 2017/01/15(日) 18:46


そこまでできれば十分です。

エラー原因はStepと2の間にスペースがないからです。
やはり、自分でタイプしないで
他人のマクロのコピペばかりではだめですね。

 Sub 新しいシートで確認13()
    Dim m As Long

    For m = 2 To 6 Step 2
        Cells(m, 1).Value = m / 2
    Next
    Cells(1, 1).Select

 End Sub

実は、こうするかと予想していました

 Cells(m, 1).Select
 Selection.Value = m / 2

で、「1行に書き直して」と言うつもりでしたが
一手間省けました。

変数をもうひとつ使用してこんな書き方も出来ます。
test5でも使っています。

 Sub 新しいシートで確認14()
    Dim m As Long
    Dim n As Long

    For m = 2 To 6 Step 2
        n = n + 1
        Cells(m, 1).Value = n
    Next
    Cells(1, 1).Select

 End Sub

ここまで理解できたと思えたら
本題に戻りましょう。

test8を修正し、m=4から25まで繰り返すマクロを考えてみてください。

(マナ) 2017/01/15(日) 19:40


マナさん、test8を次のように単純に「m=4」のところを「For m= 4 To 25」とし、「Next」をつけたして実行しても「実行時 エラー9」「インデックスが有効範囲にありません」と出ます。

Sub test8()

    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long
    Set データWS = Worksheets("出席簿")

    For m = 4 To 25       '★test5と順番をかえました。
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3
    For i = 214 To 218 Step 2
        For j = 9 To 39 Step 6
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))
            n = n + 1
        Next
    Next
Next

  End Sub

それで、test5に戻ってみて、単純に「m=4」のところを「For m= 4 To 25」にしてもだめでした。
「For・・・Next」を使って何を繰り返し処理しようとしているのかわかりません。また、マナさんが
「'★test5と順番をかえました。」と書いた理由もわかりません。

(ヨッシー) 2017/01/15(日) 23:27


マナさん、マクロを簡単な文章表現しながら考えました。
Dim 変数宣言
Set 格納する
For どこからどこまで
 Callリンク貼り付け 繰り返して実行する内容
 n=n+1       繰り返して実行かる内容

という流れで作ってみました。
 
Sub test8()

 End Sub
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long
    Set データWS = Worksheets("出席簿")
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)

    For m = 4 To 25
    Next
    n = 3
    For i = 214 To 218 Step 2
        For j = 9 To 39 Step 6
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))
            n = n + 1
        Next
    Next

End Sub
まだ、実行していませんが、今の自分が書けるすべてです。 

(ヨッシー) 2017/01/16(月) 06:24


マナさん、実行してみました。最初に2行目に「End Sub」があることを指摘され、すぐ直して、再び実行しました。またも、エラーです。「実行時エラー 1004」「アプリケーション定義またはオブジェクト定義のエラーです。」とでました。ちょっと調べてみますが、デバックを押すと、
「Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)」が黄色いマーカーがひかれました。

(ヨッシー) 2017/01/16(月) 06:33


横から失礼します。

For m = 4 To 25 の下のNextの位置が違うのではないですか。
これだとFor〜Nextの間に何にも無いですよ。
(pooh) 2017/01/16(月) 14:26


横入り失礼します。

>今の自分が書けるすべてです。

******************************************************

Option Explicit

'***************リンク貼り付けマクロ*********************
'第1引数(ひきすう)ToCell:リンク式をセットするセル
'第2引数 FromCell:参照するセル
'備考:
'コピーしたセルを貼り付ける際の貼り付けの命令は、
'シートに対して行うので、
'リンクして貼り付けの場合は「特に」、
'貼り付け先のセル範囲を選択しておく必要があります。
'(引数Destinationが指定できない為)
'********************************************************
Sub SetLink2Cell(ToCell As Range, FromCell As Range)

    Dim ws As Worksheet

    '貼付先シートの取得
    Set ws = ToCell.Worksheet
    '参照するセルをコピー
    FromCell.Copy
    '貼付先のシートを選択(貼付先シートがアクティブになってないと
    '貼り付け先のセル範囲を選択出来ない為)
    ws.Select
    '貼付先シートの選択
    FromCell.Select
    'リンク貼付
    ws.Paste link:=True
End Sub
'**********************************************************

'************<メインのプロシージャ>***********************
Sub test()

    '変数の宣言
    Dim ws氏名 As Worksheet     '各個人シート
    Dim wsデータ As Worksheet   'リスト(参照先)シート
    Dim ixRow As Long           '参照先の行番号
    Dim ixCol As Long           '参照先の列番号
    Dim x As Long               '貼付先の列番号
    Dim y As Long               '貼付先の行番号

    '準備(元の一覧があるシートの取得)

    'データシートのデータ範囲の3列目のデータを行毎に繰り返し見て行く。
    '(ixRow)初期値は4。最終値はCells(Rows.count,2).end(xlup).rowの値
    For 

        '行毎に変数を初期化
        'ixRow行目の3列目の値のシート名のシートを取得(=貼付先シート)

        '参照するセルの列番号ixColの初期値:3をセット

        '貼付先のセルに注目して処理を繰り返す
        '貼付先の行番号yの繰り返し(初期値:9、最終値:39、間隔:6)
        For

            '貼付先の列番号xの繰り返し(初期値:214、最終値:218、間隔:2)
            For

                'コピーしてリンク貼付(自作のサブルーチンへ)
                Call SetLink2Cell(貼付先セル, コピー元セル)

                '次の参照するセルの列番号ixColを用意

            '次へ
            Next
        '次へ
        Next
    '次へ
    Next
End Sub
'************************<終わり>*************************************************

日本語で書いたらこんな感じではないですかね?
日本語に対応するVBA語を空き行に翻訳してやれば出来ると思います。

<コツッ>

変数は

dim wsデータ as worksheet

のように宣言しておくと、

「ws」まで打って、「Ctrlキー + スペースキー」を押下すると、
リストが出るので、それを選択すれば、日本語の切り替えをしなくて済むので、
スムーズに打ち込めると思います^^
(出来れば日本語を使わない方が切り替えがなくて、タイピングにストレスが無いと思いますが、
英語の変数名を考えるのも一苦労なので、接頭辞等で工夫したいですね^^)

(個人の感想です)

(まっつわん) 2017/01/16(月) 16:23


poohさん、まっつわんさんありがとうございます。参考にさせていただきます。
今は、できる限り自分でマクロを完成させる、とマナさんに約束しているので、
マナさんの指導を待ちます。
(ヨッシー) 2017/01/16(月) 17:53

>マナさんの指導を待ちます。
そうなんですね。。。。
考えるのをやめるということですかね。。。。
それで、「自分で」といえるのか。。。
僕なら何度でも積んだり崩したりして試行錯誤しますが。。。

善意で付き合ってくれている個人に、負担掛けるのもどうかと思いますが。。。

横槍失礼しました。

(まっつわん) 2017/01/16(月) 18:00


>m=4」のところを「For m= 4 To 25」とし、「Next」をつけ

それで間違っていません。ちゃんとできてますよ。
例えば、3人分で
For m= 4 To 6
としたら、エラーでませんよね?

「出席簿」シートのB4〜B25までの
21人の児童名のシートが作ってありますか。
ない場合には、エラーが発生します。
確認お願いします。

エラーと関係ないですが、インデントは正確につけるようにしたほうがよいです。
http://note.chiebukuro.yahoo.co.jp/detail/n2870

(マナ) 2017/01/16(月) 20:12


マナさん、For m= 4 To 6としてらきちんと実行されました。
ところが、For m= 4 To 25として実行すると「インデックスが有効範囲にありません」
とエラーがでます。どういうことでしょうか。
「出席簿」シートのB4〜B25までの
22人の児童名のシートが作ってあります。
この投稿にあげるために「小田和正」「安室奈美恵」「山崎まさよし」の
3人分だけ名前を入れておきましたが、その3人分の名前を入れた上に、
実際の児童の名前が入っている別ブックの出席簿にし、集計用貼り付け表を
コピーして、今のブックの「出席簿」にリンクして貼り付けてあります。
その「出席簿」をもとに、作った下のマクロで実行すると、できる場合と
できない場合があるようです。
Sub test8()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long
    Set データWS = Worksheets("出席簿")

    For m = 4 To 6       '★test5と順番をかえました。
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3
    For i = 214 To 218 Step 2
        For j = 9 To 39 Step 6
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))
            n = n + 1
        Next
    Next
 Next

End Sub

あと、関係があるかどうかわかりませんが、test5のマクロで3人分のをつくりましたが、m=7にすると、その児童の出欠表は空欄のままでした。
(ヨッシー) 2017/01/16(月) 21:40


「出席簿」のB7の児童名が
「児童名簿」シートにありますか。
スペースの有無も含めて一致していますか。

(マナ) 2017/01/16(月) 22:15


マナさん、「出席簿」と「児童名簿」をとにかく何もかも同じにして、
おそるおそる実行したら全員分入りました。
何もかも同じというのは、「出席簿」に入っていた「0」をなくしたり、
「児童名簿」を「出席簿」と同じように4行目から名前を入れたり、
出席番号が入っていなかったので入れたり、セル幅を同じにしたり、
最後にしたのは、「児童名簿」の名前をいったんクリアにして、
「出席簿」の名前をコピーして貼り付けて、児童シートを今ある
マクロで作り直し、マナさんに指導してもらったマクロに
思い切って、「 For m = 4 To 25」と入れたら、突然全員分実行
し始めて、確かめてみると全員分入りました。
別ブックからリンクさせた「出席簿」が、マナさんに教えていただいた
マクロの基本になるということなのですかね。
(ヨッシー) 2017/01/17(火) 05:30

マナさん、今職場です。実行に成功したのでもう一度やってみたらエラー「データを貼り付けできません」がでました。家のパソコンと職場のパソコンが違うためでしょうか。何か原因が考えられるでしょうか・・・。
(ヨッシー) 2017/01/17(火) 08:00

>「出席簿」に入っていた「0」をなくしたり、

関係ないです。

>「児童名簿」を「出席簿」と同じように4行目から名前を入れたり、

関係ないです。

>出席番号が入っていなかったので入れたり、セル幅を同じにしたり、

関係ないです。

>「児童名簿」の名前をいったんクリアにして、
>「出席簿」の名前をコピーして貼り付けて、児童シートを今ある
>マクロで作り直し

これでうまくいったということは、
「児童名簿」の名前と「出席簿」の名前が同じではなかったということです。
例えば「小田和正」と「小田 和正」とか。

なので運用を変えて、

1)「出席簿」の名前から児童シートを作成しながら
2)リンク貼り付けも実行する。

とすることは何か問題ありそうですか。

 -----

>「データを貼り付けできません」

ちょっと原因が思いつきません。
そのファイルで手作業でリンク貼り付けしてもエラーがでますか。
エラーがでたときに、表示されているシートの選択中のセルが
貼り付けできなかったセルなので
そのセルで手作業でも同じメッセージがでるか確認してみてください。

>家のパソコンと職場のパソコンが違うためでしょうか。

それはないと思います。
同じファイルで確認すれば、どちらも同じ結果になると思います。

(マナ) 2017/01/17(火) 20:59


マナさん、家で落ち着いて一つずつ確認しながらやってみました。
1年生から6年生まで、マクロが働いたブックを作ることができました。
それを、別に学校用ファイルとして保存し、学校のパソコンでも同じく
マクロが働いてみるか確認してみます。確認できたら報告します。
(ヨッシー) 2017/01/18(水) 05:47

マナさん、職場の空き時間を使って、6つの学年を確認しています。
2つの学年で、他の4つの学年では出ないメッセージが出ます。
「ブック内の一部のリンクはすぐに更新できません」というものです。
基になっている出席簿に何か不備があるのでしょうか?
(ヨッシー) 2017/01/18(水) 11:27

マナさん、何度もすいません。リンクに成功した中身がまったく同じファイルに
別の名前をつけて保存してあるですけれど、3つの学年は、片方しかリンクしませんでした。
残り3つは、両方とも出席簿の中身を変更してもリンクして、連絡票の表の値が変化しませんでした。
何か原因は考えられますか。
(ヨッシー) 2017/01/18(水) 15:06

>「ブック内の一部のリンクはすぐに更新できません」というものです。
>基になっている出席簿に何か不備があるのでしょうか?

マクロ実行で、リンク切れになることは考えれらません。

>別の名前をつけて保存してあるですけれど、3つの学年は、片方しかリンクしませんでした。

片方とは?どういう意味でしょうか。

>連絡票の表の値が変化しませんでした。

「連絡票」とは、児童名のシートのことででしょうか。

「出席簿」は、別ブックの「通知表貼付集計表」と、
手作業でリンク貼り付けしているという話でしたが
その作業は済ませてからマクロを実行しましたか。

リンクといっても、数式が入っているだけですので
値が更新されないセルの数式が、正しいかどうかを
目で見て確認してみてください。

(マナ) 2017/01/18(水) 19:39


マクロの動作確認は、
「通知表貼付集計表」のデータを更新したとき、
「出席簿」のデータが正しく更新されることを
確認できてからでお願いします。

(マナ) 2017/01/18(水) 19:57


マナさん、おはようございます。すべての学年において、別ブックの
出席簿の値を変えると、きちんとリンクされて、マクロで作ったブックの
各「児童名のシート」に反映されることを確認しました。
お騒がせしました。
(ヨッシー) 2017/01/19(木) 04:05

次に進んでもよろしいですか?

現在のマクロは、児童数が22人固定ですが
学年によって異なることはありませんか。

(マナ) 2017/01/19(木) 19:50


マナさん、その通りです。その時は、マクロの中の
「 For m = 4 To 6 」のところを、確認して実行しています。
あと、「 For i = 214 To 218 Step 2」
も学年によって、少し違うので、確認して実行しています。

(ヨッシー) 2017/01/19(木) 21:07


>「 For m = 4 To 6 」のところを、確認して実行しています。

6を変更するということですか。
それをマクロで求めることができるかもしれません。

>あと、「 For i = 214 To 218 Step 2」

学年によって、「書式」が違うのでしょうか。
どう違うのか説明できますか。

(マナ) 2017/01/19(木) 21:22


マナさん、質問に答えます。
○「 For m = 4 To 6 」の4は固定ですが、各学年人数がちがうので
6は学年ごとに違います。
○「書式」が学年によって違います。同じなのは、A4サイズの4ページで、
各ページはA4サイズにおさめています。ただ、行幅を調整しているので、
「出欠表」のサイズは同じでも、どの行から始まるのかは各学年によって
違います。
(ヨッシー) 2017/01/20(金) 04:22

「出席簿」シートについて:
 例えば、「出席簿」シートのB4からB25まで名前が入力されているとき
 1)22名全員分すべてリンク貼り付けでよいですか
 2)最初の名前B4の上のB3は何か入力されていますか、空白ですか
 3)最後の名前B25の下のB26は空白ですか
 4)B27以下はすべて空白ですか

 ---

「書式」シートについて:

 「出欠表」が「書式」シートの何行目から始まるかは、どうすればわかりますか。
 例えば、「出欠の記録」と入力されているセルがあって、その次の行であるとか。
 マクロではなく、手作業でする場合に、操作者はどうやって判断するかを考えてください。

(マナ) 2017/01/20(金) 19:58


マナさん、質問に答えます。
「出席簿」シートについて
1)全員分のリンク貼り付けでいいです。
2)B1〜B3まで空白です。
3)空白です。
4)空白です。

「書式」シートについて
「出欠の記録」と書かれた結合セルの次の行です。
(ヨッシー) 2017/01/21(土) 06:48


そういことであれば、学年毎にマクロを書き換える必要はありません。
6とか214という数値をマクロで求めればよいのです。

まずは「 For m = 4 To 6 」から

6というのは、
B列でデータが入力されている一番下のセルの行番号
ですよね。

ここに使えそうな例がありませんか。
http://officetanaka.net/excel/vba/tips/tips130.htm

(マナ) 2017/01/21(土) 12:40


マナさん、読んでみました。大事なところは、
○一般的に、リストの最終セルを取得する目的は、おおむね次の2つです。

•データが入力されている範囲を操作する
•最終セルの下に新しいデータを入力する

For Nextの繰り返しなどで、データが入力されている範囲を自動的に判別したいときは、Endモードでジャンプしたセルの行番号を取得すればいいです。

Sub Sample4()

    Dim i As Long
    For i = 1 To Cells(Rows.Count, 1).End(xlUp).Row
        Cells(i, 1) に対する処理
    Next i
End Sub

○「 For m = 4 To 6」というところは、

   For m = 4 To Cells(Rows.Count, 1).End(xlUp).Row
     Cells(i, 2) に対する処理

    Next i
となるのでしょうか?

(ヨッシー) 2017/01/21(土) 14:23


また結果を予想してから、実行してみてください

 Sub 出席簿シートで動作確認1()
    Dim データWS As Worksheet

    Set データWS = Worksheets("出席簿")

    MsgBox データWS.Cells(4, 2).Row
    MsgBox データWS.Cells(5, 2).Row

    MsgBox Rows.Count
    MsgBox データWS.Cells(Rows.Count, 2).Row

    MsgBox データWS.Cells(Rows.Count, 2).End(xlUp).Row

 End Sub

(マナ) 2017/01/21(土) 14:51


マナさん、「Row」がよくわからないのですが、次のように予想してみました。
○「MsgBox データWS.Cells(4, 2).Row」は、
出席簿シートの4行目・2列目にいる、一番先頭の児童の名前を表示する
○「MsgBox データWS.Cells(5, 2).Row」は、
出席簿シートの5行目・2列目にいる2番目の児童の名前を表示する。
○「Rows.Count」はデータがいくつあるか、児童数が表示される。
○「 データWS.Cells(Rows.Count, 2).Row」は、
予想がつきません。
○「データWS.Cells(Rows.Count, 2).End(xlUp).Row」は、
一番最後の児童の名前を表示する。
実行して確かめてみます。

(ヨッシー) 2017/01/21(土) 18:55


マナさん、実行してみました。予想とはまったく違った値が表示されました。
○「MsgBox データWS.Cells(4, 2).Row」は、「4」。2列目の上から数えて、
4行目の「4」ですか?
○「MsgBox データWS.Cells(5, 2).Row」は、「5」。2列目の上から数えて、
5行目の「5」ですか?
○「Rows.Count」は、まったく意味が分からない数字「1048576」
○「 データWS.Cells(Rows.Count, 2).Row」も「1048576」
○「データWS.Cells(Rows.Count, 2).End(xlUp).Row」は、
「25」は2列目のデータがある最後の行は「25」?

(ヨッシー) 2017/01/21(土) 19:10


>データWS.Cells(4, 2).Row

データWS の Cells(4, 2) の Row です。
多くの場合、「.」を「の」に置き換えて考えるとよいです。
で、Rowは、「行番号」という意味です。

 Sub 出席簿シートで動作確認2()
    Dim データWS As Worksheet

    Set データWS = Worksheets("出席簿")

    MsgBox データWS.Cells(4, 2).Row
    MsgBox データWS.Cells(4, 2).Value

 End Sub

ここまで理解できますか。

(マナ) 2017/01/21(土) 19:26


>Rows.Count

これは覚えるしかないです。
Rows の Count
で、すべての行の数 になります。

試してみてください。

 Sub 出席簿シートで動作確認3()
    Dim データWS As Worksheet

    Set データWS = Worksheets("出席簿")

    データWS.Cells(Rows.Count, 2).Select
    MsgBox Selection.Row

 End Sub

ここまで、ついてこれていますか。

(マナ) 2017/01/21(土) 19:48


マナさん、
○「MsgBox データWS.Cells(4, 2).Row」は4行目・2列目の行番号だから
「4」が表示される。(確認しました)
 「 MsgBox データWS.Cells(4, 2).Value」は、4行目・2列目を参照したものを
表示するので、先頭の児童名が表示されました。(確認しました)
この2つの違いは理解できました。

○ Sub 出席簿シートで動作確認3()

    Dim データWS As Worksheet

    Set データWS = Worksheets("出席簿")

    データWS.Cells(Rows.Count, 2).Select
    MsgBox Selection.Row

 End Sub

は、すべての行の2列目を選択。選択したものの行番号を表示するので、
「1048576」が表示されると思ったのですが、
「RangeクラスのSelectが失敗しました」というエラー表示になりました。
これでいいのですか。
(ヨッシー) 2017/01/21(土) 21:37


>出席簿シートで動作確認

他のシートではだめですよ。

(マナ) 2017/01/21(土) 21:44


マナさん、すいません。確認できました。マクロを読んだ通り「1048576」が表示されました。
(ヨッシー) 2017/01/21(土) 21:53

これで、RowとかRows.Countの意味はわかりましたね。
>データWS.Cells(Rows.Count, 2).End(xlUp).Row
次は、End(xlUp)です。

出席簿シートで動作確認3 を実行した状態で
つまり、シートの一番下の行のセルが選択された状態で、

Ctrlキーと↑キーを同時に押してみてください。

どうなりましたか。

(マナ) 2017/01/21(土) 22:03


マナさん、同時に押したら、Cells(25,2)にある児童名のところにセルが選択された状態になりました。
(ヨッシー) 2017/01/21(土) 22:15

では、この操作を「マクロの記録」し
それを教えてください。

>Ctrlキーと↑キーを同時に押し

(マナ) 2017/01/21(土) 22:18


マナさん、次のようになりました。
Sub Macro1()
'
' Macro1 Macro
'
'

'

    Selection.End(xlUp).Select
End Sub
(ヨッシー) 2017/01/21(土) 22:29

Ctrlキーと↑キー同時押しの操作は
マクロでは、End(xlUp)になります。

今度は、セルをいろいろ変えて
Ctrlキーと↑キー同時押し を試してみてください。
Ctrlキーと↑キー同時押しの意味がわかります。

そうすれば、次のマクロの結果を予想できると思います。

 Sub 出席簿シートで動作確認4()
    Dim データWS As Worksheet

    Set データWS = Worksheets("出席簿")

    データWS.Cells(Rows.Count, 2).End(xlUp).Select
    MsgBox Selection.Row

 End Sub

(マナ) 2017/01/21(土) 22:45


もし理解できたと思えば、↓をもう一度読んで
http://officetanaka.net/excel/vba/tips/tips130.htm

自分のマクロに適用してみてください。

(マナ) 2017/01/21(土) 22:52


ごめんなさい、今日はここまでにします。

(マナ) 2017/01/21(土) 23:02


マナさん、以下のようなマクロで実行し、動作を確認しました。

Sub test8()

    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim m As Long, n As Long, i As Long, j As Long
    Set データWS = Worksheets("出席簿")

    For m = 4 To データWS.Cells(Rows.Count, 2).End(xlUp).Row
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3
    For i = 214 To 218 Step 2
        For j = 9 To 39 Step 6
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(i, j))
            n = n + 1
        Next
    Next
 Next

End Sub

ただ、最初2つの学年しかきちんとマクロが実行できなくて、2〜3回やり直すと全学年の
動作が確認できました。マクロがなじむまで少し時間がかかるのかなあというような
感じがしました。
(ヨッシー) 2017/01/22(日) 10:15


>マクロがなじむまで少し時間がかかるのかなあというような

それは、ないでしょう。
気にはなりますが、次の課題です。

>For i = 214 To 218 Step 2

これの 214 についても学年によって変わる件です。

で、214 は

>「出欠の記録」と書かれた結合セルの次の行です。

ということでしたので、
「出欠の記録」というセルを検索し
そのセルの行番号(Row)に1を足せば目的の数値になりますね。

「書式」シートで、「出欠の記録」を検索する操作を実施してみてください

ちゃんと、検索できたら、その操作を「マクロの記録」です。

 ----

関係ないですが
>For m = 4 To データWS.Cells(Rows.Count, 2).End(xlUp).Row

の前に、次の1行を追加してみてください。
今より快適に動作すると思います。

Application.ScreenUpdating=True

(マナ) 2017/01/22(日) 10:54


↑ 最後間違えました。Trueでなく、Falseでした。

(マナ) 2017/01/22(日) 11:18


マナさん、次のような簡単なマクロが記録されましたけれど、
これでいいですか?

Sub Macro1()
'
' Macro1 Macro
' 出欠の記録の検索
'

'
End Sub

セルは$C$210でした。
(ヨッシー) 2017/01/22(日) 11:46


そんなはずはないでしょう?
もう一度記録してみてください。

(マナ) 2017/01/22(日) 11:55


わたしも試してみてわかりました。
「すべて検索(I)」だと記録されませんでしたので、「次を検索(E)」で試してみてください。

(マナ) 2017/01/22(日) 12:37


マナさん、マクロが記録されました。

Sub Macro1()
'
' Macro1 Macro
' 出欠の記録の検索
'

'

    Cells.Find(What:="出欠の記録", After:=ActiveCell, LookIn:=xlFormulas, LookAt _
        :=xlPart, SearchOrder:=xlByRows, SearchDirection:=xlNext, MatchCase:= _
        False, MatchByte:=False, SearchFormat:=False).Activate
End Sub

(ヨッシー) 2017/01/22(日) 13:05


Cells.Find(ここに検索条件が色々ありますが無視).Activate

こんな感じに記録されました。
Cellsは全てセルという意味で、検索する範囲です。
Cellsを検索(Find)して、見つかったセルをActivateしています。

これに、どのシートのCellsを検索するかを追加して
()の中の検索条件は、今回必要なものだけにして、
検索結果の行番号(Row)を表示するように変更したのが
次のマクロです。

 Sub 検索1()
    Dim 書式WS As Worksheet

    Set 書式WS = Worksheets("書式")

    MsgBox 書式WS.Cells.Find(What:="出欠の記録").Row

 End Sub

で、実際にどこに貼り付けたいかというと
その1行下なので、1を足します。

 Sub 検索2()
    Dim 書式WS As Worksheet
    Dim 貼付行 As Long

    Set 書式WS = Worksheets("書式")

    貼付行 = 書式WS.Cells.Find(What:="出欠の記録").Row + 1

    MsgBox 貼付行

 End Sub

ちょっと難しいかも。わかりますか?

(マナ) 2017/01/22(日) 13:45


マナさん、理解できたのですが、表示されるのが「211」でした。
「出欠の記録」は次のようになっていますが、このマクロでいいのでしょうか。

210 出欠の記録(210と211の結合セル)
211
212 項目 授業(212と213の結合セル)
213   
2141学期 (214,9) (214と215の結合セル)
215
2162学期 (216,9) (216と217の結合セル)
217
2183学期 (218,9) (218と219の結合セル)
219
 罫線で区切られています。
 
(ヨッシー) 2017/01/22(日) 15:16


>貼付行 = 書式WS.Cells.Find(What:="出欠の記録").Row + 1

それならば、1じゃなく、2を足せばよいだけですよね。

貼付行 = 書式WSのCellsを検索範囲で検索されたセルの行番号に2を足す

この貼付行を使って、全学年に使えるマクロに書き換えてみてください。

(マナ) 2017/01/22(日) 15:32


マナさん、自信がありませんが、次のように書いてみました。

Sub test8()

    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
   Dim 書式WS As Worksheet
    Dim m As Long, n As Long, 貼付行 As Long, j As Long
    Set データWS = Worksheets("出席簿")
  Set 書式WS = Worksheets("書式")

    Application.ScreenUpdating = False
    For m = 4 To データWS.Cells(Rows.Count, 2).End(xlUp).Row
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3
    For 貼付行 = 書式WS.Cells.Find(What:="授業").Row + 2 To 書式WS.Cells.Find(What:="授業").Row +6 Step 2
        For j = 9 To 39 Step 6
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(貼付行, j))
            n = n + 1
        Next
    Next
 Next

End Sub

これで実行してみます。

(ヨッシー) 2017/01/22(日) 17:05


マナさん、実行してみました。どこにも値が入りませんでした。

デバックすると、「 ActiveSheet.Paste Link:=True」のところに黄色いマーカーが
ひかれました。

(ヨッシー) 2017/01/22(日) 17:18


ごめんなさい
"出欠の記録"で検索し、+4でしたね。

↓これはなんだったのでしょうか。

>「書式」シートについて
>「出欠の記録」と書かれた結合セルの次の行です。

(マナ) 2017/01/22(日) 17:29


マナさん、手直しして、最終的なマクロは次で、実行したらきちんと値が入りました。

Option Explicit
Sub リンク貼り付けマクロ(コピーセル As Range, 貼り付けセル As Range)

    コピーセル.Worksheet.Select
    コピーセル.Select
    Selection.Copy
    貼り付けセル.Worksheet.Select
    貼り付けセル.Select
    ActiveSheet.Paste Link:=True

 End Sub

Sub test8()

    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
     Dim 書式WS As Worksheet
    Dim m As Long, n As Long, 貼付行 As Long, j As Long
    Set データWS = Worksheets("出席簿")
    Set 書式WS = Worksheets("書式")

    Application.ScreenUpdating = False
    For m = 4 To データWS.Cells(Rows.Count, 2).End(xlUp).Row
    Set 氏名WS = Worksheets(データWS.Cells(m, 2).Value)
    n = 3
    For 貼付行 = 書式WS.Cells.Find(What:="出欠の記録").Row + 4 To 書式WS.Cells.Find(What:="出欠の記録").Row + 8 Step 2
        For j = 9 To 39 Step 6
            Call リンク貼り付けマクロ(データWS.Cells(m, n), 氏名WS.Cells(貼付行, j))
            n = n + 1
        Next
    Next
 Next

End Sub

(ヨッシー) 2017/01/22(日) 17:52


for〜next は繰り返し処理ということは理解していますよね。
その中で、
>書式WS.Cells.Find(What:="出欠の記録").Row + 4
を書くと、何回も検索します。

検索は、for〜nextの前で1回だけに書き直してください。

(マナ) 2017/01/22(日) 18:11


マナさん、つまり次のようにするということですか。

貼付行 = 書式WS.Cells.Find(What:="出欠の記録").Row + 4.Select
For 貼付行 =Selection To Selection+4 Step 2

違いますか?

(ヨッシー) 2017/01/22(日) 18:47


まだ、見れば何となくわかるけど
自分では書けないといった感じでしょうか。

 Sub test9()
    Dim データWS As Worksheet
    Dim 氏名WS As Worksheet
    Dim 書式WS As Worksheet
    Dim コピー行 As Long, コピー列 As Long, 貼付行 As Long, 貼付列 As Long
    Dim 貼付開始行 As Long

    Set データWS = Worksheets("出席簿")
    Set 書式WS = Worksheets("書式")

    貼付開始行 = 書式WS.Cells.Find(What:="出欠の記録").Row + 4

    Application.ScreenUpdating = False

    For コピー行 = 4 To データWS.Cells(Rows.Count, 2).End(xlUp).Row

       Set 氏名WS = Worksheets(データWS.Cells(コピー行, 2).Value)
       コピー列 = 3

       For 貼付行 = 貼付開始行 To 貼付開始行 + 4 Step 2

           For 貼付列 = 9 To 39 Step 6
               Call リンク貼り付けマクロ(データWS.Cells(コピー行, コピー列), 氏名WS.Cells(貼付行, 貼付列))
               コピー列 = コピー列 + 1
           Next
       Next
    Next

 End Sub

(マナ) 2017/01/22(日) 19:23


マナさん、教えていただいたこのマクロが完成形ですか。それとも、漢字表記のところは、
アルファベット小文字に置き換えてもいいということですか。
(ヨッシー) 2017/01/22(日) 20:58

期待通り動くなら、これで完成です。

変数名は、ヨッシーさんがiを変更されたので、
他の変数もあわせて、日本語にしただけで
自分のわかりやすいように修正して構いません。

「リンク貼り付けマクロ」のほうも
少し修正する予定でしたが、
これ以上は、説明しても、消化しきれないかと思います。
エラーになるわけでもありませんので
今回は、このままにしておきましょう。

使っていて問題がでたら、また質問してください。

(マナ) 2017/01/22(日) 21:24


マナさん、長い間ありがとうございました。マクロについて基本的な
知識もない私に、「マクロは自分で書いて」と諭してくれ、それこそ
貴重なマナさんの時間を指導のために使わせてしまい申し訳なく思っています。
マナさんからいくつもの課題が提示され、それについて自分なりに考え、
つたないマクロを書いては、手直ししていただき、とても勉強になりました。
重ねてお礼申し上げます。
教えていただいたマクロは、職場で有効に使わせていただきます。
使っていたら問題が出たら、また投稿して質問させていただきます。

これを機会に、マクロについて基本から学んでみようかと思いますが、
マナさんおすすめの何か役立つ本等はありますでしょうか。
それとも、やはり、何か学習会のようなものに参加して身に着けた方が
いいのでしょうか。
(ヨッシー) 2017/01/23(月) 03:35


わたしは、自分で本を買ったことがないのですが
マクロの勉強を始めたとき使ったのは、

同じ職場に、わたしより先に勉強しようとして
あっさり挫折しちゃった同僚がいて、
もう自分はいらないから使ってとわたされた
短いマクロばかりを集めた
「3行マクロ」集みたいなものでした。
で、それを丸暗記するつもりで
毎日、書き写す作業を繰り返していました。

非常に効率の悪い勉強方法かもしれませんが
もともと理屈から入るタイプではないので
わたしにとっては、たぶんこのやり方で正解だったと思います。

でも、その同僚にとっては、
その本は役にたたなかったわけで

マクロに限らずですが、
どんな勉強の仕方がよいかなんて
人によって違うと思いませんか。

なので、おすすめの本というものはないです。

わたしは、掲示板で質問なんて考えもしませんでしたが
本で最低限の知識が得られれば、
あとは掲示板で質問したり
ネット検索したりするほうが効率的な気がします。

そう考えると、とりあえず安くてもいいから、
ぱっと見、簡単なことばかりの本を
選べばどうでしょうか。

おすすめはありませんが、
高くて、難しいそうなものは
おすすめしません。

(マナ) 2017/01/23(月) 22:18


マナさん、ありがとうございました。少しずつがんばってみます。
長い間にわたるご指導は忘れません。また、困ったら、掲示板に
投稿します。
マナさんのご活躍と、健康を願っています。
本当にありがとうございました。
(ヨッシー) 2017/01/24(火) 04:25

コメント返信:

[ 一覧(最新更新順) ]


YukiWiki 1.6.7 Copyright (C) 2000,2001 by Hiroshi Yuki. Modified by kazu.