シート名が変化するシートをマクロで選択する方法は？

質問

１．シート名「フォーマット」にはデータ入力用定型フォーマットがある。
２．データを入力する際は「フォーマット」シートは残しておき、「フォーマット」シートをコピーしてシート名を「1000」に変更する。
　　次回データ入力する際は同様にシートコピー後、シート名「1001」「1002」という具合に変更して使用する。
３．シート「1000」をアクティブにし、マクロ登録をしてある図形ボタンをクリックして、マクロ実行。
　このマクロは別途作成済で、事前に用意しておいたシート「作業用」にシート「1000」のデータを貼り付け、データ変換処理を行います。
４．変換処理後のデータをシート「1000」に貼り付ける。

という一連の流れの中で、「フォーマット」シートをコピーして使用する関係で、シート名が変化します。
上記４の段階でシート「1000」を選択するためにはどのようなマクロ記述をすればよいでしょうか？

回答No.5

mt2008 · ベストアンサー

No.4です。
外れていなくて良かった。
「hogehoge」は、メタ構文変数と呼ばれるもののひとつで、要するに「仮の名前」です。
hogeやhogehogeが何故かよく使われています。
この場合、作成するマクロの名前をhogehogeと仮定しています。

作業用シートから引数で指定されたシートに値を貼り付けるマクロの例を挙げておきます。
この例の場合、「作業用」シートのC1セルの値を引数で指定されたシートのC1セルに値だけ貼り付けています。No.4で示したSampleと組み合わせてお試しください。

Sub hogehoge(sSheetname as String)
　Sheets("作業用").Range("C1").Copy

　Sheets(sSheetname).Range("C1").Select
　Selection.Pastespecial Paste:=xlValues
End Sub

お礼

早々に追加ご回答いただき、ありがとうございます。
教えていただいた方法で無事に目的のマクロを完成することができました！
つたない説明だったにもかかわらず、私の意図する所をくみ取って、的確にズバリご回答いただいて本当に感謝感謝です。
mt2008さん、ありがとうございました。

回答No.6

mitarashi

#1です。
考え方を述べただけで、そのまま使えるコードを呈示した訳ではありません。「シート名」を記録しておく例も呈示されていますが、ここではオブジェクトとしてのシートを記録？しています。
複写したシート上のボタンから実行するマクロに少しつけ加えて、
Sub test()
Dim sh As Worksheet

Set sh = ActiveSheet
sh.Range("a1").Copy Sheets("作業用").Range("a1")
'作業用シートでの秘密の処理を記述
Sheets("作業用").Range("a2").Copy sh.Range("a2")
End Sub
とすればよろしいかと。
Copyの所も簡単に記してありますので、値複写がご希望の場合などはアレンジなさってください。

お礼

追加のご回答をいただき、ありがとうございます。
おかげさまでNo.5でmt2008さんがご回答いただいた内容で無事完成することができました。
mitarashiさんの方法もほぼ同じようですね。
色々と勉強になりました。どうもありがとうございました。

回答No.4

mt2008

質問を取り違えていたらごめんなさい。
データを貼り付ける処理は、「作業用」シートから引数で指定されたシートにデータを貼り付けるマクロを作っておいて、3で起動するマクロを以下の様なマクロに変更し、つくって置いたマクロにシート名を渡すってのはどうでしょう？

Sub Sample()
Dim sSheetname As String
sSheetname = ActiveSheet.Name 'ボタンが押されたシート名を記憶
Call Macro3 'いままで3で呼ばれていたマクロ
Call hogehoge(sSheetname) '「作業用」シートからこのシートにデータを貼り付けるマクロ

End Sub

お礼

ご回答ありがとうございます。
書いて頂いた、ボタンが押されたシート名を記憶→いままで3で呼ばれていたマクロ→「作業用」シートからこのシートにデータを貼り付けるマクロ、という流れで合っていると思います。

しかし、私もまだまだ素人なもので疑問ばかりですみませんが、「引数で指定されたシートに～」というところは具体的にどのような記述になるのでしょうか？
また、hogehogeの部分はこのままでも大丈夫なのでしょうか？
初めて見るものだったので、どんな意味があるのかもよくわかりませんでした。ついでに教えていただけるとありがたいです。

今のところ、期待する答えに最も近い気がしますので、ぜひぜひまた追加で教えてください。
よろしくお願いします。

回答No.3

imogasi

質問文はもう少し要点を絞って表現できないのかな。
シートの指定は、VBAでは、下記しかない
（１）名前を指定　Sheets("名前")
（２）位置を指定　Sheets(2)のように
（３）Activeシート
（４）previousなど
ーー
この中で位置は不安定（操作者が質問者以外だと動かす恐れが有る）。
名前が都度変わるなら、どうしたら確実に捉えられるかは、質問者しかわからない。その辺は知恵を出して、守れるルール！を質問者が決めて、こうしたいが、コードは？と質問すべきだ。これは質問者の問題で、実行可能なことのタイプは質問者が決める（知っている）ことなんですよ。そしてそのルールを守ることも。
本当は自動でやらないで、その都度処理するシート名を操作者に指定させるのが本筋。
ーーー
コピーした名前は同一ではありえないし、だんだん増えて付番されるなら、最高値の番号のシートを処理するのはどうだろう。
ーー
全角数字でシート名が増やされるものとする。半角の場合は下記コードを少し変えればよい。
その際数字として考えて最大のシート名を捉える例。
Sub test01()
Dim sh As Worksheet
fst = "y"
For Each sh In Worksheets
If IsNumeric(sh.Name) = True Then
If fst = "y" Then
n = StrConv(sh.Name, 8)
ms = Sheets(1).Name
fst = "N"
End If
n = StrConv(sh.Name, 8)
If m < n Then
m = n
ms = sh.Name
End If
End If
Next
MsgBox ms
End Sub

お礼

ご回答ありがとうございます。
説明が下手でうまくお伝えできず、申し訳ありません。

下の回答の方もご参照いただきたいのですが、シート名は入力者が指定します。
また、決して連番という訳でもなく、シート名の数字が最も大きいシートを選択するという訳でもありません。
例えば、1000→1001→1002と連番でシート名を付けたが、1000に戻ってマクロ実行する、ということも考えられます。
ですから、「マクロ実行した時のシート名の所に戻ってくる」際のシートの選択方法がわからないのです。

ぜひまた追加で教えてください。
よろしくお願いします。

回答No.2

_Kyle

ご質問の趣旨を取り違えていましたらすみません。

「1000」,「1001」,「1002」……というふうに、
入力処理の都度シートを増やしていくブックについて、
「シート名の数字が最も大きいシートを選択するには？」
ということかと思ったんですが…。
------------------------------------------------
動作の概要
　ブック中のシートを順に回して、
　シート名が数字で、かつその数字が最大であるシートを見つけ、
　そのシートをオブジェクト変数myShtに格納する。
　さらに、そのシートを選択し、シート名を表示する。

Sub Sample()
　For Each tpSht In Worksheets
　　If IsNumeric(tpSht.Name) Then
　　　If CLng(tpSht.Name) > myNam Then
　　　　myNam = tpSht.Name
　　　End If
　　End If
　Next tpSht
　Set mySht = Worksheets(myNam)
　mySht.Select
　MsgBox "最新のシートは" & myNam & "です"
End Sub
------------------------------------------------
あるいは（処理の流れから想像するに）
最新のシートは「フォーマット」シートの右隣にありそうに思われるので、
単に「フォーマット」シートの右のシートを選択すれば良いのかも。

動作の概要
　「フォーマット」シートの右にあるシートをオブジェクト変数myShtに格納する。
　さらに、そのシートを選択し、シート名を表示する。

Sub Sample2()
　Set mySht = Worksheets("フォーマット").Next
　mySht.Select
　MsgBox "最新のシートは" & mySht.Name & "です"
End Sub

以上ご参考まで。

お礼

ご回答ありがとうございます。
下でも回答していますが、「シート名の数字が最も大きいシートを選択するには？」という訳ではありません。
ご説明するのも難しく、やりたいことをご理解いただくには何回かのやりとりが必要かとも思っています。
説明が下手で本当に申し訳ありません。

下と同じ説明になりますが・・・
シートコピー後のシート名は手入力で、しかも入力者によって違うことになるのですが、
基本的には”4桁数字”で、頭1桁目で入力者を分けることとして、
入力者Ａは「1000→1001→1002・・・」、
入力者Ｂは「2000→2001→2002・・・」、
入力者Ｃは「3000→3100→3200→3101→3001・・・」、
だったり不規則で、必ずしも1ずつ増える名前の付け方でない入力者もいる、
という想定をしています。

また、流れ３で用意してある別のマクロを実行するとシート「作業用」で一旦データ変換されたものが出来上がります。
それを流れ３で実行した時のシート名（前述のように固定ではありません）の所に戻ってきて貼り付けしたい、
ということになります。
この際のシートの選択方法がわからなかったので、ご質問させていただいた次第です。

ぜひまた追加で教えてください。
よろしくお願いします。

回答No.1

mitarashi

こういう事でしょうか？
Sub test()
Dim sh As Worksheet

Sheets("format").Copy Before:=Sheets(1)
Set sh = ActiveSheet
sh.Name = "1000"
sh.Range("a1").Copy Sheets("作業用").Range("a1")
Sheets("作業用").Range("a2").Copy sh.Range("a2")
End Sub

お礼

ご回答ありがとうございます。
テストしてみましたが、これだとシート「format」から1回目のコピーをした時に
コピーしたシート名が「1000」に自動的になってしまいます。
また、2回目にシート名「1001」にしたい時には対応できないようです。

シートコピー後のシート名は手入力で、しかも入力者によって違うことになるのですが、
基本的には”4桁数字”で、頭1桁目で入力者を分けることとして、
入力者Ａは「1000→1001→1002・・・」、
入力者Ｂは「2000→2001→2002・・・」、
入力者Ｃは「3000→3100→3200→3101→3001・・・」、
だったり不規則で、必ずしも1ずつ増える名前の付け方でない入力者もいる、
という想定をしています。
答えに近いかと一瞬思えたのですが、もう少しという感じです。
ぜひまた追加で教えてください。
よろしくお願いします。