エクセルマクロ シート名の指定と変更について

質問

いつもお世話になります。
現在、このような作業をしています。
１．前月と前々月の資料（ファイル名 "資料" シート名 "404" "405"）を、計算用シート（ファイル名 "分析" シート名 "計算"）に転記する。（手動）
２．月次の分析資料を作る。（マクロ起動）
３．２で作成した資料を、シート名 "000" に転記する。（マクロ）
４．次回の準備として、新しいシートを挿入する。（マクロ終了）
５．３で作成されたシート名 "000" をシート名 "406" に変更する。（手動）
６．５のシート名"406"を、ファイル名"分析"から、ファイル名"資料"に移動する。（手動）
７．４で挿入されたシート名を "000" と書き換える。（手動）
以上の一連の作業の中で、（手動）の部分をマクロで出来るようにしたいのですが、構文がわかりません。
といいますのは、シート名は、各年月の略号をあらわしており、毎月毎月、参照するシート名が変わるのです。（逆に言えばマクロの中で参照シート名を固定できない）
そこで、今月のシート名ならば、"406"となりますので、その数字を入力（ファイル名 "分析" シート名 "計算" のセルＡ１に入力）してやれば、必要なシートが、前月と前々月、"405" "404" と判別できるので、何か方法があると思うのですが、構文がうまくできないので、たずねてみました。
補足しますと、現在、仮の置場的に機能しているシート "000"は、不要になると思います。
どうかよろしくおねがいします。
ちなみにエクセル２００３、ＯＳ＝ＸＰです。

回答No.8

papayuka · ベストアンサー

#5です。

簡単な補足です。

Dim sBook As Workbook

は変数の宣言をしている部分です。変数と言うのは器と考えて下さい。この場合「sBook」は 「Workbook型」の器である事を宣言しています。

例ではユーザーフォームのイニシャライズで開いたブックを変数「sBook」にセットしています。

fName = ThisWorkbook.Path & "\資料.xls"
　　　　　↑マクロを記述したブックのパスに「\資料.xls」を加えたもの
　　　　　　マクロを記述したブックのパスが「C:\My Document」だとすると
　　　　　　「C:\My Document\資料.xls」
Set sBook = Workbooks.Open(fName)
　　　　　↑「C:\My Document\資料.xls」を開いて「sBook」にセット

これによって同じモジュール内の別の Sub ～ End Sub 内で「sBook」を「Workbooks("資料.xls")」の変わりとして扱えます。

「実行時エラー４２４　オブジェクトが必要です。」は「sBook」に開いたブックがセットされていない状態でブックをクローズしようとした事によって起こっていると思います。

-----------
#6さんへ

「1枚目のシートが存在しなくなっている所為」
「分析というファイルにSheets(1)が存在しない」

これは誤りです。
Sheets("Sheet1")のように引数をシート名で指定した場合は有り得ますが、Index で指定した場合は「1」であれば一番左のシートを表します。（シートを非表示にしてあっても）
ブック内にシートが1枚も存在しない事は無いのでSheets(1)が無くなる事はありません。

Sub Test()
　MsgBox "最初は " & Sheets(1).Name & _
　　　　 " で最後は " & Sheets(Sheets.Count).Name
End Sub

を実行すると必ず一番左にあるシート名と一番右にあるシート名が表示されます。

お礼

なるほど。。。
そういうこと（一番左と右）だったのですね。
これもいろいろ役立ちそうな知識で、すごい得した気分です。
いろいろ教えていただいて本当に助かりました。
また困ったことが出てくると思いますので、その節はご教示をたまわりたく思います。
本当にありがとうございました！

回答No.7

papayuka

#5です。

UserFormモジュールの先頭にコメント以外のコード又はコードの雛型があるのでは？

Dim sBook As Workbook

この記述が UserForm モジュールの一番上に必要です。

'UserForm ここから---------------------------------------------------
Dim sBook As Workbook　'← これを UserForm モジュールの一番上に
Private Sub UserForm_Initialize()
　　　・
　　　・

お礼

どうもありがとうございます！
ご指摘のとおりでした。
UserForm の上に CommandButton の構文がありましたので、Dim sBook As Workbook　が、 CommandButton の構文の枠内に入ってしまって、コンパイルエラーが出てしまうので、勝手に置き換えていました。
それで、UserFormの構文を一番上において、ご指摘の順番で記述すると、うまく出来ました。
これは大変有意義なプログラムです！
複数のファイルを利用しやすくなるので、大きくなりすぎたファイルを分割して、必要に応じて必要なファイルだけ立ち上げる操作が可能になりそうなので、大変喜んでいます。
本当にどうもありがとうございました！

回答No.6

miwaki

#3回答補足
分析.xls　は、シートの削除／追加が繰り返されてますよね。そのため1枚目のシートが存在しなくなっている所為だと思います。なにぶん私もよくわからず記録型マクロで作成したものですから・・・・。
従って、分析というファイルは新規に作成して、Sheet1を絶対削除しないようにすればよろしいのではないかと思います。

お礼

何度もありがとうございます。
補足しましたように、再度つくりなおしたときに、その作り直した方は、sheet1 を残したにも関わらず、同じ結果（同じエラーが出た）ものでしたが、なぜか、別のＰＣではちゃんと動いた。更に＃５さんのご指摘（拡張子をつける）により、元のＰＣでもちゃんと動く、という結果になりました。
何にしても、助かりました。
どうもありがとうございました。

回答No.5

papayuka

横から失礼します。

> Sheets(TextBox1.Value).Copy before:=Workbooks("分析").Sheets(1)

多分ですが、実行環境の拡張子の表示・非表示の違いでエラーが発生しているのではないでしょうか？
("分析")を("分析.xls")としてみては？

> UserForm1.Hide

Hide は非表示にするだけなので不用になった UserForm は Unload Me とした方が良いと思います。

-----
以下、ご希望の直接的な回答にはなっていないかも知れませんが参考になれば幸いです。

1.新規ブックを作成し、ファイル名は何でも良いので何処かのフォルダに必ず保存する。
（仮に A.xls とする）
2.A.xlsと同じフォルダ内に「資料.xls」のコピーを置く（テスト用）
3.A.xls に UserForm を作成し、CommandButton を3個、ComboBox を1個、TextBoxを1個 置く
4.A.xls の UserForm モジュールにサンプルをコピペする
5.A.xlsに標準モジュールを追加し、サンプルをコピペする。

'UserForm ここから---------------------------------------------------
Dim sBook As Workbook
Private Sub UserForm_Initialize()
Dim ws As Worksheet, fName As String
　fName = ThisWorkbook.Path & "\資料.xls"
　Set sBook = Workbooks.Open(fName)
　For Each ws In sBook.Worksheets
　　　ComboBox1.AddItem ws.Name
　Next ws
　ComboBox1.Value = ComboBox1.List(ComboBox1.ListCount - 1)
　TextBox1.Text = Format(Date, "yyyymm")
　CommandButton1.Caption = "転記"
　CommandButton2.Caption = "シート作成"
　CommandButton3.Caption = "終了"
End Sub

Private Sub CommandButton1_Click()
On Error Resume Next
sBook.Worksheets(ComboBox1.Value). _
　Range("A1:A15").Copy Destination:= _
　　ThisWorkbook.Worksheets(1).Range("A65536").End(xlUp).Offset(1, 0)
End Sub

Private Sub CommandButton2_Click()
On Error Resume Next
　Worksheets.Add(after:=sBook.Worksheets _
　　(sBook.Worksheets.Count)).Name = TextBox1.Value
End Sub

Private Sub CommandButton3_Click()
　sBook.Close
　Unload Me
End Sub
'UserForm ここまで---------------------------------------------------

'標準 ここから-------------------------------------------------------
Sub FormShow_Macro()
　UserForm1.Show
End Sub
'標準 ここまで-------------------------------------------------------

UserFormを表示すると「資料.xls」を開き、ComboBoxに「資料.xls」内の全シート名を追加します。
また、TextBoxに実行した月を「yyyymm」形式で表示します。

転記ボタンを押すと「資料.xls」の ComboBoxで指定したシートA1:A15を、「A.xls」の一番左のシートのA列にコピーします。

シート作成ボタンを押すと「資料.xls」の一番右にTextBoxで指定した名前でシートを追加します。

閉じるボタンを押すと「資料.xls」を閉じて、 UserForm を アンロードします。

補足

どうもありがとうございます。
まず、拡張子の問題ですが、".xls" 付けると、もとのＰＣでもちゃんと動くようになりました＾＾
それから、更に進化した方法をご教示いただき、まことにありがとうございます。
しかし、これがうまく動きません。
１．転記ですが、
エラー処理のおかげで、止まることは無いのですが、転記は実行されません。
２．シート作成ですが、
エラー処理のおかげで、止まることは無いのですが、シート作成は実行されません。
３．終了ですが、
実行時エラー４２４　オブジェクトが必要です。
とのエラーが出てしまいます。
これも、対処の仕方がわかりません。
申し訳ないですが、ご教示いただけますと幸いです。

回答No.4

tosi0000

こんにちわ。
多分完全に自動化できると思いますが、多少手入力を使ってみてはいかがでしょうか？
一つの案としてお話しいたします。

質問の内容をまとめますと下記になるかと思います。
1. [資料]404　→　[分析]前々月　へ複写
2. [資料]405　→　[分析]前月　へ複写
3. [分析]計算　マクロ実行
4. [分析]計算 → [分析]000 へ複写
5. [分析]新規シート追加
6. [分析]000 → [分析]406 へ名称変更
7. [分析]406 → [資料]406へ移動
8. [分析]新規シート　→ [分析]000　へ名称変更

多分文章から察しますとこのような手順で作業をされているかと思います。

この中で可変の項目は404,045,406となります。
そこで[分析]にシートをひとつ追加して、
仮に
[分析]パラメータとして、
A1 = 404
A2 = 405
A3 = 406
とセル値をセットします。

そして、マクロの可変部分シート名をこのセルより引くようにします。
翌月には、このシート名称のセル部分のみを変更して、実行するようにします。
つまり翌月は、
A1 = 405
A2 = 406
A3 = 407
でマクロ実行すね。
（また、セル表示を関数で自動で出しても良いと思います。）

さて、マクロの作り方ですが、1～8までをマクロ記録で連続して処理します。
そして、記録されたマクロの404,405,406の表示部分を抜き出して、パラメータシートのA1,A2,A3のセル値で入れ替えるようにします。
多少、記述で戸惑う部分もあるかも知れませんが、さほど難しくはないと思います。
ただし、フォルダ名称なども記録されますので、環境を変える場合には、もう一工夫必要と思われます。

以上、最も手っ取り早い方法かと思いますが、如何でしょうか？

お礼

どうもありがとうございます。
大変参考になります！
作業内容は、順序の多少の違いはともかく、よく把握いただいており、うれしく思います。
現在、
１．メール又はデータ元の会社のHPからエクセルシートに落とした元の資料を、計算しやすいように変換させる。あるいは並べ替える。
２．それを計算用シートに複写して計算させてから、ストックする。
と２段階でマクロを使っています。
といいますのは、なぜか、１段階の変換に間違いが生じることが時々あるのです。
そういうときは一旦ファイルを閉じてもう一回マクロを実行するとうまく出来ます。
理由はわかりませんがなぜかそういう結果が生じます。
理論上は、１，２の段階を一気にできるのですが、先のような不具合が生じることがあるので、フルオートにはしていません。
その辺の作業の効率化というか安定化させるためのヒントとして、toshiさんのアイデアは大変参考になりました。
かさねて御礼申し上げます。

回答No.3

miwaki

＞Sheets(TextBox1.Value).Copy before:=Workbooks("分析").Sheets(1)
ここで、「実行時エラー９　インデックスが有効範囲にありません」と表示されます。
いろいろ試してみたのですが、対処の仕方がわかりません。

この原因は、三つ考えられます。
ひとつは、分析というファイルの名前が違っている。
ふたつめは、分析というファイルにSheets(1)が存在しない。
みっつめは、TextBox1に入力した名前と同じ名前のシートが資料ファイルに存在しない。

特に、みっつめのファイル名については、半角か全角かも含めてよくご確認ください。マクロ2がうまく行くのにマクロ1がうまくゆかないというのは、みっつめの可能性が高いと思います。
私の作成したテストマクロでは問題なく動くのを確認して、マクロコマンドをコピーして回答に貼り付けています。

また、
＞ユーザーフォームは、コンボボックスを用いて、選択内容は、プロパティのRowSourceで規定しました。

とおっしゃってますが、コンポボックスを用いてというのがよくわかりません。私は、ツール>マクロ＞VisualBasicEditor＞挿入>ユーザーフォーム　でユーザーフォームを作成しています。

お礼

理由はよくわからないのですが、違うPCに両ファイルをコピーして実行すると、１，２共に正常に作動しました。
両方とも、miwakiさんの構文をそのまま複写して、TextBox を ComboBox に変更しただけで、後は全く同じものです。
ちなみに、どちらもエクセル２００３、OS=XPです。
さっきまでの苦労は何だったのか？と思いますが、そのような結果になりました。
しかし、ご教示いただいたおかげで作業をすすめられるようになりました。
本当にどうもありがとうございました。

補足

どうもありがとうございます。
ご指摘いただいた３つのケースですが、
１．ファイル名は間違いないです。
２．Sheets(1)とは、sheet1とは別物のことですか？
よくわからないので、同じ名前”分析”のファイルを作成して、Sheet1を残して、Sheet2を"計算"シートに（名前を変更して）して、試してみました。
３．同じ名前のシートは存在します。
　　miwakiさんの記述いただいたコマンドをそのまま　コピーして使っています。
　また、エラーの出ている前の行の、selectの段階で、ちゃんと選択したシートが表示されているので（たとえば"200404"を表示していますので）存在は間違いないハズです。
　ただ「ハズ」といった根拠ですが、この入力された「200404」が、「数字」として認識されているのか「文字列」として認識されているのか、逆に言うと、どちらで認識されるべきなのか、その辺で疑問が残っています。
ちなみに後述、コンボボックスに読み込ませる「200401」などの数字は、文字列ではなくて数字の扱いです。
　次に、コンボボックスの話ですが、私の言葉足らずだったかもしれません。
ユーザーフォームの作成経緯は、ご指摘のとおりですが、TextBoxのかわりに、ComboBoxを用いて、更に、入力の手間を省くために、あらかじめ用意した「200404」などの数字を、ComboBoxのプロパティ中のRowSourceの中で、計算!H1:H60　というように記述して読み込ませた上で、選択出来るようにした、という意味です。
なお、ユーザーフォーム２も、コンボボックスを同じ要領で用いています。
現在、以上のような状況にあり、同じエラーが出るのですが、どうしたものでしょうか？

回答No.2

miwaki

分析という名のファイルを作り、以下のマクロを設けました。
1のマクロ
Sub Macro1()
UserForm1.Show
End Sub

更に「分析するシート名を入れてください」というメッセージと入力するTextBox1とCommandButton1を持つUserForm1を作り、以下のマクロを設けました。

Private Sub CommandButton1_Click()
UserForm1.Hide
Workbooks.Open Filename:="C:\My Documents\資料.xls"
Sheets(TextBox1.Value).Select
Sheets(TextBox1.Value).Copy before:=Workbooks("分析").Sheets(1)
End Sub

Macro1を実行すると、シート名入力フォームが出て、シート名を入力して、コマンドボタンをクリックすると
C:\My Documentsに保管された資料という名のファイルが読み込まれて、その中の入力した名前のシートが分析という名のファイルにコピーされます。

５＆６のマクロ
Sub Macro2()
UserForm2.Show
End Sub

更に「保存するシート名を入れてください」というメッセージと入力するTextBox1とCommandButton1を持つUserForm2を作り、以下のマクロを設けました。
Private Sub CommandButton1_Click()
UserForm2.Hide
ActiveSheet.Name = TextBox1.Value
ActiveSheet.Move before:=Workbooks("資料.xls").Sheets(1)
Windows("分析.xls").Activate
End Sub

Macro2を実行すると、シート名入力フォームが出て、シート名を入力して、コマンドボタンをクリックすると
資料という名のファイルに入力した名前のシートが追加されます。

上記いずれもエラー処理を省略していますのでご注意を。

補足

ご丁寧な解説、まことにありがとうございます。
正に私の欲する内容でした＾＾
またユーザーフォームまでご教示くださいまして、助かりました。
ユーザーフォームは、コンボボックスを用いて、選択内容は、プロパティのRowSourceで規定しました。
それで、フォーム２の方はうまく動くのですが、
フォーム１でエラーが出てしまいます。
実行すると、
Sheets(TextBox1.Value).Select　
まではうまく動いており、シートもちゃんと選択しているのですが、
Sheets(TextBox1.Value).Copy before:=Workbooks("分析").Sheets(1)
ここで、「実行時エラー９　インデックスが有効範囲にありません」と表示されます。
いろいろ試してみたのですが、対処の仕方がわかりません。
お手数ですが、どうしたらよいか？教えてください。

回答No.1

peugeot307

現行のマクロのソースを見れば、ここをこうすればいいですよとアドバイスできるかもしれませんが・・・