[[20220411120917]]　『一番古いシートを別ブックに移動すると＃N/A！と潤x（yyy555）　ページの最後に飛ぶ

『一番古いシートを別ブックに移動すると＃N/A！と出る』（yyy555）

決算書を新シートに更新（数式のみ削除）して10年間そのブックに保存しておきます。ここまではできたのですが、11年目に11年前のシートを別ブックに移動するとエラーが起こります。

Sub 図1_Click()
'
' 図1_Click Macro
'

    Sheets("R5年度").Select
    Sheets("R5年度").Copy Before:=Sheets(2)
    Range("D5:E5").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    Columns("H:H").EntireColumn.AutoFit
    Range("B2").Select←ここまではエラーはでません。
    ActiveWindow.SmallScroll Down:=-24
    Worksheets(11).Move Before:=Workbooks("過去.xlsx").Worksheets(1)

End Sub

11番目のシートを過去ブックに移動すると、新シートの通貨で計算していた箇所が標準になり、#N/A!が出ます。そのセルをWクリック、エンターでもとに戻ります。
Wクリック、エンターせずにもとに戻す方法はありますか？

＜使用 Excel：unknown、使用 OS：unknown ＞

質問に対する答えはではありませんが気になったところなど。

■１
VBAの世界では基本的に、ブックやシート、セルなど（オブジェクトといいます）をきちんと明示すれば、いちいちアクティブにしたり選択したりする必要はありません。
また、【標準モジュール】でシートの指定を省略した場合、ActiveSheetが指定したとみなされるルールです。

したがって、複数のブックやセルを扱うことが前提であれば、きちんと対象のオブジェクトを指定するようにするとよいでしょう。

■２

  Columns("H:H").EntireColumn

↑は↓でも意味が同じです。

  Columns("H:H")

■３
ということを踏まえると、たとえば↓のように整理できます。

    Sub 整理()
        With ThisWorkbook
            .Sheets("R5年度").Copy Before:=.Sheets(2)

            With .Sheets(2) '【Sheets(2)】の前に挿入してるからコピー挿入したシートが【Sheets(2)】になる
                On Error Resume Next
                'SpecialCellsを使う場合、該当するセルがないと実行時エラーが発生するので対策しておいたほうが良い
                .Range("D5:E5").SpecialCells(xlCellTypeConstants, 1).ClearContents
                On Error GoTo 0

                .Range("H:H").AutoFit
            End With

            .Worksheets(11).Move Before:=Workbooks("過去.xlsx").Worksheets(1)
        End With
    End Sub

■４
質問のほうは、私にはわかりませんが、手作業でシート移動させるとどうなのでしょうか？
手作業でも同様であれば、マクロのせいではないかもしれません。

（もこな２） 2022/04/11(月) 16:00

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

まだまだ勉強不足で、マクロに助けてもらいながらマクロでできない部分を調べてVBAを追加してと、つぎはぎ状態です。

お答え頂いた内容に編集するとこのようでいいのでしょうか？
これを実行すると「実行時エラー　424　オブジェクトが必要です」と出て
.Range("H:H").AutoFit
が黄色くなります。

Sub 図1_Click()
'
' 図1_Click Macro
'

 With ThisWorkbook
    .Sheets("R5年度").Copy Before:=.Sheets(2)
            With .Sheets(2)
            On Error Resume Next
                .Range("D5:E5").SpecialCells(xlCellTypeConstants, 1).ClearContents
                On Error GoTo 0
                .Range("H:H").AutoFit
            End With
            .Worksheets(11).Move Before:=Workbooks("過去.xlsx").Worksheets(1)
        End With

End Sub

■４
手作業ではエラーになりません。
別ブックに移動する
ActiveWindow.SmallScroll Down:=-24

    Worksheets(11).Move Before:=Workbooks("過去.xlsx").Worksheets(1)
↑これを削除し実行してもエラーにはなりませんでした。

（yyy555） 2022/04/12(火) 10:32

＞11番目のシートを過去ブックに移動すると、
＞新シートの通貨で計算していた箇所が標準になり、#N/A!が出ます。
どんな計算がされていたもので、表示形式はどんなものなんですか？
数式は削除したのであれば、#N/Aというのも変な気がしますけど。

そういったあたりの説明があれば、皆さんからコメントがつくでしょう。

（γ） 2022/04/12(火) 11:30

＞「実行時エラー　424　オブジェクトが必要です」と出て～
すみません。↓のいずれかに修正してください。

 .Range("H:H").EntireColumn.AutoFit
 .Columns("H:H").AutoFit

（もこな２） 2022/04/12(火) 12:27

γさんありがとうございます。
通貨で計算していた箇所には、右隣りのシートを返すように
=INDIRECT(""&INDEX(book,SHEET()+1)&"!"&CELL("address",B2))が入っています。
数式を削除したのはその隣のセルでした、、

もこな２さんありがとうございます。
質問の問題が解決してから実行してみたいと思います。
（yyy555） 2022/04/18(月) 09:21
　

γさん、わかりにくくてすみません。

決算書ブックのシートは10年分保存します。
シートの並びは

次年度更新、R5、R4、R3、R2、R1、H30、H29、H28、H27

とあります。

次年度更新シートには図形があり、クリックすると次年度更新シートとR5シートの間に次年度更新シートの右隣のシート（前年度）がコピーされた新シートが追加されます。

次年度更新、新シート（前年度コピー）、R5、R4、R3、R2、R1、H30、H29、H28、H27（過去ブックに移動）

手入力で入力された数式は削除され、F4からG29のセルには前年度の金額（R5シート）を反映するように=INDIRECT(""&INDEX(book,SHEET()+1)&"!"&CELL("address",B2))が入っています。
ここまでは実行してもエラーはでません。

このあとにH27シートを[過去.xlsm]へ移動すると新シートの
=INDIRECT(""&INDEX(book,SHEET()+1)&"!"&CELL("address",B2))が入っているセル全てが#N/Aとなり、その一つのセルをダブルクリック、エンターすると直り前年度の金額を返してくれます。

ダブルクリック、エンターしなくてもいいようにするにはどうしたらいいでしょうか。

 Range("B2").Select
 ActiveWindow.SmallScroll Down:=-24
これはなくても大丈夫でした。

なので、今は下のようになっています。

Sub 図1_Click()
'
' 図1_Click Macro
'

    Sheets("R5年度").Select
    Sheets("R5年度").Copy Before:=Sheets(2)
    Range("D5:E5").Select
    Selection.SpecialCells(xlCellTypeConstants, 1).Select
    Selection.ClearContents
    Columns("H:H").EntireColumn.AutoFit
    Worksheets(11).Move Before:=Workbooks("過去.xlsm").Worksheets(1)

End Sub

数式の検証
INDERECT(""&INDEX({"[決算書.xlsm]
次年度更新","[決算書.xlsm]R5","
[決算書.xlsm]R4","[決算書.xlsm]
R3","[決算書.xlsm]R2","
[決算書.xlsm]R1","[決算書.xlsm]
H30","[決算書.xlsm]H29","
[決算書.xlsm]H28","[決算書.xlsm]
H27"},SHEET()+1)&"!"&CELL("address",B2))

となり、最後のH27の段のSHEET（）に下線がついていて、

この数式に含まれる関数によって、シートを計算するたびにその結果が変わります。最後の検証の
ステップはセルの結果と一致しますが、途中のステップは一致しない場合があります。

と書いてあります。

この関数は右隣だけを反映するものだと思っていたのですが、全シートに関係してくるのでしょうか。。
だからH27 シートを削除するとちゃんと返してくれないのでしょうか・・・

（yyy555） 2022/04/18(月) 12:39

Bookという名前定義は、移動後のブックでも有効なんですか？
ブックをまたいで現役のブックの名前を見に行くのですか？
そこでワンクッションが必要になっているのかも。
また、移動後のシート構造で返ってくるSheet()の値との整合性は
きちんと確保されているのですか？
# その辺が第三者にはよくわからない。
# 基本的には情報をもっているかたが良く調べることかと思いました。
# これまでとさせてください。

（γ） 2022/04/18(月) 13:35

γさんありがとうございます。

右隣のシートを返す
で検索して出ていたものをコピペしてきましたので、どのbookのことを指すのか私にもわかりません。。。
なんとなくアクティブなブックなのかなと勝手に解釈していました。

過去ブックも現役ブックもいつまでも見返せる状態にしたいです。

過去ブックに移動後の右隣のシートを返す関数ですが、同じようにダブルクリックエンターで直りますが、R5シートが現役ブックになくなると11番目のシートの移動も、新シートも追加されなくなりました。。。

これでは11年後には使えなくなるということがわかりました。

良い方法はないでしょうか
（yyy555） 2022/04/18(月) 13:58

数式は一回入力したら必要ないと思うので値に直しておけば、
ブックを移動しても状態に影響されないのでは？

マクロを使用するのであれば、前年度からコピペすればよいかと。

というか、年度毎にブックを分けてしまえばよいかと。。。
ついでに言うと、決算書のブックにマクロもおかないで、
マクロは、新しく作るときに使用するツールとして別に用意しておけば、
よいかと。

（まっつわん） 2022/04/18(月) 14:47

まっつわんさんありがとうございます。

値に直すと新シートが０になりダブルクリックエンターで元の数式に戻りますが、値にはなりませんでした。
前年度からコピぺするマクロボタンを作ると、そのボタンを作った前の年のコピペになってしますとおもうのですが、勘違いでしょうか?
例えばR4年にR3年のコピペでマクロボタンを作ります。
R5年にそのマクロボタンを押すとR3年のコピペになってしまうのではと・・・・

毎年毎にブックで分けるのをやってみます！
（yyy555） 2022/04/19(火) 10:37

＞手作業ではエラーになりません。
そうなると、マクロでも動かした直後は（元のブックを参照した数式になっているので）正常（に見える）だったりしませんか？

そうであれば、単にシート全体をコピーしてそのまま値貼付することで固定できますよね。

また、そうでない（動かした直後からN/Aエラーになる）ということであれば、移動はやめて、シートごとコピー挿入してから、元ブックの11番目シートの内容を値貼付すればよいとおもいます。
例えばこんな感じ。

    Sub 整理_改()
        Dim セル範囲 As String

        With ThisWorkbook
            .Sheets("R5年度").Copy Before:=.Sheets(2)
            With .Sheets(2)
                On Error Resume Next
                .Range("D5:E5").SpecialCells(xlCellTypeConstants, 1).ClearContents
                On Error GoTo 0
                .Columns("H:H").AutoFit
            End With

            セル範囲 = .Worksheets(11).UsedRange.Address

            .Worksheets(11).Copy Before:=Workbooks("過去.xlsx").Worksheets(1)
            Workbooks("過去.xlsx").Worksheets(1).Range(セル範囲).MergeCells = False '結合セル対策
            .Worksheets(11).Range(セル範囲).Copy
            Workbooks("過去.xlsx").Worksheets(1).Range(セル範囲).PasteSpecial Paste:=xlPasteValues
            Workbooks("過去.xlsx").Worksheets(1).Range(セル範囲).PasteSpecial Paste:=xlPasteFormats '結合セル対策

            .Worksheets(11).Delete
        End With
    End Sub

（もこな２） 2022/04/20(水) 19:27

Sub test()

    With ThisWorkbook.Worksheets
        '一番左のシートの処理
        With .Item(1)
            .Copy Before:=.Item(1)
            .Range("D5:E5").ClearContents
            .Columns("H").AutoFit
        End With

        'コピー元のボタン削除
        .Item(2).Shapes(1).Delete

        '一番右のシートの処理
        With .Item(.Count)
            .Cells.Copy                                         'セル全部をコピーして
            .Cells.PasteSpecial Paste:=xlPasteValues            '値のみ貼り付け
            .Move Before:=Workbooks("過去.xlsx").Worksheets(1)  'シートの移動
        End With
    End With
End Sub

こんな感じではないですかね？
マクロボタンが必要なのは最後の決算書(一番左のシート）だけでよいですよね？
数式も新規にシートを増やしたときだけ参照したいのですよね？
使う人に間違った操作をさせないように抑止するのも開発者の仕事でしょう。
その辺を考慮してブラッシュアップ（メンテナンス）をしていきましょう。

（まっつわん） 2022/04/21(木) 08:50

訂正

＞.Copy Before:=.Item(1)

↑ここは、

.Copy Before:=.Cells(1).Worksheet

のような感じでした＾＾；

（まっつわん） 2022/04/21(木) 08:54

コメント返信：

[ 一覧(最新更新順) ]

[[20220411120917]] 『一番古いシートを別ブックに移動すると＃N/A！と潤x（yyy555） ページの最後に飛ぶ

[[20220411120917]]　『一番古いシートを別ブックに移動すると＃N/A！と潤x（yyy555）　ページの最後に飛ぶ