エクセルVBAで複製シートの参照方法

質問

下記はシートMMMと、MMMを参照しているシートFFFのセットを複数枚複製するVBAを作りました。
シートMMMは一つの所在地の明細表で、最初はブランクです。所在地が複数ある場合にシートを複製します。

For n = 1 To X'(Xは変数です。)
Sheets(Array("MMM", "FFF")).Copy after:=Sheets(Sheets.Count)
Next

ところがMMMには名前「小計」が定義されたセルがあります。
明細表が書ききれなくなり、行を追加され、小計のアドレスが変わっても、後から別シートに全複製シートの小計を参照できるようにするために名前を定義したんです。

ところが、マクロが走ると、その名前を複製後のシートでもその名前を使用するかどうかを聞いてきて、止まってしまいます。

Application.DisplayAlerts = Falseで回避すると、自動的に「はい」になり名前の「小計」は最初のMMMにしか存在しなくなり、参照には使えなくなります。

困りました。
複製された各シートの小計セルに自動で「小計」と名前定義する方法、または別に名前定義でなくてもいいんですが、任意に増やしたMMMやFFFシートの複製の小計があるセルを別のシートに後から参照させる方法はないでしょうか？最初は存在しないシートですんで最初からTOTALのシートで参照しておくことが出来ません。また、行数を増やされる場合があるので、アドレスでは小計のセルを特定出来ないし、最下行でもないのでEnd(xlUp)で取得することもむずかしいんです。

回答No.5

taocat · ベストアンサー

こんばんは。
ありゃりゃ。。。(^^;;;

＞Application.DisplayAlerts = Falseで回避すると、自動的に「はい」になり名前の「小計」は最初のMMMにしか存在しなくなり、参照には使えなくなります

ということなので、名前「小計」を各シートに同名で定義しなおす方法を。

-----------------------------------------
Sub Test()

　Dim N As Integer
　Dim CopySU As Integer
　Dim myName As String
　Dim myAddress As String
　Dim StartShtNo As Integer

　CopySU = 2
　StartShtNo = Sheets.Count + 1

　Application.DisplayAlerts = False
　　For N = 1 To CopySU
　　　Sheets(Array("MMM", "FFF")).Copy after:=Sheets(Sheets.Count)
　　Next
　Application.DisplayAlerts = True

'基本シートMMMの名前（小計）の再定義

　myAddress = ActiveWorkbook.Names("小計").RefersToRange.Address

　ActiveWorkbook.Names("小計").Delete
　ActiveWorkbook.Names.Add Name:="小計", RefersTo:=Sheets("MMM").Range(myAddress)

'コピーしたシートの名前（小計）の再定義

　For N = StartShtNo To Sheets.Count Step 2
　　　myName = "'" & Sheets(N).Name & "'!小計"
　　　ActiveWorkbook.Names.Add Name:=myName, RefersTo:=Sheets(N).Range(myAddress)
　Next N

End Sub
---------------------------------------------

こんどは勘違いしてませんように。。。(^o^)
以上です。

お礼

taocatさん、なんどもありがとうございます。

はい、これで当初思い描いたとおりの動きになりました。ありがとうございます。

さて、今度は定義された各シートの「小計」をTOTALシートに持ってくる方法を考えなくては。
ありがとうございました。

補足

試行錯誤しましたが、TOTALシートで参照する方法は何とか思いつきました。

ありがとうございました。

回答No.6

Wendy02

merlionXX さん、こんにちは。
Wend02です。

昨日は、出かける前だったのでよく考えてみませんでしたが、Application.DisplayAlert =False でシートコピーしたら、最後に、以下のようなプログラムはどうかな？

名前定義には、二種類あります。ですから、VBAの取り扱いには注意が必要です。以下は、名前定義を置き換えています。現在は、FFF側のほうは、除外するようになっています。なお、名前定義自体は、削除はいたしません。

Sub NameStockPr()
'シートコピー後の名前を変換するプログラム
　 Dim myNameAddress
　 Dim i As Integer
　 Dim j As Integer
　 Const MYNAME As String = "小計"
　 Const FIRSTSHEET As String = "MMM"
　 With ActiveWorkbook
　　　myNameAddress = Replace(.Names(MYNAME).RefersTo, _
　　　　　　　　　　"=" & Sheets(FIRSTSHEET).Name & "!", "")
　　　On Error Resume Next
　　　For i = 1 To Sheets.Count
　　　　 If .Worksheets(i).Name Like "*M*" _
　　　　　　And .Worksheets(i).Name Like "*(*" Then
　　　　　　j = j + 1
　　　　　　Application.Names.Add Name:=MYNAME & CStr(j), _
　　　　　　RefersTo:="='" & .Worksheets(i).Name & "'!" & myNameAddress
　　　　　　With .Worksheets(i).Cells
　　　　　　　 .Replace MYNAME, MYNAME & CStr(j), xlPart, , True, True
　　　　　　End With
　　　　 End If
　　　Next i
　 End With
End Sub

出来上がると、小計1,小計2……と置き換わっています。

取り出すほうは、配列数式ができるかと思ってやってみましたが、取り出せませんでしたので、ユーザー定義関数にするか、ひとつずつ出すか、どちらかにしてみてください。

お礼

何度もありがとうございました。
取り出すほうも、今テスト段階ですが何とかなりそうです。
これでやっと来週の作業の方向性を決めることができました。

回答No.4

bonaron

#1のbonaronです。

>FFF(2)にMMM(2)を参照させるのはセットで複写するしかないんです。
#3さんへのコメントで
>MMMシートの「小計」を参照するのはFFFシートではありません。
この条件なら、比較的簡単に解決できます。

ということで、それが可能な、MMM,FFF を作る方法を考えました。
以下、
MMMを参照しているセルが少なければ
(1)MMMを現在のブックにコピー。「MMM (2)」が出来る。
(2)元のMMMの名前を「MMM0」に変更する。
(3)MMMのコピーの名前を「MMM」にする。
(4)FFFの「MMM0!」に変わった参照を「MMM!」に修正する。

手作業で参照を修正するには多すぎるなら
(1)FFFをシートのコピーで「新しいブック」にコピー。
(2)新しいブックを名前をつけて保存し、閉じる。
(3)MMMを現在のブックにコピー。「MMM (2)」が出来る。
(4)元のMMMの名前を「MMM0」に変更する。
(5)MMMのコピーの名前を「MMM」にする。
(6)FFFの名前を「FFF0」に変更する。
(7)(2)で保存したブックを開く。
(8)(7)のシート FFFを元のブックにコピーする。

これで MMM,FFF の同時コピーが可能になります。
他のシートから参照するには
「=MMM!小計」「='MMM (2)'!小計」のように。

動作確認後、不要なシートを削除で出来上がり。

お礼

何度もありがとうございます。
手作業での修正は残念ながらできないんです。
今回わたしがつくるものを使用するのはわたしではないんです。
だから処理はボタン一つで自動的に行なわれなくてはならないんです。すみません。

回答No.3

taocat

merlionXXさん、相変わらず色んなことにトライされてますねぇ。感心します。
それにmerlionXXさんの質問を考えるのは勉強にもなります。

さて、本題。（以下の名前とは定義された名前）
コピーされてできたシートには元シートの名前もコピーされますが、その名前にはコピーされてできたシートのシート名が付加されます。
これ以前merlionXXさんが質問されてましたよね。各シートに「同じ名前を定義する方法」。あれです。
要するにコピーされてできたシートの参照式の名前の前にシート名を付加するだけです。

例えば以下のように。
------------------------------------------

Sub Test()

　Dim N As Integer
　Dim CopySu As Integer
　Dim myCell As Range
　Dim myShtMei As String

　CopySu=3

For N = 1 To CopySu

　　Sheets("MMM").Copy after:=Sheets(Sheets.Count)

　　myShtMei = ActiveSheet.Name

　　Sheets("FFF").Copy after:=Sheets(Sheets.Count)

　　Set myCell = ActiveSheet.Cells.SpecialCells(xlCellTypeFormulas).Find(what:="=小計")

　　myCell.FormulaR1C1 = "='" & myShtMei & "'!小計"

Next N

End Sub
----------------------------------------------

使用範囲が狭ければ、検索は以下でもいいかも。

Set myCell = ActiveSheet.Cells.Find(what:="=小計")

それから今回は必ず検索データはあるので以下は省略。

If Not myCell Is Nothing Then

また同じシートに”小計”参照式が複数ある場合は（ないでしょうが）

-----------------------------------------
For Each myCell In Cells.SpecialCells(xlCellTypeFormulas)

　　If myCell.FormulaR1C1 = "=小計" Then

　　　　myCell.FormulaR1C1 = "='" & myShtMei & "'!小計"

　　End If
Next
---------------------------------------------

またユニークな質問、してくださいな。(^^;;;
以上です。

お礼

taocatさん、お世話になります。

私の書き方がまずかったようです。
MMMシートの「小計」を参照するのはFFFシートではありません。もちろんFFFシートはMMMシートのいろんなセルを参照してますが、そこに名前の定義はありません。だからMMMとFFFを別々にコピーするとFFF（2～X）は、全て最初のMMMのセルを参照したままです。FFF(2)はMMM(2)を参照しなくてはなりません。

名前定義は、あたらしく何枚できるか特定できないMMM(2～X)、FFF(2～X)を、TOTALという別のシートが、複製された各シートに存在する小計セルを参照するために利用するために必要かなと思ったのです。

だから名前定義にこだわらず、何枚できるかわからない。ペアになったシートの存在する、セル位置を特定できない「小計」を全て参照できる方法があるならご教示くださいますようお願いいたします。

回答No.2

Wendy02

merlionXXさん、こんにちは。

Wendy02です。

この前の続きですね。土曜日もお仕事でご苦労様です。

＞行数を増やされる場合があるので、アドレスでは小計のセルを特定出来ないし、最下行でもないのでEnd(xlUp)で取得することもむずかしいんです。

通常、小計は、セルの中から、小計または、「小」という文字を探して、その列から、アドレスを取るのではありませんか？

名前定義で処理する場合は、シートの参照に、どのような内容で、名前定義をつけているかは分りませんが、私はなるべく使わないようにしていても、必要な場合があって、名前定義が邪魔になるときがあります。

そういう場合、私の使う方法に、一旦、その名前定義の文字列数式をString型の変数に確保しておいて、該当する名前定義を削除します。それを後で戻すという方法があります。

例えば、以下の場合は、「小」とつく名前定義を一旦消して、それを後で再び戻すということをします。

Dim myNameStock() As Variant
Dim i As Long
Dim j As Long
　With ActiveWorkbook
　　For i = 1 To .Names.Count
　　If InStr(.Names(i).RefersTo, "小") > 0 Then
　　　ReDim Preserve myNameStock(1, j)
　　　myNameStock(0, j) = .Names(i).Name
　　　myNameStock(1, j) = .Names(i).Value
　　 .Names(i).Delete
　　 j = j + 1
　　End If
　 Next i
　End With

参考にしてみてください。

もし、詳しい内容を教えていただいたら、回答の内容は、まったく違うアイデアになる可能性があることも書いておきます。

お礼

いつもお世話様です。
ご教示のコードは定義された名前で「小」のつくのをmyNameStockという配列に確保し、定義自体を削除してるんですよね？
せっかくのご教示ですが悲しいかな、それからどうしたらよいのか理解できません。
走らせて見ましたがなにも動いてないようなんです。ぐすん。

定義は使わないほうがいいのかも知れませんね。

回答No.1

bonaron

For n = 1 To X
Sheets("MMM).Copy after:=Sheets(Sheets.Count)
Sheets("FFF).Copy after:=Sheets(Sheets.Count)
Next

これだと、名前もコピーされます。

お礼

早速ありがとうございます。
ただ、その方法では複製されるFFF（2）もMMMを参照してしまいます。最初に書きましたとおりMMMとMMMを参照しているFFFはペアなんです。FFF(2)にMMM(2)を参照させるのはセットで複写するしかないんです。