②Excel 簡単にシートコピーしたら前日の残高と日付を変更させたい→マクロの記録でエラーが出ます

日計表について、昨日回答頂き、関数でシート名の取得という事が出来ました。ちなみにシート名は日付にする事にしました。そこからマクロの記録をクリックして記録しました。
その際は、シートをコピーする→シートの名前を翌日に変える→シート内の日付を（="日付"+1)にて翌日に変更→前月残高を表の上に入れる為、前月のシートをクリックし、前月残高の数字をクリックし、エンター→マクロの記録終了
といる手順でマクロ記録しました。その後試しにそのシート（翌日の）に数字を入力して最初のシートとデータと変えてみてから、マクロ実行しました。シート名は前日「最初のシート名（2）」で表示されました。そして、最初のシートのコピーとなりました。
望んでいるのは、翌日のシート、翌日のシート、と次々数字が前日の数字と日付+1日が反映されていく事です。
マクロはコードを書くのは無理なスキルです。この場合、どうしたら、シート名取得した事とかを活かせるのでしょうか。素人で申し訳ありません。教えて下さい。宜しくお願いします。
ちなみに、マクロのエラーは下記です。
あと、シート名は日付、最初は「7月20日」としました。「7月21日」「7月22日」と一月分次々変更していきます。
ーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーーー
Sub Macro3()
'
' Macro3 Macro
'

'
Sheets("7月20日").Copy After:=Sheets(2)
Sheets("7月20日 (2)").Select
Sheets("7月20日 (2)").Name = "7月21日"　←ここが黄色く塗りつぶしになります。
Range("L2:T2").Select
ActiveCell.FormulaR1C1 = "=""2022/7/20""+1"
Range("F4:M4").Select
ActiveWindow.SmallScroll Down:=-3
Range("F3:M3").Select
Sheets("7月20日").Select
ActiveWindow.SmallScroll Down:=12
Range("A27:L27").Select
ActiveWindow.SmallScroll Down:=-12
Range("E28:L28").Select
Sheets("7月21日").Select
Range("F3:M3").Select
ActiveWindow.SmallScroll Down:=12
Range("F26:M26").Select
Sheets("7月20日").Select
Range("F26:M26").Select
Sheets("7月21日").Select
ActiveWindow.SmallScroll Down:=-15
Range("F3:M3").Select
ActiveWindow.SmallScroll Down:=-6
Selection.ClearContents
Range("F7:M7").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=6
Range("F22:M22").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=9
Range("N23:T23").Select
ActiveWindow.SmallScroll Down:=3
Range("C33").Select
Selection.ClearContents
Range("C35").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("F3:M3").Select
ActiveCell.FormulaR1C1 = "='7月20日'!R[23]C:R[23]C[7]"
Range("F3:M3").Select
ActiveWindow.SmallScroll Down:=-21
End Sub

No.8 ベストアンサー 回答者： fujillin 回答日時： 2022/07/17 23:16

No6です。

回答者には、文章にないことはわかりません。
未だに、「どのような構成のファイルなのか。どのように使うのか。」すらわからない状態です。

＞これをしても、全シートをグループ化したままだとシートの保護は
＞アクティブにならずです。
シートのグループ化はユーザの操作の話ですよね？
どう関係してくるのかわかりません。
「なにを、どのような環境下で、どうしたいのか」を明確になさらないと、回答のしようもありません。
雰囲気だけから勝手な回答をするなら、「何とかできるのではないの？」あるいは、条件が難しいようなら「代替措置を考えれば、目的は達せられるのではないか」です。


＞特定部分のセルを入力できないようにしたいので、
「特定部分」が固定なら、シートの保護をかけておけばすむ話としか思えません。
（内容が不明なので、明示されている文章だけからの判断です）

＞マクロ初心者には無理でしょうか。
初心者の度合いによります。
マクロでは、普通に操作でできることは殆ど実現可能です。
（操作や関数でも実現できないことや、論理的でない処理は無理です）
とは言え、実現できるかどうかは技量次第のところがありますので、私にはわかりかねます。

この回答へのお礼

色々本当に有難うございました。
確かに、ファイルがあっての質問ならまだしも、文章だけだと
状況分かりませんよね。質問の仕方も主観的で、申し訳ありませんでした。
今回教えて頂いた色々な事で、かなり勉強になりましたし、
今後活かせるものもあります。
これで、なんとかお仕事の業務は果たせると思います。
シートの保護は、もう少し自分で考えて研究してみます。
お忙しいところ、お時間作って頂き、感謝でいっぱいです。
直接会ってお礼をしたいくらいです。
またExcelでつまづいて、解決策が分からない時は投稿するかもしれませんので、その時は投稿を見つけて頂ければ幸いです。
本当に有難うございました。

お礼日時：2022/07/18 22:01

No.7 回答者： ひでオンジ 回答日時： 2022/07/17 19:09

お疲れ様でした

あとはfujillinさんにアドバイスを頂いて下さい

No.6 回答者： fujillin 回答日時： 2022/07/17 14:03

No3です。

＞この状態だと、シート保護は不可能なのでしょうか？
＞クライアントにこのブックを渡して、日報を入力して頂く予定です。
元シートに保護がかかっているということなのか、作成したシートに保護をかけたいのか不明ですが、どちらも可能です。（内容によってコードは変わります）

元シートに保護が掛かっている場合、コピーは可能ですが編集する際には、一旦保護を解除して、編集終了後に再度保護をかけるなどが必要になります。
（編集部分が保護対象になっていなければ、そのままでも編集できます）

さらには、保護のかけ方を UserInterfaceOnly にしておけば、マクロからの変更は保護がかかったままでも可能になります。
（いちいち解除したりする必要はありません）
https://docs.microsoft.com/ja-jp/office/vba/api/ …


＞挿入ボタンを作成して、実行して頂くと、簡単だと思い、作成します。
＞でも、シート保護していると、挿入ボタンは作成出来ないのですね！？
シート上にボタンを作成しておくのであれば、シートのコピー時にボタンもコピーされます。
その他にも、マクロを簡単に実行できる方法としては、「ショートカットキーへの登録」や「リボン等への登録」などの方法も考えられます。

この回答へのお礼

色々有難うございます。
元々シートには保護をかけていません。
例えば、7月1日から7月31日までのシートを全選択して、特定部分の
セルを入力できないようにしたいので、セル選択してからシート保護を
かけたいという流れなのですが、送って頂いたURLだと、少々難しすぎて理解が出来ません。

Sub 保護したままグループ開閉()
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
ws.EnableOutlining = True
ws.Protect UserInterfaceOnly:=True
Next ws
End Sub

というのを見つけたのですが、
これをしても、全シートをグループ化したままだとシートの保護は
アクティブにならずです。
やはり、全シートをグループ化したままセルの入力を制限するのをひと月分一気というのは、マクロ初心者には無理でしょうか。
無理なら諦めようと思います。
何か手段があるなら、教えて頂けるとと思います。
本当に何度も申し訳ありません。宜しくお願いします。

お礼日時：2022/07/17 18:56

No.5 回答者： ひでオンジ 回答日時： 2022/07/17 10:56

お疲れ様です

また漏れてしまいましたすみません

>教えて頂いたこの関数は、前日の残高が反映されるのでしょうか？
すみません。B4に反映されるのは前月ではなく前日の値でした
前日の値(F3)=INDIRECT(B3&"!F26")

>また、教えて頂いた関数上2つは、7月2日のシートの余白のセルに入れて、
>3つ目を7月2日のシートのＢ3に入れるのでしょうか？
余白に入れても、一気にまとめてしまってもOKです。
今回のは分かりやすいように数式を分解しています。
こんな感じでもOKです
前日の値(F3):=INDIRECT(TEXT(DATEVALUE(RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1))))-1,"M月D日")&"!F26")

この回答へのお礼

有難うございました。関数でだけで、前日残高反映出来ました！
日付の関数と一緒に使えます！関数好きなんですが、たくさんネストさせるのはなかなかコツがあり、難しいですね。知らない関数とかたくさんでもっと勉強が必要です。
本当に何度も申し訳ありませんでした。助かりました。また機会がございましたら何卒宜しくお願い致します。

お礼日時：2022/07/17 18:30

No.4 回答者： ひでオンジ 回答日時： 2022/07/17 08:04

お疲れ様です

indirectで合ってると思います
Excelの関数だとこんな感じですね

当日(B2):=RIGHT(CELL("filename",A1),LEN(CELL("filename",A1))-FIND("]",CELL("filename",A1)))
前日(B3):=TEXT(DATEVALUE(B2)-1,"M月D日")
前月の値(B4):=INDIRECT(B3&"!B2")

この回答へのお礼

回答有難うございます。
教えて頂いた関数で、当日と前日はきちんと値返せました。
前月の値というのが、0と表示されます。これは？申し訳ありません
。きちんと理解が出来ていませんと。

細かく説明しますと、例えば7月1日（シート名も7月1日）の　セル番地F26にその日の残高を入れております（仮に残高は500円とします）。
次のシート、セル番地F3に、その残高が引き継ぐようにしたいです（7月2日のシートのB3に500と入る）。
教えて頂いたこの関数は、前日の残高が反映されるのでしょうか？
また、教えて頂いた関数上2つは、7月2日のシートの余白のセルに入れて、
3つ目を7月2日のシートのＢ3に入れるのでしょうか？
お手数をお掛けして申し訳ありません。
更に回答を頂けると有難いです。

お礼日時：2022/07/17 10:29

No.3 回答者： fujillin 回答日時： 2022/07/16 23:20

こんばんは

シート名の規則は「m月d日」と仮定しました。
コピー後にどこをどうしたいのか、よくわからないので、斜め読みでの適当ですので、違っている箇所は適宜修正してください。

シート名のチェックとシート作成・コピーまでは以下のままでも使えると思います。
ひとまず作成後のシートの変更は以下と解釈しました。
　・L2セルに「yyyy/m/d」形式で日付を入力
　・F3:M3セルに「=前日シート!A30」の関数式を入力
　・F7:M7,F22:M22,C33,C35セルをクリア


※ 「コピー元シート」をアクティブにした状態で試してください。
Sub Q13047885()
Dim sh As Worksheet, s As Worksheet
Dim n1 As String, n2 As String, dd As Date

Set sh = ActiveSheet
n1 = sh.Name
If Not IsDate(n1) Then MsgBox "シート名が無効です": Exit Sub
dd = DateValue(n1) + 1
n2 = Format(dd, "m月d日")
For Each s In Worksheets
If s.Name = n2 Then MsgBox n2 & "のシートは既に存在します": Exit Sub
Next s

sh.Copy after:=sh
With ActiveSheet
.Name = n2
.Range("L2").Value = Format(dd, "yyyy/m/d")
.Range("F3:M3").FormulaLocal = "='" & n1 & "'!A30"
.Range("F7:M7,F22:M22,C33,C35").ClearContents
End With
End Sub


※ 直接関係はありませんが、「マクロの記録」を作成する際は、極力無駄な操作を行わず、簡潔に操作する方が無駄を省くことができます。

この回答へのお礼

再度回答頂き有難うございます。

ただ今何とか、モジュールの、A30→F26（ここに当日残高を入れています）を変えて、実行してみました。
なんと、出来ました！！
日付も翌日になり、望んでいた、前日のF26の残高が次のシートのF3に
反映されました。！
すごいです！！！
これを、週明け、会社のブックで試してみます。
出来たら、日報の作成終了で、上司に渡せます。

ちなみにもう一点質問したいのですが、
この状態だと、シート保護は不可能なのでしょうか？
クライアントにこのブックを渡して、日報を入力して頂く予定です。
関数などを触らないようにしたかったのですが、出来ないので驚きました。
マクロとはそういう機能があるのですね！？
それと、マクロ実行するにあたり、挿入ボタンを作成して、実行して頂くと、
簡単だと思い、作成します。でも、シート保護していると、挿入ボタンは作成出来ないのですね！？会社でマクロ記録をしてみて気づきました。

とにかく、マクロが作成され、うまく実行出来ました。
本当に有難うございました。助かりました！感謝でいっぱいです。
コードを自ら書いていくのは、私にはとても難しいです。

お礼日時：2022/07/17 11:16

No.2 回答者： ひでオンジ 回答日時： 2022/07/16 22:13

すみません

前のシートを引き継ぐ感じなのですね
失礼しました

Sub Macro00()
Dim strY As String
Dim strSheetName As String
Dim strNewSheetName As String
Dim ws As Worksheet
strY = Format(Date, "YYYY年")
strSheetName = ActiveSheet.Name
strNewSheetName = strY & strSheetName
If IsDate(strNewSheetName) = False Then
Exit Sub
End If
'追加したいシートが存在するかチェック
For Each ws In ThisWorkbook.Worksheets
If ws.Name = strNewSheetName Then
Exit Sub
End If
Next
ActiveSheet.Copy after:=Sheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = strNewSheetName
'===========ここから例のマクロ============
'Sheets("7月20日").Copy After:=Sheets(2)
'Sheets("7月20日 (2)").Select
'Sheets("7月20日 (2)").Name = "7月21日"　←ここが黄色く塗りつぶしになります。
Range("L2:T2").Select
'ActiveCell.FormulaR1C1 = "=""2022/7/20""+1"
ActiveCell.Value = CDate(strNewSheetName)
Range("F4:M4").Select
ActiveWindow.SmallScroll Down:=-3
Range("F3:M3").Select
Sheets("7月20日").Select
ActiveWindow.SmallScroll Down:=12
Range("A27:L27").Select
ActiveWindow.SmallScroll Down:=-12
Range("E28:L28").Select
Sheets("7月21日").Select
Range("F3:M3").Select
ActiveWindow.SmallScroll Down:=12
Range("F26:M26").Select
Sheets("7月20日").Select
Range("F26:M26").Select
Sheets("7月21日").Select
ActiveWindow.SmallScroll Down:=-15
Range("F3:M3").Select
ActiveWindow.SmallScroll Down:=-6
Selection.ClearContents
Range("F7:M7").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=6
Range("F22:M22").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=9
Range("N23:T23").Select
ActiveWindow.SmallScroll Down:=3
Range("C33").Select
Selection.ClearContents
Range("C35").Select
Selection.ClearContents
ActiveWindow.SmallScroll Down:=-21
Range("F3:M3").Select
ActiveCell.FormulaR1C1 = "='7月20日'!R[23]C:R[23]C[7]"
Range("F3:M3").Select
ActiveWindow.SmallScroll Down:=-21
End Sub

No.1 回答者： ひでオンジ 回答日時： 2022/07/16 21:43

最近何かでそんなシートを作った気がします

tmpというシートを作って下さい
あとはこのマクロを実行するだけで行けると思います

'============マクロ==============
Sub CopySheet()
Dim iDay As Integer
Dim strYM As String
Dim strYMD As String
Dim dCurrent As Date
Dim strSheetName As String
Dim dicSheetName As Object
Dim ws As Worksheet
Dim tmpSheet As Worksheet
Set dicSheetName = CreateObject("Scripting.Dictionary")
'シート存在チェックするために一旦ディクショナリーに格納
For Each ws In ThisWorkbook.Worksheets
'ついでにテンプレートシートを格納
If ws.Name = "tmp" Then
Set tmpSheet = ws
End If
dicSheetName.Add ws.Name, 1
Next
If tmpSheet Is Nothing Then
MsgBox ("テンプレートシートを作って下さい。シート名「tmp」")
Exit Sub
End If
strYM = Format(Date, "YYYY/MM")
strYM = InputBox("年月をセットしてください。（YYYY/MM）", , strYM)
For iDay = 1 To 31
strYMD = strYM & "/" & iDay
If IsDate(strYMD) = True Then
dCurrent = CDate(strYMD)
strSheetName = Format(dCurrent, "M月D日")
'シート存在チェック（既に存在するシートはコピーしない）
If dicSheetName.Exists(strSheetName) = False Then
tmpSheet.Copy after:=Sheets(ThisWorkbook.Worksheets.Count)
ActiveSheet.Name = strSheetName
End If
End If
Next
Set dicSheetName = Nothing
MsgBox ("完了")
End Sub

この回答へのお礼

有難うございます。しかし、当方マクロにはうとく、tmpファイルというのの作成方法をネット検索しましたが、分かりません。大変申し訳ありません。
それを作らずに先程教えて頂いたマクロをコピーして実行しましたが出来ませんでした。

ところで、関数だけで、日付の1-31日までの日付変更だけ一気に出来ました。
=DATE(YEAR(Sheet1!$A$2), MONTH(Sheet1!$A$2), SHEET() - 1)
を使いました。
となりますと、sheet関数を使って、前日のシートの残高を翌日の
シートへ反映させる、それを1-31日まで出来るネストさせる関数とかは
ないものでしょうか？indirect関数なのかと思いましたが、sheet関数との
ネストが分からず出来ません。
これが出来れば、マクロにうとい私でも関数だけで日報が出来るように思われるのですが。
本当にお忙しいところ申し訳ありません。
マクロは諦めようかと思い、関数で有効な手段がありましたらお願いします。

お礼日時：2022/07/16 23:23