[[20181013144638]]　『マクロごとシートをコピー・複製したい』（reso）　ページの最後に飛ぶ

『マクロごとシートをコピー・複製したい』（reso）

一つのシートの中で、ゴールシークを自動で実行するマクロ(ボタンを押すと計算実行)を作成しました。

このシートを計算書の標準フォームとして使用することを考えています。

複数ケースの計算を実行する場合には、この標準フォームをシートごとまるっとコピーして使いたい。と思っています。
計算バターンが１０個あれば、シートを１０個作成するといった感じです。
この計算結果を、同じブック内の他シートにリンクさせたいため、あくまでも同一ブック内に存在させたいのです。

現在シートをコピーすると、マクロのボタンごとコピーできるのですが、押しても、コピー元の別シート側で計算が実行されてしまいます。

複製したシートの中にあるマクロボタンに対して、再度計算するマクロを登録すれば、実行はできるのですが、そうすると、シート複製事にボタンに対してマクロの割り当てる作業が発生し、時間的なロスとなるため、その手間を省きたいです。

現在マクロの実行式・ブログラムは、標準モジュールのところではなく、該当するシートのところに記載しています。

どのようにすれば、マクロごとシートをまるっとコピーさせることができますでしょうか？

＜使用 Excel：Excel2007、使用 OS：Windows10 ＞

 ＞現在マクロの実行式・ブログラムは、標準モジュールのところではなく、該当するシートのところに記載しています。

 内容を理解し切っていないですが・・

 本体は標準モジュールに書いて、
 シートのところには、そのプログラムを呼出すコードだけ書けばいいんじゃないですか？

（半平太） 2018/10/13(土) 15:46

プログラムを呼び出すコードとは、どのようなプログラムを書けばよいのでしょうか？
（reso） 2018/10/13(土) 15:53

 ＞シートのところには、そのプログラムを呼出すコードだけ書けばいいんじゃないですか？

 ・・じゃなかった・・　ごめんなさい　m(__)m

 「マクロのボタン」にその標準モジュールに書いたプログラムを登録すればいい、と思われますけど？

（半平太） 2018/10/13(土) 15:54

 どんなコードなのか、アップして頂けませんか？

 データのあるシート と 結果出力シートが別なので、
 何か工夫を入れる必要がありそうな気がしてきました。

（半平太） 2018/10/13(土) 16:15

Range("cj113").Value = Range("cj111") * 0.9
Range("cj114").GoalSeek Goal:=Range("cj76"), ChangingCell:=Range("cj113")
Range("cj123").Value = Range("cj111") * 0.9
Range("cj136").GoalSeek Goal:=0, ChangingCell:=Range("cj123")

Range("cv113").Value = Range("cv111") * 0.9
Range("cv114").GoalSeek Goal:=Range("cv76"), ChangingCell:=Range("cv113")
Range("cv123").Value = Range("cv111") * 0.9
Range("cv136").GoalSeek Goal:=0, ChangingCell:=Range("cv123")

Range("dh113").Value = Range("dh111") * 0.9
Range("dh114").GoalSeek Goal:=Range("dh76"), ChangingCell:=Range("dh113")
Range("dh123").Value = Range("dh111") * 0.9
Range("dh136").GoalSeek Goal:=0, ChangingCell:=Range("dh123")

こんなかんじです
（reso） 2018/10/13(土) 16:21

ただ、このマクロを色々なブックに挿入していきたいので、プログラム自体は、標準モジュールではなく、個別シート内に記載する必要があると思うのですが…。違いますか？
（reso） 2018/10/13(土) 16:36

 ＞ただ、このマクロを色々なブックに挿入していきたいので、
 ＞プログラム自体は、標準モジュールではなく、
 ＞個別シート内に記載する必要があると思うのですが…。違いますか？

 それはどちらのモジュールでも対応可能と思いますが、
 下に述べた結論らしきものをご参照ください。

 ＞現在シートをコピーすると、マクロのボタンごとコピーできるのですが、
 ＞押しても、コピー元の別シート側で計算が実行されてしまいます。

 マクロボタンへの登録マクロ名が「Sheet1!GOAL」とかになっているからでしょうね。

 なんか、今のコードを標準モジュールに引っ越して、
 そっちのマクロを登録するだけ(Sheet1!が付かない状態）で
 いいような気がしてきましたけども、それで解決しないですか？

（半平太） 2018/10/13(土) 17:47

結論が出ているようですので、お邪魔虫かもしれませんが。
　
(1)シートの数が多数に昇るのなら、
Formコントロールのボタンにして、標準モジュールにマクロを書けばよいですね。
相手にするのはボタンのあるシートだけのようなので、管理するコードが1つだけで済みますから、
そのほうが効率は良いはずです。
(2)2,3個程度のシートなら、シートモジュールでもよいかも。
シートのコピー（全体を選択してとかじゃなく）をすればマクロも同時にコピーされますよね。
（γ） 2018/10/13(土) 17:57

アドバイス頂いた内容試してみます。確認後アップします。
（reso） 2018/10/13(土) 18:00

標準モジュールにマクロを書き写したところ、同一ブック内でのコピーうまくいきました。マクロも使えました。アドバイスありがとうございます。

それに引き続きの質問です。

標準モジュールにマクロを移動した結果、別ブックにシートコピーした時には、マクロが紐づかなくなります。

各シートにマクロを書くと、別ブックへのコピー時にはマクロが引き継がれますが、最初の質問の通り、同じブック間でコピーすると、マクロが紐継がれなく。

それぞれにメリット、デメリットがありますが、この両方を満足する方法はないものでしょうか？

同一シート内でのコピー、別シートへのコピーを両方ともに実行したいのです。
（reso） 2018/10/13(土) 20:50

＞シート複製事にボタンに対してマクロの割り当てる作業が発生し、時間的なロスとなるため、その手間を省きたいです

マクロで割り当てればよいのでは無いでしょうか？
シートモジュール
Sub test()

    Range("A1").Value = Me.Name
End Sub

Private Sub Worksheet_Activate()

    Me.Shapes(1).OnAction = Me.CodeName & ".test"
End Sub

（kazuo） 2018/10/13(土) 21:44

アドバイスありがとうございます。

このプログラムはどういう内容なのでしょうか？

意味が理解できず…。

自分のファイルにどのように反映すればよいのかがわからなくて…。
（reso） 2018/10/13(土) 21:54

 ＞現在シートをコピーすると、マクロのボタンごとコピーできるのですが、
 ＞押しても、コピー元の別シート側で計算が実行されてしまいます。

 あれー？　話の展開が、上の前段部分と矛盾してないですか？

 ＞標準モジュールにマクロを移動した結果、
 ＞別ブックにシートコピーした時には、マクロが紐づかなくなります。

 そんなハズないと思います。

 コピー元のシートのボタンに標準モジュールのマクロを登録してから、テストを開始されましたか？

（半平太） 2018/10/13(土) 23:41

標準モジュールにマクロを登録し、そのマクロを使用するように設定し保存をしました。

そのあとで、そのシートを新しいブックにコピーすると、

新しいブックの中には、マクロが存在しなかったです。

もともと、そこを改善するために、標準モジュールではなく、シートのほうにマクロを登録していたんです。
（reso） 2018/10/14(日) 00:07

 ようやく事情が分かりました。
 新しいブックは、ブックごとコピーすると思っていました。m(__)m

 でしたら、お手数ですが・・・
  元シート(Sheet1と仮定）のモジュールにコードを戻していただいて、

 マクロボタンの登録名を「Sheet1!excuteLocal」にして、
 そのプロシージャ「excuteLocal」を以下の通りにする、でどうでしょうか？

 Sub excuteLocal()　’追加するプロシージャ
     ActiveSheet.goal
 End Sub

 Sub goal()　’今までのプロシージャ名がgoal と仮定
  　　Range("cj113").Value = Range("cj111") * 0.9 
 　　Range("cj114").GoalSeek Goal:=Range("cj76"), ChangingCell:=Range("cj113") 
     ：：
 　　：：
 End Sub

（半平太） 2018/10/14(日) 00:40

 話をよく分かってなかったら申し訳ありません。

 単にActiveXコントロールのコマンドボタンに、マクロを記述しておけば良いだけのような気がするんですが、
 以下では何か不都合があるんですか？

 Private Sub CommandButton1_Click()

    Range("cj113").Value = Range("cj111") * 0.9 
    Range("cj114").GoalSeek Goal:=Range("cj76"), ChangingCell:=Range("cj113") 
    Range("cj123").Value = Range("cj111") * 0.9 
    Range("cj136").GoalSeek Goal:=0, ChangingCell:=Range("cj123")

    Range("cv113").Value = Range("cv111") * 0.9 
    Range("cv114").GoalSeek Goal:=Range("cv76"), ChangingCell:=Range("cv113") 
    Range("cv123").Value = Range("cv111") * 0.9 
    Range("cv136").GoalSeek Goal:=0, ChangingCell:=Range("cv123")

    Range("dh113").Value = Range("dh111") * 0.9 
    Range("dh114").GoalSeek Goal:=Range("dh76"), ChangingCell:=Range("dh113") 
    Range("dh123").Value = Range("dh111") * 0.9 
    Range("dh136").GoalSeek Goal:=0, ChangingCell:=Range("dh123")

 End Sub

（sy） 2018/10/14(日) 06:10

＞自分のファイルにどのように反映すればよいのかがわからなくて
私のは、
ボタンがなんなのか解らないので、Shapes(1)とし、
プロシージャー名も解らないので、testとしてみたのです。
ボタンとプロシージャー名とを実際のものに変えて適用していただければ良いと思います。
（kazuo） 2018/10/14(日) 17:47

SY様

ご指摘のように、フォームコントロールボタンではなく、ActiveXのコマンドボタンとして
マクロを登録したところ

シートコピーで、そのまま、マクロもコピーされ、そのまま実行することが出来ました。

ありがとうございました。

問題全て解決です。

（resort） 2018/10/16(火) 15:20

コメント返信：

[ 一覧(最新更新順) ]

[[20181013144638]] 『マクロごとシートをコピー・複製したい』（reso） ページの最後に飛ぶ

[[20181013144638]]　『マクロごとシートをコピー・複製したい』（reso）　ページの最後に飛ぶ