シート間での連動反映

質問

誰か！！助けて下さい。。

現在、経理用の表を作成しています。
ひとつのブックを【月単位】として、1日～31日までをシートで・・・というようなものです。。シート名は半角で『7.30』という形式で入れています。

各シート、表の一番下のセルに『本日の現金残高』が計算表示されるようにしました。
そのセルの値をそのまま、自動的に次シートにある『前日現金残高』セルに(例：6/1〈1シート目〉→6/2〈2シート目〉→6/3・・・・)というように連動して反映〈表示〉させたいのです。。。。

ちなみに全シート、形式は全く同じです。なので両セルとも、当たり前ですがシート上で同じ位置にあります。
それからセルですが・・・
３のI～Ｑまでを結合して一つのセルとして、ここが『前日現金残高』、つまりここに前日分を反映させたいです。
前日分は５３のI～Ｑまでの結合セルで、ここが『本日の現金残高』になりま

やり方がわからなくて、その部分だけ毎日コピぺしていたのですが、これだと、途中入力ミスや追加があった場合にまた一からやりなおしになってしまって困っています。

ちなみに雛形から毎月日数分コピーして使っていますが、もし上記が解決された場合、毎月１シート目(○月1日分)の『前日現金残高』セルは
前月bookの最終シートの『本日現金残高』と同じにしなくてはなりません・・・。ここは最悪、手入力でも構いませんが、こういう事を考慮に入れると、毎月のBOOK内のシート作りはどの様にしたらよいでしょうか？？ただ、シートのコピペだけでは上手くいかない気がするのですが・・・・・・。

分かりにくい説明と思いますが、どうかよろしくお願いします。

回答No.3

Yosha · ベストアンサー

yoshaです。

>別ブックからの『='[2007_01.xls]1:31'!$I$9:$K$9』の書き込みが上手くいきませんでした

誠に申し訳ない。「$I$9:$K$9」はミスです。長々と書きましたが、リンクを張る手法でした。もうそれが解っていらっしゃるので、私の回答は不要となります。
ただ、『=if('7:01'!I53="","-",'7:01'!I53』は、『本日の現金残高』の欄が空欄でも“０”でも『前日現金残高』は“０”表示となりますので、それを区別するための手法です。空欄の場合は、“-”or“---”、“０”の場合は“０”と表示されます。

毎月のブックの作成は手作業では大変です。マクロを使えば即座に出来ます。
参考までに、質問の中の項目を満たすマクロを作ってみました。
新しいブックをマクロを有効として開き、Alt + F11 を押すと、Visual Basic Editorが開きます。「挿入(I)」→「標準モジュール(M)」をクリックすると、一番上に「Option Explicit」の記述がある場所が現れます。ここに、以下のSub ～End Subまでの文(コードといいます)をコピペしてください。
Visual Basic Editor 中の右向き▲か、Worksheetの上部の右向き▲のいずれかをクリックします。

出てきたマクロのダイアログボックスの「CreateNewSheet」を選択状態にして(既になっている)「実行」ボタンをクリックします。
出てきたダイアログボックスに作成したい月の数字を入力して、「ＯＫ」をクリックすれば、その月の日数に応じたシートが作成されます。ただ、リンクするブック名が判りませんので１日目のシートの“I3”には式が入っていません。そちらでリンクを張ってください。

Sub CreateNewSheet()
Dim nMonth As Integer
Dim nDay As Integer
Dim nD As Integer
Dim n As Integer
Dim s As String
Dim sSheetName As String
Dim sMonth As String
Dim sw As String
Const nDays = "0103050708101203060911"

sw = Chr(34)

sMonth = InputBox("作成する月を入力してください")
sMonth = Right("0" & sMonth, 2)
If sMonth = 0 Then Exit Sub

n = InStr(nDays, sMonth)
Select Case n
Case 0
nD = 29
Case Is < 15
nD = 31
Case Else
nD = 30
End Select
Sheets.Add Count:=nD - Worksheets.Count

For nDay = 1 To nD
sSheetName = sMonth & "." & Right("0" & nDay, 2)
Sheets(nDay).Select
Sheets(nDay).Name = sSheetName
Range("I3:Q3").Merge
Range("I3").HorizontalAlignment = xlCenter
Range("I53:Q53").Merge
Range("I53").HorizontalAlignment = xlCenter
If nDay < nD Then
s = "=if('" & sSheetName & "'!I53="
s = s & sw & sw & "," & sw & "---"
s = s & sw & ",'" & sSheetName & "'!I53)"
Worksheets(nDay + 1).Range("I3").Formula = s
End If
Next
End Sub

お礼

yoshaさん、ここまでして頂いて本当にありがとうございました。
大変勉強になりました、そして助かりました。

回答No.2

Yosha

>全シート、形式は全く同じです

ということは、『本日の現金残高』『前日現金残高』セルの位置は決まっているのでしょう？
でしたら、そのセルの位置をはっきりと示して質問する方が、より正しく回答できます。

>３のI～Ｑまでを結合して一つのセルとして
>前日分は５３のI～Ｑまでの結合セル

この表現、解り難いですね。“Ｉ５３”～“Ｑ５３”までを結合するのですか？

何となく全体は見えてきますので、推測しながら回答します。
また、マクロなどは使えないと判断して、なるだけ簡単な方法を考えてみます。

毎月定型のシートを使うようなので、『雛型ブック』を作り、その中で、決まった位置関係でのデータのやり取りは、受け取る方のセルに『式』を入力するようにしればコピペをしないで済みます。『雛型ブック』を呼び出し、新しい名前を付けて保存すれば、毎月のシートのコピペは不要となります。もしよければ、３１日用、３０日用、２９日用、２８日用あるいは１月～１２月用の各雛型ブックを作れば作業はまだ楽になります。

当然、各雛型の各シートには、タイトル、罫線なども予め書き込んでおきます。セルのマージ(結合)も行っておきます。

例えば、“2007_01”“2007_02”というブックがあり、各ブックには、“1:01”“1:02”～というシートがあり、各シートの“I3”～“Q3”、“I53”～“Q53”のセルがそれぞれマージしているとします。
そして“I3～Q3”のセルには『前日現金残高』、“I53～Q53”のセルには『本日の現金残高』が入るものとします。

“1:02”～“1:31”シートの“I3(～Q3)”セルに『='1:01'!I53』～『='1:30'!I53』という『式』を書き込みます。これで、各シートの“I53(～Q53)”『本日の現金残高』に数字が入ると“I3(～Q3)”『前日現金残高』に自動的にその値が書き込まれます。この式では、『前日現金残高』に数字が入っていないときには『本日の現金残高』に“0”が書き込まれます。“0”の代わりに“-”が良いのであれば、『=if('7:01'!I53="","-",'7:01'!I53』を書き込みます。

“2007_02”ブック“2:01”シートの“I3～Q3”の『前月現金残高』セルには、『='[2007_01.xls]1:31'!$I$9:$K$9』または、『=if('[2007_01.xls]1:31'!$I$9:$K$9="","-",'[2007_01.xls]1:31'!$I$9:$K$9』を書き込みます。

『式』中のセル、シート、ブック名などに変更があれば、適宜それに合うように値を変えてください。

お礼

Yoshaさん、ご回答ありがとうございます。大変助かりました～!!
なんとか、31日分(1ヵ月分)の雛形ブックできました。
解りずらい書き方だった部分、で申し訳ありませんでした。
全体はご推測どおりでした。
ただ、『='1:01'!I53』～『='1:30'!I53』の各シートへの書き込みは出来たのですが、別ブックからの『='[2007_01.xls]1:31'!$I$9:$K$9』の書き込みが上手くいきませんでした。。。結局リンク貼り付けをしたのですが、この式の9の表すものはなんですか？？ここはI53セルを意味している場所だと思うのですが・・・・すみません初歩的な質問かもしれませんが、よろしかったら教えていただけると嬉しいです。

補足

すみません。。。ちょっと気になって・・・
＞『雛型ブック』を作り、その中で、決まった位置関係でのデータのや＞り取りは、受け取る方のセルに『式』を入力するようにしればコピペ＞をしないで済みます。『雛型ブック』を呼び出し、新しい名前を付け＞て保存すれば、毎月のシートのコピペは不要となります。
この場合、雛形の式に『='1:01'!I53』～『='1:30'!I53』と書き込むとすると、コピペは不要になりますが、とすると(各月用の雛形を作っていない場合)新しいブックを作る時にする作業は、別名保存とシート名の変更作業だけでしょうか？ちなみにシート名を書き換えれば、自動的に式中のシート名も変わりますよね・・・・

回答No.1

Bickyon

前日分のシートのI53のセルをコピーして、本日分のシートのI3のセルのところで、
右クリックし、形式を選択して貼り付けを選んで、リンク貼り付けを行ってみてください。
すると、「='9.02'!$I$53」というようになったと思います。
意味は９月２日のシートのＩ５３セルの値を取得するです。

別ブックから取得する場合は、「=[ExcelBookName.xls]SheetName!$列$行」という形式になります。
この方法だとシートをコピーして行ったときに、手作業でシート名を修正しなければいけませんので、可能ならばシート名に月は入れないようにしたほうが良いです。

お礼

Bickyonさん！！ありがとうございました！
リンク貼り、目からウロコでした。。。。
ブック内も、別ブックでも使えるんですね♪
月を入れないほうがいいというのも大変参考になりました。。

シート名「1～31」の雛形ブックを作り、それを別名保存していって、
とりあえず、毎月1シートの別ブックから取得するところだけは、毎回雛形にリンクの貼り付け作業をする事にしました。
この方法でよいのですよね？

本当にどうもありがとうございました。。。