VBAでシート１のデータを他シートに入力させるには

質問

sheet１に
　　Ａ　Ｂ　　Ｃ　D
１　Ｈ　２２　．　４
２　　　　　　　　５
　　　　　　　　　６と入力しています
sheet２には売り上げの表があります

現在sheet１のコード表示をクリックして、下記を入力しています。
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Name = Range("A1")
End Sub
それでsheet２にはＨ２２．４、以下sheet３　４　・・としています。

sheet２には売り上げの表があってその表題をsheet１に入力されている
Ｈ２２．４
　　　　５を使って
平成［sheet1のＢ１のデータ］年［sheet1のＤ１のデータ］月分売上高というように入力させたいのですが、色々勉強しましたが
いまいち良くわかりません。
宜しくお願いします。

回答No.7

CC_T · ベストアンサー

全角半角の変換にはStrConv関数が使えますね。
～～～
Private Sub Worksheet_Change(ByVal Target As Range)

Dim m
Dim th As String

For m = 2 To 10　
Sheets(m).Name = "H" & Range("A1") & "." & m + 2
th = "平成" & Range("A1") & "年" & m + 2 & "月分売上高"
Sheets(m).Cells(1, 1) = StrConv(th, vbWide)
Next m

For m = 11 To 13
Sheets(m).Name = "H" & Range("A1") + 1 & "." & m - 10
th = "平成" & Range("A1") & "年" & m - 10 & "月分売上高"
Sheets(m).Cells(1, 1) = StrConv(th, vbWide)

Next m

End Sub
～～～

これで各シートA1セルに入力される文字列が「平成２５年６月分売上高」など全角にできますね。
シート名も変更したければ、同様にStrConvを使った操作で実現できますよ。

お礼

出来ました、色々とありがとうございました。
モジュールは初心者の講座とかネット上にたくさんありますが
見てもよくわからず、自分のしたいことが乗ってるサイトはまず
ないですよね。
また、何かありましたら質問させて下さい。
お世話になりました。

回答No.6

CC_T

参考までに、作成したブックをアップローダーに入れておきます。
パスは00000

参考URL：

http://www.dotup.org/uploda/www.dotup.org1945644.lzh.html

お礼

Sub 半角数字を全角へ()
Dim x As Range
Set x = ActiveSheet.UsedRange
x = Application.Substitute(x, "0", "０")
x = Application.Substitute(x, "1", "１")
x = Application.Substitute(x, "2", "２")
x = Application.Substitute(x, "3", "３")
x = Application.Substitute(x, "4", "４")
x = Application.Substitute(x, "5", "５")
x = Application.Substitute(x, "6", "６")
x = Application.Substitute(x, "7", "７")
x = Application.Substitute(x, "8", "８")
x = Application.Substitute(x, "9", "９")
End Sub
これをしたにいれたのですが、変わりません

sheet表示は半角でも良いのですが、
平成２２年　４月分というふうに全角にしたいです。
宜しくお願いします

補足

何から何まですみません、ありがとうございます。
それともうひとつsheet２以降の表の表題の数字
平成22年の22を全角にするにはどのようにするのでしょうか？
これで最後ですので宜しくおねがいしあｍす。

回答No.5

CC_T

> それから上記のはsheet１のコード表示をクリックして
> worksheet　changeってそこに入力しましたが、その下に入力
> すればいいのですか？それでは動かなかったのですが？

おや？
新規ブックを立ち上げて、シート１3枚分用意して、一番左のシートでコードの表示クを
リックしてでworksheetのchangeの下窓にさっきのをペースト。
で、同じ左端シートのA1セルに数字を入れる…っと。ちゃんと動きますけどね…？
Excelのバージョンは何でしょうか？

～～～
ワークシートのイベントではシートのセルの入力都度にこのマクロが動くことになる
はずなので、「常時動いている」という表現しました。
PCにとっても些細な負荷にすぎないのでしょうけど、年に１回のために平素から
働かせるのもなんだかね、ってところです。

ちなみに下の
Sub Macro1()
　から
End Sub
までの行分は、マクロに登録して、シートを12枚用意したブックでマクロの実行して下さい。
シートの枚数数えて必要数シートを挿入するってあたりまでは含めていませんので。

補足

あーなるほど出来ました。
２２だけを入れただけで全て月まで表示されるんですね、あーすごいですね。それから最後に一つ

”ワークシートのイベントではシートのセルの入力都度にこのマクロが動くことになる”

というのはsheet１のどこかのセルに入力したらってことですよね？
他のsheet、sheet２やsheet３のどこかにいれても実行はされないみたいですので？

回答No.4

CC_T

シート１の平成○年に合わせて他のシートのタイトルとセルの値を一気に更新したい、というわけですね。

であれば、例えば
Private Sub Worksheet_Change(ByVal Target As Range)
For m = 2 To 10
Sheets(m).Name = "H" & Range("A1") & "." & m + 2
Sheets(m).Cells(1, 1) = "平成" & Range("A1") & "年" & m + 2 & "月分売上高"
Next m
For m = 11 To 13
Sheets(m).Name = "H" & Range("A1") + 1 & "." & m - 10
Sheets(m).Cells(1, 1) = "平成" & Range("A1") + 1 & "年" & m - 10 & "月分売上高"
Next m
End Sub
でご希望の動作が実現できるでしょう。シート１はA1セルに「２３」など数字を入れるだけで、あとは記入不要。
Cells(1,1)でＡ１セルを指定して「平成○年●月分売上」を入力していますので、ここで任意のセルを指定できます。
もし各シートでグラフを作成されているなら、グラフタイトルは上記で入力したセルを参照すればいいでしょう。

～～～

こうなるとsheet1が無駄に思えるのと、マクロが常に動いているってのは個人的にはなんだか落ち着きませんので、
個別のマクロにして必要な時に呼び出す方法も書いておきます。下記をマクロに登録して実行してみてください。

Sub Macro1()
　Dim Y, m
　Y = InputBox("年度を入力（例：平成２４年度なら２４を入力）")
　 For m = 1 To 9
　 Sheets(m).Name = "H" & Y & "." & m + 3
　 Sheets(m).Cells(1, 1) = "平成" & Y & "年" & m + 3 & "月分売上高"
　 Next m
　 For m = 10 To 12
　 Sheets(m).Name = "H" & Y + 1 & "." & m - 9
　 Sheets(m).Cells(1, 1) = "平成" & Y + 1 & "年" & m - 9 & "月分売上高"
　 Next m
End Sub

売上高のbookにマクロを登録すると、売上高を記入していく際に毎回「マクロ有効にするか？」なんて
セキュリティー確認が表示されて鬱陶しいので、別のブックに登録して必要時に読み込む方がいいかもしれません。

お礼

ちょっと中途半端になってますので再度説明しますと

sheet１
　 A B C D
1　H　22 ． 4
2　　 23　　 5
3　　 　 6
4　　 　 　 7

それで現在これをsheet１のWorksheet_Changeにいれています。

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Name = Range("A1") & Range("B1") & Range("C1") & Range("D1")
Sheets(3).Name = Range("A1") & Range("B1") & Range("C1") & Range("D2")
Sheets(4).Name = Range("A1") & Range("B1") & Range("C1") & Range("D3")
Sheets(5).Name = Range("A1") & Range("B1") & Range("C1") & Range("D4")
Sheets(6).Name = Range("A1") & Range("B1") & Range("C1") & Range("D5")
Sheets(7).Name = Range("A1") & Range("B1") & Range("C1") & Range("D6")
Sheets(8).Name = Range("A1") & Range("B1") & Range("C1") & Range("D7")
Sheets(9).Name = Range("A1") & Range("B1") & Range("C1") & Range("D8")
Sheets(10).Name = Range("A1") & Range("B1") & Range("C1") & Range("D9")
Sheets(11).Name = Range("A1") & Range("B2") & Range("C1") & Range("D10")
Sheets(12).Name = Range("A1") & Range("B2") & Range("C1") & Range("D11")
Sheets(13).Name = Range("A1") & Range("B2") & Range("C1") & Range("D12")
End Sub

あと、sheet2から12までのsheet名前が上の式でH22.4からH23.3に変わっています
上記の各シートの表の表題を"平成"sheet1：B1　"年"　sheet1：D4"月度売上高"
として平成２２年４月度売上高、以下23年３月までを表示したいのです。

上のモジュールに付け加えたら出来ないのでしょうか？
たびたびすみません、宜しくお願いします。

補足

解答ありがとうございあｍす。
Private Sub Worksheet_Change(ByVal Target As Range)
For m = 2 To 10
Sheets(m).Name = "H" & Range("A1") & "." & m + 2
Sheets(m).Cells(1, 1) = "平成" & Range("A1") & "年" & m + 2 & "月分売上高"
Next m
For m = 11 To 13
Sheets(m).Name = "H" & Range("A1") + 1 & "." & m - 10
Sheets(m).Cells(1, 1) = "平成" & Range("A1") + 1 & "年" & m - 10 & "月分売上高"
Next m
End Sub
これは常に動いているってことですが、

Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Name = Range("A1")
End Sub
これは常に動いていませんよね？
別のブックに登録して読み込む分も記入していただきましたが、
常に動かない様にはできないのでしょうか？

それから上記のはsheet１のコード表示をクリックして
worksheet　changeってそこに入力しましたが、その下に入力
すればいいのですか？それでは動かなかったのですが？

なんどもすみません。

回答No.3

mu2011

NO1です。
>シートの年月と表の年月は同じものが入るので問題はありません。
　⇒不正な入力等、不測の事態に対応できていないと思ったからです。
　　（仮にSheet1でA1に入力していない、不正なデータを入力等は、マクロ実行エラーとなる）

　　その他については、シート１のＡ１には、「Ｈ」、又は「Ｈ２２．４」の何れが入力されているのか、又、この日付シートの役割が読み取れませんでしたので回答しました。

　　この辺りについてもう少し判り易く補足して再質問されては如何でしょうか。

補足

sheet１

　　Ａ　Ｂ　　Ｃ　D
１　Ｈ　２２　．　４
２　　　２３　　　５
　　　　　　　　　６と入力しています

sheet２～１２には各月の売り上げの表があり、

現在sheet１のコード表示をクリックして、下記を入力しています。
Private Sub Worksheet_Change(ByVal Target As Range)
Sheets(2).Name = Range("A1")
End Sub

それでsheet２にはＨ２２．４、以下sheet３にはＨ２２．５
sheet４にはＨ２２．６　・・と表示しています。

で、実行したいのは
sheet２の売上表の表題をsheet１にある数値を使って
平成２２年　４月度　５月度というように表示し
sheet１の２２が２３に変わったら各sheetの表題も
平成２３年　４月度というように変わるようにしたいということです。

説明下手ですみませんが、宜しくお願いすます。

回答No.2

CC_T

「売り上げの表題」が何を指すのかによって、作業が異なります。
質問タイトルにあるように「シート１のデータを他シートに入力させる」だけならVBAを使うまでもない。
="平成"&Sheet1!B1&"年"&Sheet1!D1&"月売上高"
をシート２の任意のセルに入れるなどすれば済むわけですからね。

そんなわけでご質問の意図を正しく理解できたか今一つ自信がありません。
意図とは異なる現状と、理想とする状態を明確にして、質問を補足されることをお勧めします。

～～～

とりあえず、sheet１には
・Ａ１に元号の”H"
・Ｂ１に和暦
・Ｃ１にピリオド
・Ｄ列には月を順に
入力してあり、それを基にしてSheet2以下に作成済みの売り上げ表のタイトルを変えたい、
という内容かと思ったのですが、
>　sheet２にはＨ２２．４、以下sheet３　４　・・としています。
とありますので、「H22.4」シートにある「売上表グラフ」のタイトルを「平成22年4月分売上高」、「H22.5」シートにある「売上表グラフ」のタイトルを「平成22年5月分売上高」、としていきたいということなのでしょうか？
しかし、
> Sheets(2).Name = Range("A1")
としているところから見ると、シート２の名前を変えたいがsheet1のセルの値を変えた時点でsheet2の名称が”H”に変わってしまう状態で困っている…ということにも見受けられますよ？

もし目的が
・シート２以降に月ごとの売上高の表を作るための下準備がしたい
ということで、理想状態が、
・シート２の名称を「平成22年4月分売上高」、シート３の名称を「平成22年5月分売上高」…という風にシート１の入力に応じて自動で設定したい
ということであれば、シート数が不足する場合に新規シートを挿入するなんてことまで考える必要もあるでしょうし、新たな月を作成する際にシート２以降に記載済の内容とシートのタイトルがズレてしまう可能性があるなど悪影響が想定されますから、ワークシートのイベントでシート名を変えるような操作はお勧めできませんね。

補足

言葉たらずですみません。

、「H22.4」シートにある「売上表グラフ」のタイトルを「平成22年4月分売上高」、「H22.5」シートにある「売上表グラフ」のタイトルを「平成22年5月分売上高」、としていきたいということなのでしょうか？
そうです、そのとおりです。
sheet２にはＨ２２．４、sheet３にはＨ２２．５と続きます

あらかじめシート２～１３まで「平成22年4月分売上高」～
「平成23年3月分売上高」までのシートを準備しておき
フォームを作っておき、２３年度になったらシート１の２２と２３を
２３と２４に変えたら全ての１２シートのシート表示と表の表題を変えたいということです。

シート数が不足する場合に新規シートを挿入するなんてことまで考える必要もあるでしょうし
新たな月を作成する際にシート２以降に記載済の内容とシートのタイトルがズレてしまう可能性があるなど悪影響が想定されますから
ですからこれは必要ないわけでして

宜しくお願いします

回答No.1

mu2011

先ずはマクロの前にセルの表示形式を学習する必要があると思う。
特に日付形式は、エクセルが認識している日付シリアル値を利用する方が表示編集等に有効です。
例えば、FORMAT関数を使用すれば、日付シリアル値からご希望の表題は簡単に表示できます。

この日付表示について理解できれば以下の点を再考してみては如何でしょうか。
・日付表示内容をセル分割にしておく必要があるのか？
・日付リストにしている用途は何なのか？

因みに現在のコードは、Sheet1内の任意セルが変更された場合、無条件にSheet2タブ名を変更するが問題ないのか？

以上、不躾ですが今一度、やりたい事を見直しをされては如何でしょうか。

補足

早速の解答ありがとうございます。
確かにおっしゃるとおりで、現在
="平 成 "&YM!B1&" 年　 "&YM!D1&"月 分 売上高"としております
しかしシートごとにおこなわなくてはならないのでVBAで一括で処理できないかと思い
調べている所存です。

因みに現在のコードは、Sheet1内の任意セルが変更された場合、無条件にSheet2タブ名を変更するが問題ないのか？
との事ですが、シートの年月と表の年月は同じものが入るので問題はありません。

どうぞ、宜しくお願いします。