EXCEL2003 マクロでのデータコピー

質問

毎日手書きしている日計表をマクロ処理したくて　奮闘しています。
手書き用（エクセルで作成済み）のシートがあってこれに計算式を入力しました。　D25が =SUM(D5:D24) 、H24が　=SUM(H5:H23)、H25が　=SUM(B25+D25-H24)　こういうふうになっています。

このシートを　月ごとに　必要枚数（営業日分）だけコピーして　それぞれのシートに　その日（1日…）の名前を付ける…ここまではできましたが　1日のシートの　H25の金額を　2日のシートの　B25　に反映させたいのですが　やり方がわかりません。

ヒントをいただけたら　ありがたいです。
家で使っているのは　EXCEL2007　なので画像はそれで開いたものですが　職場では　2003を使っています。

回答No.3

MarcoRossiItaly · ベストアンサー

No.2 です。ごめんなさい、間違えました。

No.2 のコード中、「Target.Address(0, 0) <> "A2:H2" Then」を、「Target.Address(0, 0) <> "A2" Then」に、もしくは「Intersect(Target, Range("a2:h2")) Is Nothing Then」に書き換えます。

また、Union メソッドの引数として、「, Range("b25")」を追加しました。営業日でない日付を記入した場合に、B25 セルも 0 で上書きするために、一応。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim ws As Worksheet, i As Long, j As Long, btm As Range, cnt As Long
　　If Target.Address(0, 0) <> "A2" Then Exit Sub
　　　　'日付のセルを結合していない場合は「A2」→「H2」（大文字）と書換え
　　　　'If Intersect(Target, Range("a2:h2")) Is Nothing Then Exit Sub　としてもよい
　　Union(Range("a5:d24"), Range("e5:h23"), Range("b25")).ClearContents
　　Set ws = Worksheets("sheet1")
　　With ws
　　　　For i = 1 To .Cells(Rows.Count, "l").End(xlUp).Row
　　　　　　If .Cells(i, "k").Value = Range("a2").Value Then
　　　　　　　　Select Case .Cells(i, "l").Value
　　　　　　　　Case 1
　　　　　　　　　　Set btm = Range("d24")
　　　　　　　　Case 2
　　　　　　　　　　Set btm = Range("h23")
　　　　　　　　Case Else
　　　　　　　　　　Set btm = Nothing
　　　　　　　　End Select
　　　　　　　　If Not btm Is Nothing Then
　　　　　　　　　　If btm.Value = "" Then
　　　　　　　　　　　　For j = 1 To 4
　　　　　　　　　　　　　　cnt = cnt + 1
　　　　　　　　　　　　　　If cnt = 1 Then Range("b25").Value = Val(.Cells(i - 1, "q").Value)
　　　　　　　　　　　　　　btm.End(xlUp).Offset(1, j - 4).Value = .Cells(i, "l").Offset(0, j).Value
　　　　　　　　　　　　Next j
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　End If
　　　　Next i
　　End With
End Sub

回答No.4

MarcoRossiItaly

＞ということで、とても参考にはなりましたが　今と同じ状態で税理士さんに渡すための日計表を作っていますので　教えていただいたようにはできないのです。

できない理由は、何ですか？

No.3 もしくは No.2 の回答どおりに質問者さんが試してみたところ、どこにどういう不具合が出たのですか？

「今と同じ状態で」との記述が見えますが、No.2 の画像や No.3 のコードは、Sheet2 が、今と同じ状態の帳票となるように作ってあるかと思いますが？

質問者さんのおっしゃる「今と同じ状態で」とは、何を意味しているのですか？

もしも税理士の方に渡すときに Sheet1 がないほうがよいという事情があるなら、ファイルごとコピーし、余分なシートを削除するなどしてください。また、数式が入っていないシートにしたいなら、同様にバックアップした後、Ctrl+A、値複写により数式を除去するなどしてください。

なお、私は税理士になったことはないですが、データをもらうなら、Sheet2 よりも Sheet1 の状態でもらったほうが圧倒的にありがたいです。なぜなら、Sheet2 は Sheet1 のデータの一部を転記しただけのものであり、Sheet1 のほうが長期間なのでそれに応じたクロス集計その他の分析をする余地もあるし、Sheet2 に載っている全ての情報は、Sheet1 を使っていつでも簡単に、算出・加工・表示することができるからです。一言で言えば、Sheet1 が全データ・元データ、即ちデータベースだということです。

したがって、もしもその税理士さんが Excel の得意な方であればあるほど、なるべく Sheet1 のデータベース型で提供することをお勧めします。もしかしたら、いつも日単位でしかデータをもらえないので仕方なく、もらった Sheet2 の情報をつなぎ合わせて、Sheet1 を自作されているなんてこともあるかもしれませんよ。あるいは紙媒体から、Sheet1 の形に手入力とか。

補足

回答ありがとうございます。

現在は用紙を印刷したものに毎日書き込んでいます。
ですがもしできるなら手書きにせずに毎日それぞれの項目を入力していき月末にまとめて計算済みの用紙を印刷したいと思っています。といってもこれは私のマクロの勉強のためにやっていることで実際にこれを使えるかどうかわかりません。

毎年1年分の日計表用紙は(会計処理については全くわかりませんが)科目を税理士さんが書き込んでそれを元に収支を計算されて税金の申告の資料の一部として使われたあとは　こちらに返ってきています。それを数年間は保管してあります。

今と同じ状態…というよりは今と同じ形で残すためです。

結局「B25」への前日分シートの「H25」の値の入力については　No.1さんの回答を参考にして　シートをインデックス番号で指定してすることにして　このインデックス番号を変数にして(シートの枚数－1)回処理するようにしました。（もちろん入力用に作成したシートのみ処理します）
ここまではシート作成の段階で　あとこのシートに入力していけば　前日分シートの「H25」の値と本日分シートの「B25」が違ってきてしまいますので　1日分の入力が終わったら再集計するようにこれから作る予定です。
もちろん　年月を利用してファイル名を決めて新しいブックとして保存するところまでやってみます。

本を参考にしながらの独学ですので　なかなかスマートなマクロが書けないのですが書き込んだプログラムがどういう動きをするかを見るのが面白いです。　

今回はお付き合い頂きありがとうございました。

回答No.2

MarcoRossiItaly

マクロで別シートからデータを転記し、上書きしていくのは良いと思いますが、「新しいシートの追加」は、今回の目的のような場合は以下の説明どおり不要なので、しません。

上書きなので、必要なシート枚数は 2 枚だけです。データベース用のシート（Sheet1）と帳票用のシート（Sheet2）です。Sheet2 とはつまり、質問文にお示しのシートに相当します。

Sheet2 を上書きしても Sheet1 に元データが残っているので、困りません。したがって毎日追加するのは Sheet1 のレコードであって、シートは追加しません。

1 年の営業日数を 240 日とします。お示しの画像では 5 行目から 24 行目までの行数が 20 行になっている関係で、1 年間に Sheet1 に記入される行数は、最大で 20 x 2 x 240 = 9,600 行くらいになりますね。1 年経っても、たった 9,600 行です。オートフィルタで絞り込み表示も簡単にできます（残高の計算が狂うので、並べ替えは考えないほうがいいです）。

対して、質問者さんの計画どおりに毎日シートを追加していくと、1 年で 240 シートになりますね。お示しの画像のような素朴な表だけなら、一応それくらいのシート数を作ることは可能と思いますが、たくさんあるので、目的のシートを探すだけでもたいへんです。また、ちょっと重たい数式を使ったり、様々な書式を適用したり、オブジェクトを追加したり……などと行っていると、すぐに大容量のファイルになってしまいそうです。さらに、様式に変更があったりすると、作業グループやマクロでまとめて修正することを考えるとしても、240 シート全部にうまく変更を適用させるのは大変になる可能性もありますね。

2 か所に次式を記入します。

Sheet1 Q2　=n(q1)+p2*(3-l2*2)

Sheet2 H25　=b25+d25-h24
　　　　　　あるいは
　　　　　　=sum(b25,d25,-h24)

Sheet1 と Sheet2 の日付は全て、シリアル値です。セルの書式により見かけが異なっているだけです。

Sheet1 には、残高の列も設けるべきです。今日の残高は昨日の残高を基に計算し、昨日のは一昨日のを基に、……というふうに連鎖させるわけですから、予めどこかに基準となる残高を持っておかないと、出入りのデータだけをマクロで転記しても、H25 セルの計算結果が本当に正しいという保証がなくなるからです。

ファイルのバックアップを取ってから、Sheet2 のシート見出しを右クリックして「コードの表示」で表示されるウィンドウに、次のコードを貼り付け。その後、Sheet2 の日付を編集すると、自動的に転記します。なお当日の日付のシリアル値をセルに記入するショートカットキーは、Ctrl+; です。

Private Sub Worksheet_Change(ByVal Target As Range)
　　Dim ws As Worksheet, i As Long, j As Long, btm As Range, cnt As Long
　　If Target.Address(0, 0) <> "A2:H2" Then Exit Sub　　'日付のセルを結合していない場合は「A2:H2」→「H2」（大文字）と書換え
　　Union(Range("a5:d24"), Range("e5:h23")).ClearContents
　　Set ws = Worksheets("sheet1")
　　With ws
　　　　For i = 1 To .Cells(Rows.Count, "l").End(xlUp).Row
　　　　　　If .Cells(i, "k").Value = Range("a2").Value Then
　　　　　　　　Select Case .Cells(i, "l").Value
　　　　　　　　Case 1
　　　　　　　　　　Set btm = Range("d24")
　　　　　　　　Case 2
　　　　　　　　　　Set btm = Range("h23")
　　　　　　　　Case Else
　　　　　　　　　　Set btm = Nothing
　　　　　　　　End Select
　　　　　　　　If Not btm Is Nothing Then
　　　　　　　　　　If btm.Value = "" Then
　　　　　　　　　　　　For j = 1 To 4
　　　　　　　　　　　　　　cnt = cnt + 1
　　　　　　　　　　　　　　If cnt = 1 Then Range("b25").Value = Val(.Cells(i - 1, "q").Value)
　　　　　　　　　　　　　　btm.End(xlUp).Offset(1, j - 4).Value = .Cells(i, "l").Offset(0, j).Value
　　　　　　　　　　　　Next j
　　　　　　　　　　End If
　　　　　　　　End If
　　　　　　End If
　　　　Next i
　　End With
End Sub

補足

回答ありがとうございます。

もともとは税理士さんからこの書式の用紙をいただいてそれに書き込んでいました。
税理士さんがそれに科目などを追記、分類してくれています。
10年ほど前、職場で使うコンピュータがオフコンからWindowsPCになってエクセルが使えるようになったので　自分で同じようにして用紙を作って印刷してそれに日々書き込んでいます。1ヶ月分ずつ綴じてそれを税理士さんに渡すときに検算するのが面倒くさくなって…まあ、こちらの作業の方が面倒ですけど…自分で作れないかな？と思って始めた　同僚に言わせると「無駄な作業」ですが、私としてはプロセスを楽しんでいる状態です。

ということで、とても参考にはなりましたが　今と同じ状態で税理士さんに渡すための日計表を作っていますので　教えていただいたようにはできないのです。

回答No.1

noname#191210

多分、ある特定のシートの特定セルの値を別のシートの特定のセルに代入したいということですよね

単純に
Worksheets("Sheet1").Range("B25").Value = Worksheets("Sheet2").Range("H25").Value

"Sheet1","Sheet2"については、変数がよければ、変数にして下さい。
また、固定したシート名ならそれにして下さい。（例えば"1日","2日"）

お礼

ほかの方の補足にも書いたのですが　回答を参考にして　どうにか自分のやりたいことができるようになりました。
ありがとうございました。

補足

回答ありがとうございます。
私自身もそれで大丈夫だと思っていたのですが　それを実行すると　H25に書いてある
=SUM(B25+D25-H24) が消えてしまって　数値になってしまいます。それでは不都合なのでちょっと考え方を変えて　出来上がったシートの　B25に数式を書き込みたいと思っています。

Sub 日計表作成準備()

Sheets("First").Activate '作業用シート

Range("A4:D34").Select
Selection.ClearContents

Dim StartGyo As Integer
Dim SakuseiY As Integer
Dim SakuseiM As Integer
Dim EndDay As Integer
Dim Hizuke As Integer
Dim YoubiN As Integer
Dim YoubiT As String
Dim SunCheck As String

Range("G3:G4").ClearContents 'この3行のセルは各々結合してあります
Range("I3:I4").ClearContents
Range("I7:I8").ClearContents

SakuseiY = InputBox("年を入力", "作成年", Year(Now))
SakuseiM = InputBox("月を入力", "作成月", Month(Now))

Range("G3:G4").Value = SakuseiY
Range("I3:I4").Value = SakuseiM

Select Case SakuseiM
Case 1, 3, 5, 7, 8, 10, 12
EndDay = 31

Case 4, 6, 9, 11
EndDay = 30

Case 2

If (SakuseiY Mod 4) = 0 Then
EndDay = 29

Else
EndDay = 28

End If

End Select

StartGyo = 4

For Hizuke = 1 To EndDay

Cells(StartGyo, 2).Select
Selection.Value = Hizuke

YoubiN = Weekday(SakuseiY & "/" & SakuseiM & "/" & Hizuke)

Cells(StartGyo, 1).Select
Selection.Value = YoubiN

Cells(StartGyo, 1).Select

Select Case YoubiN

Case 1
YoubiT = "日曜"

Case 2
YoubiT = "月曜"

Case 3
YoubiT = "火曜"

Case 4
YoubiT = "水曜"

Case 5
YoubiT = "木曜"

Case 6
YoubiT = "金曜"

Case 7
YoubiT = "土曜"

End Select

Cells(StartGyo, 3).Select
Selection.Value = YoubiT

Cells(StartGyo, 3).Select

Select Case YoubiT

Case "月曜", "火曜", "水曜", "木曜", "金曜", "土曜"
SunCheck = "○"

Case "日曜"
SunCheck = ""

End Select

Cells(StartGyo, 4).Select
Selection.Value = SunCheck

StartGyo = StartGyo + 1

Next Hizuke

MsgBox "日曜を除く日の日計表シートを作成します。それ以外の休日、休診日があればチェック欄の○を消してください"

Cells(1, 1).Select

End Sub
------------
Sub シート作成()

Dim Kazu As Integer

Sheets("First").Activate

Application.ScreenUpdating = False

Range("B3:D34").Select
Selection.Copy

Range("B73:D104").Select
ActiveSheet.Paste

Range("D74:D104").SpecialCells(xlCellTypeBlanks).EntireRow.Delete

With Sheets("First")
For Kazu = 74 To .Range("B104").End(xlUp).Row
Sheets("O-Sheet").Copy After:=Sheets(Sheets.Count)
Sheets("O-Sheet (2)").Name = .Cells(Kazu, 2).Value & "日"

Next Kazu

End With

Kazu = Kazu - 74

Application.ScreenUpdating = True

Sheets("First").Activate
Range("I7:I8").Value = Kazu

…
こうやってシートを作成しますので　今月の場合　2日から始まり日曜、祭日を除いて28日まで23枚です。
3日のシートの B25に 「='2日'!H25」 と数式を書けば　いいというのは分かりましたが　それを(2日を除く)すべてのシートにマクロで書き込むにはどういうふうに書けばいいでしょうか？
作業シート上に　必要な日付けだけの表が残っているのでそれを利用したらいいかな？と思っています。
数式を　文字として扱って　「2日」　の2の部分を変数で扱えばどうだろうかと思いましたが…やり方がわかりません。どなたかお教えください。

ちょっとかじった程度の者ですので用語とか間違えているかもしれません。