Excelにてシート1のデータをシート2の集計表に反映させるには？

質問

今、Excelにて売上集計表を作成しているのですが、
シート1にその月の各支店毎の売上を打ち込み、
それを、コマンドボタン(フォームのボタンでもいいのですが、)
を押すと、シート2のその月の列にコピーされるというプログラムを
組みたいのですが、どうやれば良いのでしょうか？
シート1は毎回X列を打ち変える(実際には間の各個人売上を打ちかえるのですが...)というシートで
シート2はJ列から順に4月、5月・・となっている表です。

シート1はこうです。

　　　　A　・・　　　　X　 　 Y　　・　・　・
1　　　　　　　　　　4月
2　大阪支店 　 220万
・
8　奈良支店 　 150万
・
14　京都支店　 180万
・
20　神戸支店　 200万
・
26　滋賀支店　 110万
・
32　岐阜支店　 140万
・
38 　 計　　 　 　1000万
・
・

という感じで入れて、X1の月を変えて(形式表示を変えているので実際の入力は数字のみです)、
コマンドボタンを押すと、シート2のその月の列(4月ならJ列8月ならN列という感じ)に
各支店の数字がコピーされるという仕組みが作りたいのです。
C～Wの間には個人別の売上が記載されており、
その累計をSUM()で出して、X列に記載しています。
行については、セル数も結合方法も、
シート1,シート2共に同じにしてあるので
X列をそのまま値だけをコピーすれば移るのですが、
なにぶん使用するのがExcelに不慣れな不特定多数の方が使用し、
しかも「形式を選択して貼り付け」の「値」にチェックを入れてもコピー出来ず、
「値と数値の書式」をチェックしないとコピー出来ないので
(なぜだか分かりませんが...。)誰にでもすぐ使える方法が一番いいのです。
よって、シート1にコマンドボタンを置き、
それを押せばコピーされる方法があれば教えて下さい。
関数でそれが可能なのであればそれでもいいですし、
他にいい方法があれば教えて下さい。
宜しくお願い致します。

回答No.2

n-jun · ベストアンサー

>シート1にその月の各支店毎の売上を打ち込み、
>それを、シート2のその月の列にコピーされるというプログラム
は、こんな感じではどうでしょうか？

Sub Test()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim r1 As Range, r2 As Range

Set ws1 = Worksheets("Sheet1")
Set ws2 = Worksheets("Sheet2")

With ws1
Set r1 = .Range(.[X2], .Cells(Rows.Count, "X").End(xlUp))
End With

Set r2 = ws2.Rows(1).Find(What:=ws1.Range("X1").Value, LookIn:=xlValues, _
LookAt:=xlWhole)

If r2 Is Nothing Then
MsgBox "見つかりませんので" & vbLf & "終わります。"
Exit Sub
End If

r2.Offset(1).Resize(r1.Rows.Count).Value = r1.Value
End Sub

シート２のＡ列とシート１のＡ列の順序が同じなのが、
条件です。

補足

お手数をお掛けして申し訳ないのですが、
Test()　というのは、どこに貼り付けるプログラムなのでしょうか？
コマンドボタンに貼る場合、
Private Sub CommandButton1_Click()
End Sub
というような形になりますよねぇ？
それとも、モジュールの種類が違うのでしょうか？
何度もすいませんが、その辺りを再度ご教授願います。

回答No.5

n-jun

4月とかが値で入力しているのではなく、シリアル値（日付）を
４月と表示しているのでしょうか？
その場合だと見つからないのかも？

お礼

その辺も自分なりに考えて何度も変えながらやってみたんですが、
無理でした。
でもホント、何度もご足労頂いてすいませんでした。
また、何かありましたらアドバイス宜しくお願い致します。

回答No.4

n-jun

ANo.2です。
こちらで標準モジュールで検証したそのままをのせてます。

Sub Test()
End Sub
の中身を
Private Sub CommandButton1_Click()
End Sub
の中へコピペして下さい。

お礼

何度もご回答頂きありがとうございます。
作って頂いたプログラムを幾度と無く試してみたのですが、
「見つかりませんので終わります」というメッセージボックスが
出るばかりで、コピーが出来ません。
何がおかしいのでしょうか？
n-junさんが自分でやったら出来ている訳ですよね？
コピーペしているだけなのになぜ出来ないのでしょう？
表の内容も質問で載せた表そのままなのですが、
正直、X2:X7を結合していたりと、ところどころ表現しにくくて
1つのセルで表示しているように書いていますが、
別BOOKに新しく簡易な表を作り直してもやってみたのですが、
うまくいきませんでした。
今回は諦めて、また別の方法を模索してみます。
しかし、多大な労力を使ってご協力頂きありがとうございました。
感謝致します。
まだまだ、勉強不足な私ではありますが、
今後とも宜しくお願い致します。

回答No.3

pc_knight

既に、No.2にてぴったりの回答がされてます。
guchi_yamaさんの描かれているイメージとずれているかもしれませんが、別の方法：Sheet1に一つでもデータ変更が生じたらリアルタイムで、そのデータがSheet2に反映されるという方法を提案します。
次のコードをSheet1のシート名タグを右クリック→コードの表示（Ｖ）で表示されるコードエリアに貼り付けます。
Private Sub Worksheet_Change(ByVal Target As Range)
Dim tuki As String
Dim m As Integer
If Target.Row Mod 6 = 2 Then
tuki = Cells(1, "x")
m = Val(Left(tuki, Len(tuki) - 1))
Sheets("Sheet2").Cells(Target.Row, m + 6).Value = Cells(Target.Row, "x")
End If
End Sub

お礼

ご回答頂きありがとうございました。
このやり方も、何度も試してみたのですが、うまくいきませんでした。
ご協力頂いたのに、完成できず申し訳ありません。
もっともっと勉強して、理解できるよう勤めます。
また何かありましたら、これに懲りずにご回答頂ければ幸いです。
本当にありがとうございました。

回答No.1

n-jun

シート１は、大阪支店・奈良支店・・・と行間が空いてますが、
シート２はどのようになっているのでしょう？

同じ行間が空いている？
それとも、支店名で上詰めしている？
支店名はＡ列にある？

補足

説明不足ですいません。
シート1、シート2と、共に全て同じ行間です。
セルの結合方法も同じにしてあります。
なのに、形式を選択して貼り付けで、値だけのコピーが出来ないのは、
どこか違う部分があるって事ですかねぇ？
でも、値と数値の書式を一緒にコピーすると出来るんです。
って、それはどうでもいいですよね；；
支店名も同じA列にあります。
タイトル部分がI列まであるんですが、A～I列まで全て同じです。
J列からシート2には4月、5月、6月・・と月の累計が入るようにしています。
宜しくお願い致します。