エクセル（マクロ）で累計部分を更新しながらシートを

質問

日報作成をマクロで自動化する方法を探しています。

とりあえずできたのは、
１：「1日」シートを作り、セルA1には日付「2015年9月1日」を入力。
２：「1日」シートをコピーして「2日」シートを作成し、セルA1の日付を「2015年9月2日」に変更。
３：２の作業を「31日」シートまで繰り返す。
というところまでです。
（１つのワークブックに、「1日」～「31日」までのシートがある状態です。）

これに加えて、たとえば、セルB1に当日売上を入力すると、
セルB2に月累計が出るようにしたいのです。
試してみたのは、「1日」シートのセルB2に「＝B1」と入力し、
「2日」～「31日」シートのB2に
=INDIRECT(TEXT(A1-1,"1日")&"!B2")+B1
と入力してみましたが、これだと月累計ではなく「1日」シートのみとの合計になってしまい、
「3日」～「31日」シートでは、数式内のシート名"1日"の部分を
手動で更新していかなければなりません。

数式内のシート名を翌日の日付に変更しながらシートをコピーする方法はありますか？

または、
B2＝前日シートのB2＋当日シートのB1
となる数式は無いでしょうか？？

ちなみに今マクロで使っているのは下記のコードです。
———————————
Sub 日報作成()

Dim firstDay As Date
firstDay = InputBox("日報", "最初の日付を指定", Date) '最初のシートの日付を指定
Worksheets("1日").Range("A1") = firstDay

Dim i As Integer
For i = 1 To 30
Worksheets("1日").Copy after:=Worksheets(Worksheets.Count) '1日のコピーを末尾に作る
Worksheets(Worksheets.Count).Name = i + 1 & "日" 'コピーしたシートの名前を変える
Range("A1").Value = DateAdd("d", i, firstDay) '日付を一日ずつ足していく
Next i

End Sub
―――――――――――

似たようなデータを毎月大量に作るので、どうにか効率化できたらと思っています。
ちなみに、わたしはプログラミングCを少しかじったことがありますが、
マクロを使うのは初で、上記もネットで見つけたコードを少しいじった程度です、、

詳しい方がいらしたら教えてください。
よろしくお願いいたします。

回答No.4

keithin

sub macro1()
　dim w as worksheet
　dim i as integer

’クリンナップ
　application.displayalerts = false
　for each w in worksheets
　　if w.name <> "1日" then
　　　w.delete
　　end if
　next
　application.displayalerts = true

’シート１日の準備
　worksheets("1日").range("A1") = date - day(date) + 1
　worksheets("1日").range("B2").formula = "=B1"

’シートの展開
　for i = 2 to day(dateadd("M",1,range("A1") - 1))
　　worksheets("1日").copy after:=worksheets(worksheets.count)
　　range("A1") = range("A1") - 1 + i
　　activesheet.name = i & "日"
　　range("B2").formula = "=" & i - 1 & "日!B2+B1"
　next i
end sub

このマクロで各シートＢ２に記入される関数式を見てわかるとおりですが，マクロを使うなら直接欲しい数式を書かせてしまった方が簡単です。

回答No.3

kagakusuki

月累計を求めるためのワークシート関数に関してですが、当日のB1セルに値を入力した場合にのみ、前日のB1セルの値に、当日のB1セルの値を加算するというやり方では、もし途中のシートにB1セルが未入力となっているセルが存在した場合、そのシートよりも後のシートのB2セルの値には、そのシートよりも前のシートのB1セルに入力されている分が反映されなくなってしまいます。
　ですから、B2セルに入力する関数は足し算ではなく、SUM関数の串刺し計算にされた方が良いと思います。

【参考URL】
　Excel 複数シートのデータを集計、串刺し演算 ３D参照～Excelの達人技
　　http://kokodane.com/tec2_38.htm

　複数のシートの同じ位置にあるセルまたはセル範囲に対する 3-D 参照を作成する - Office Support
　　https://support.office.com/ja-jp/article/%E8%A4%87%E6%95%B0%E3%81%AE%E3%82%B7%E3%83%BC%E3%83%88%E3%81%AE%E5%90%8C%E3%81%98%E4%BD%8D%E7%BD%AE%E3%81%AB%E3%81%82%E3%82%8B%E3%82%BB%E3%83%AB%E3%81%BE%E3%81%9F%E3%81%AF%E3%82%BB%E3%83%AB%E7%AF%84%E5%9B%B2%E3%81%AB%E5%AF%BE%E3%81%99%E3%82%8B-3-D-%E5%8F%82%E7%85%A7%E3%82%92%E4%BD%9C%E6%88%90%E3%81%99%E3%82%8B-40ca91ff-9dcb-4ad1-99d2-787d0bc888b6

　それで御質問のマクロに関してですが、まず、

>１：「1日」シートを作り、セルA1には日付「2015年9月1日」を入力。

に関しては、「1日」シートは雛型ですので手作業で作成して頂いた方が良いと思います。
　ですから、下記のマクロは「1日」シートが既に存在している状態で起動させる様にして下さい。
　尚、下記のマクロでは、「1日」シートのA1セルに入力されている日付の月に合わせて、他のシートのA1セルの日付も自動的に変更される様になる"ワークシート関数"を、各シートに入力する様になっておりますので、下記のマクロを使って1度2日～31日のシートを作成してしまえば、後はファイルをコピーしてから、B1セル等に入力済みのデータを消去し、「1日」シートのA1セルに入力されている日付を変更するだけで、他の月用のファイルを作成する事が出来ます。

Sub QNo9043435_エクセルマクロで累計部分を更新しながらシートを()

Const EndWord As String = "日", DateCell As String = "A1" _
, InputCell As String = "B1", CumulatCell As String = "B2"

Dim FirstSheet As Worksheet, i As Byte, _
DateCellRC As String, InputCellRC As String

DateCellRC = Range(DateCell).Address(, , xlR1C1)
InputCellRC = Range(InputCell) _
.Address(, False, xlR1C1, , Range(CumulatCell))

If IsError(Evaluate("ROW('1" & EndWord & "'!A1)")) Then
MsgBox "シートのレイアウトの雛型として設定されている" _
& vbCrLf & vbCrLf & 1 & EndWord & vbCrLf & vbCrLf & _
"というシート名のシートが見つかりません。" & vbCrLf _
& "マクロを終了します。", vbExclamation, "存在しないシート"
Exit Sub
End If
Set FirstSheet = Sheets(1 & EndWord)

With Application
.ScreenUpdating = False
.Calculation = xlManual
End With

For i = 2 To 31
If IsError(Evaluate("ROW('" & i & EndWord & "'!A1)")) Then
FirstSheet.Copy After:=Sheets(i - 1 & EndWord)
ActiveSheet.Name = i & EndWord
ActiveSheet.Range(InputCell).ClearContents
End If
With Sheets(i & EndWord)
With .Range(DateCell)
.FormulaR1C1 = "=IF(ISERROR(1/(DAY('1" & EndWord & "'!" & _
DateCellRC & ")/(""1904年1月""&REPLACE(CELL(""filename"",RC)" _
& ",1,FIND(""]"",CELL(""filename"",RC),FIND("".xl""," & _
"CELL(""filename"",RC))),)))),"""",(TEXT('1" & EndWord & "'!" _
& DateCellRC & ",""yyyy年m月"")&" & "SUBSTITUTE(RIGHT(" & _
"CELL(""filename"",RC),3),""]"",))+0)"
.NumberFormatLocal = "yyyy""年""m""月""d""日"""
End With
.Range(CumulatCell).FormulaR1C1 = _
"=IF(ISNUMBER(" & InputCellRC & "),SUM('1" & EndWord _
& ":" & i & EndWord & "'!" & InputCellRC & "),"""")"
End With
Next i

With Application
.Calculation = xlAutomatic '計算モードを自動に切り替え
.ScreenUpdating = False 'モニター表示の更新を行う
End With

End Sub

回答No.2

shintaro-2

>=INDIRECT(TEXT(A1-1,"1日")&"!B2")+B1

何か勘違いをされていませんか？
text関数で日付の出力形式を指定するのは"d"か"dd"です。
”1日”なら、どの日付でも戻り値としては1日になります。
関数でやるのなら
=INDIRECT(TEXT(A1-1,"d")&"日!B2")+B1
となるはずです。

回答No.1

dogs_cats

月初に月末分のフォームをコピーし、累計式をB2に挿入する事で良いのですよね。

月の月末日は変動するのでinputboxの日付から当月の日にちを取得する。変数Days
for~nextは１日目は不用なので2から開始。

inputboxは１日以外でも一日を表示するようにしました。

Sub 日報作成()

Dim firstDay As Date
Dim i As Integer
Dim Days As Integer

firstDay = InputBox("日報", "最初の日付を指定", DateSerial(Year(Date), Month(Date), 1)) '最初のシートの日付を指定

With Worksheets("1日")
.Range("A1") = firstDay
.Range("B2").Formula = "=B1"
End With

Days = Day(DateAdd("m", 1, firstDay) - 1)

For i = 2 To Days

Worksheets("1日").Copy after:=Worksheets(Worksheets.Count) '1日のコピーを末尾に作る
Worksheets(Worksheets.Count).Name = i & "日" 'コピーしたシートの名前を変える
Range("A1").Value = DateAdd("d", i - 1, firstDay) '日付を一日ずつ足していく
With Worksheets(i & "日")
.Range("B2").Formula = "=B1+" & i - 1 & "日" & "!B1"
End With

Next i

End Sub

31枚ものシートを作成するのは得策とは思えませんが。
データ量はそんなに多いのでしょうか。1枚のシートで纏められるものは纏める。
累計計算が思ったように出来ないのは１シートで無いからです。
工夫すれば１ケ月分を１シートにする事は出来ますね。