マクロの入ったシートをコピーしたいのですが参照シート名が変わるので困っています

こんにちは
作成中のファイルが大体完成！と喜んでいたら以下の部分で躓いてしまいました。。。
フォーマットのシート（シート名：フォーマット）が出来たので
このシート名を取引先名に変えて増やしていこうと思ったのですが
フォーマットを参照したコード？がたくさん入っており
増えるたびにこれを一々書き換えるのが大変なことに気がつきました

With Worksheets("フォーマット")←問題のコードです

この部分をどのように変更すればコピーしたときも
コードを書き換えなくて済むようになるでしょうか？

分かり難い文章で申し訳ございませんが
ご教授のほどよろしくお願いいたしますm(_ _;)m

No.4 ベストアンサー 回答者： merlionXX 回答日時： 2008/02/02 18:32

＃3です。

ではこれではいかがでしょう？
食材情報管理シートをアクティブにするのをやめました。

Sub test02()

Dim sn As String
With ActiveSheet
.Range("A5:I304").ClearContents '入力画面の抽出一覧をクリア
sn = .Name 'シート名をsnに代入
Sheets("食材情報管理シート").Range("C2").FormulaR1C1 = "=" & sn & "!R[-1]C[8]"
Sheets("食材情報管理シート").Range("A4:I303").Copy
.Range("A6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Application.CutCopyMode = False

End Sub

この回答へのお礼

これ+別シートのコード変更したら出来ました！本当にありがとうございます

分かり難い説明で御迷惑をおかけしました
勉強して精進していきたいと思います。

お礼日時：2008/02/04 11:10

No.5 回答者： zap35 回答日時： 2008/02/02 18:36

＃０１です。

ちょっとちょっとです(^^)
＞補足でもう一つ書いていただいた分では対応できなかったので
このコード実行時に開いているシートは何ですか？　またRange("A6")はどのシートのことを指していますか？
それを明示せず、ご自身のコードから「読み取らせよう」とするから回答する側は混乱するのです。以下は確認です。

もしかしてClearContentsするのはActiveSheetで、Sheets("食材情報管理シート").Range("A4:I303")を貼り付けるのもActiveSheetではないですか？

＞ActiveCell.FormulaR1C1 = "=フォーマット!R[-1]C[8]"
は現在のシートのアクティブセルに「フォーマット」シートの内容が表示されるようにするのですか。それともActiveSheetのR[-1]C[8]ですか？
（処理的に何の意味かは分かりませんが…）

With ActiveSheet
　.Range("A5:I304").ClearContents
　ActiveCell.FormulaR1C1 = "=フォーマット!R[-1]C[8]"
' 　ActiveCell.FormulaR1C1 = "=R[-1]C[8]"　　ActiveSheetならこっちの行を生かす
　Sheets("食材情報管理シート").Range("A4:I303").Copy
　.Range("A6").PasteSpecial Paste:=xlPasteAll, 　Operation:=xlNone, _
　　　　SkipBlanks:=False, Transpose:=False
End With
　Application.CutCopyMode = False

この回答へのお礼

＞もしかしてClearContentsするのはActiveSheetで、Sheets("食材情報管理シート").Range("A4:I303")を貼り付けるのもActiveSheetではないですか？

質問がわかり難くなってしまい申し訳ございませんでしたm(_ _;)m
その通りです

皆様のおかげで無事解決しました
本当にありがとうございます！！
今回教えていただいたことを応用して色々出来そうなので
また勉強します

お礼日時：2008/02/04 11:08

No.3 回答者： merlionXX 回答日時： 2008/02/02 17:33

> アクティブなシートのある部分をクリアした後

> 他のシートに一旦移ってコピーしてまた戻ってきて
> 貼り付けるという内容なのですが。。。
> 戻ってこれません（＞＜）

他のシートを選択した段階でActiveSheetが変わってしまうからです。
ActiveSheet名をいったん取得しておいてあとからそのシートをアクティブにすればいいんです。
お書きになった部分ではこんな感じでしょうか。

Dim sn As String
With ActiveSheet
.Range("A5:I304").ClearContents '入力画面の抽出一覧をクリア
sn = .Name 'シート名をsnに代入
End With

Sheets("食材情報管理シート").Range("C2").Select
ActiveCell.FormulaR1C1 = "=入力画面!R[-1]C[8]"
Range("A4:I303").Copy

Sheets(sn).Activate 'snに戻る

With ActiveSheet
.Range("A6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False
End With
Application.CutCopyMode = False

この回答へのお礼

本当にご親切にありがとうございます！

後の問題はこのコードだけなのですが
（すいません最初に自分が間違えたので分かり難くなってしまいました。。。
補足でもう一つ書いていただいた分では対応できなかったので）

ActiveCell.FormulaR1C1 = "=フォーマット!R[-1]C[8]"

このフォーマットって部分をコピーしたときも対応できるように
するにはどうしたらよいでしょうか？
試しに
ActiveCell.FormulaR1C1 = "=Sheets(sn)!R[-1]C[8]"
とかでやってもエラーになってしまいました

お礼日時：2008/02/02 18:05

No.2 回答者： zap35 回答日時： 2008/02/02 17:19

＃０１です。

補足のコードはWith文で定義したオブジェクトを使っていないので、別の問題と考えてよいですか？

机上デバッグしかしていませんが、これならどうです？
最初にアクティブなシートを保存しておいて、最後にそのシートを開き直すようにしました

マクロ記録ではSelectメソッドが多く作成されますが、Selectはマクロの実行速度を落とす最大の原因ですから、極力なくす方がよいと思います。

Dim act as Worksheet
Set act = ActiveSheet
Sheets("フォーマット").Select　
Sheets("フォーマット").Range("A5:I304").ClearContents
Sheets("食材情報管理シート").Range("C2").FormulaR1C1 = "=フォーマット!R[-1]C[8]"
Sheets("食材情報管理シート").Range("A4:I303").Copy
Range("A6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, _
　　SkipBlanks:=False, Transpose:=False
Application.CutCopyMode = False
act.Activate

No.1 回答者： zap35 回答日時： 2008/02/02 16:25

コード全体を見ないと何とも言えませんが

　With　ActiveSheet
ではどうでしょう。開いているシートを参照することになります。

この回答への補足

間違いがありましたm(_ _;)m

ActiveCell.FormulaR1C1 = "=入力画面!R[-1]C[8]"
　　　　　　　　　　　↓
ActiveCell.FormulaR1C1 = "=フォーマット!R[-1]C[8]"

補足日時：2008/02/02 17:06

この回答へのお礼

ありがとうございます！！
コピーしてもほとんどの部分で解決しました！

ただ一つ以下の部分だけはWith　ActiveSheetに変更すると
エラーが出てしまいます。。。
アクティブなシートのある部分をクリアした後
他のシートに一旦移ってコピーしてまた戻ってきて
貼り付けるという内容なのですが。。。
戻ってこれません（＞＜）

Sheets("フォーマット").Select '入力画面の抽出一覧をクリア
Range("A5:I304").Select
Application.CutCopyMode = False
Selection.ClearContents
Sheets("食材情報管理シート").Select
Range("C2").Select
ActiveCell.FormulaR1C1 = "=入力画面!R[-1]C[8]"
Sheets("食材情報管理シート").Select
Range("A4:I303").Select
Selection.Copy
Sheets("フォーマット").Select '抽出データをコピー
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=False

お礼日時：2008/02/02 16:48