エクセルで計算式の連携を生かしたまま一部のシートを差し替えたい

質問

会社でエクセル形式で見積用ソフトが支給されてます（かなり膨大なソフト、シート数は６。sheet1～6）。この見積ソフトに自分でシートを増やして見積データ-から発注書、ローン返済計画書、諸費用計算書などが作成できるようにしています（sheet7-14）。ところがしばしば改訂版が支給されます。この場合支給されるのは当然ながらsheet1～6の部分です。連携すべき部分のセルは変わりません。
連携している計算式を生かしたままsheet1～6の部分のみ改訂版にさしかえるのはどうすればいいのでしょう。シートの削除、コピーで行いますと計算式が「REF#」となってしまいます。何か他の方法で連携して使える方法があればそれでも結構です。なおこのBOOK全体は、お客様ごとに見積するたび名前を付けて保存しています

回答No.4

imogasi · ベストアンサー

#2のものです。演算式の中のシート名を一括して変更するのなら、わざわざ＃２のようにVBAを使わなくても、
編集－置換－元のシート名－変更後のシート名で、一括変更出きるようです。質問の目的・内容・主旨がそれで解決するようならば、やって見てください。また＃２で
Cells(2000, 1) = cl.Formula
'----あれば演算式内で、ｘをｙで置換え
Cells(2000, 1).Replace x, y
cl.Formula = Cells(2000, 1).Formula
の部分の３行は
'----あれば演算式内で、ｘをｙで置換え
cl.Replace x, y
で済むようです。結果的に無駄を色々書きこんだことになり済みませんでした。

回答No.3

sora221

連携(参照)しているシートのセルの位置が、改訂されたシートも変わっていない事が前提ですが、
改訂シートのセル全体をコピーして、貼り付け(上書き)すればよいのではないでしょうか。

位置も変わっているのなら、元のシートを改訂シートに合わせて予め行・列の挿入・削除、セルの移動などで合わせておけば、計算式もその時点で変わります。

回答No.2

imogasi

色々な問題があるのですね。関数式では不可能だと思います。関数式の中の部分の「置換」が出来ると言うのを読んだ気もしますが、取りあえず、思いつくまま、VBAでやって見ました。
VBAの走らせ方等判らなければ補足します。
（内容）
例えばSheet1の演算式の中の=Sheet2!○○があるとして、そのSheet2の部分を、全て例えばSheet3に置き変えることを意図しました。
下記ルーチンでは、１対しか変更できないのでSheet3-->Sheet8,Sheet4-->Sheet9,・・・と複数あれば、複数回
実行が必要です。
実行すると、初めにSheet名を聞いてきますが,Sheet1等の当初のシートの名前のままでテストするときは、ｓは大文字ですので注意すること（シート名は１字１句正確に入力すること）。
申し訳ありませんが、少数例でしかテストしておらず、私が思慮している事項の不足で、失敗すると、影響が大きそうなので、シートかブックのコピーを取り、慎重にテストの上自己責任で採用可否を決めてください。
下記でRange("a1:j100")の部分は、演算式が入っている範囲を（各シート間で最大公約数的に見積り）指定してください。大きめに指定しても良い。
Cells(2000, 1) (３箇所）は一時的に使うセルなので
害のないと思われるCells(2000, 1) を使っています。
本番で使うセル以外ならどこでも良いです。
Sub test02()
Dim cl As Range
x = InputBox("Fromシート名 = ")
y = InputBox("Toシート名＝")
'-----指定範囲の各セルに演算式があるか
For Each cl In Range("a1:j100")
If cl.HasFormula = True Then
Cells(2000, 1) = cl.Formula
'----あれば演算式内で、ｘをｙで置換え
Cells(2000, 1).Replace x, y
cl.Formula = Cells(2000, 1).Formula
End If
Next
End Sub

回答No.1

comv

こんばんは

>シートの削除、コピーで行いますと計算式が「REF#」
>となってしまいます。
Sheet7-14を新しいBOOKにコピーすればよろしいのでは？
その結果、もし参照先が元のBOOKにリンクされて
しまったら一旦、保存後　編集　リンクの設定
リンク先の変更で　保存先の自己セルを選べば
解消されます。