エクセルのマクロを使った帳票を作っていた際に、
基本となるシートをコピーして複数シートの帳票を作っていたのですが、
大量にシートを作成しているとコピーが出来なくなるという問題がありました。

今回はその際に採用した回避方法を紹介します。

まず、なぜ大量コピーに失敗するかという原因ですが、
コピーを繰り返すたびにメモリの消費が増えてしまい、
シートの情報量にもよりますが数十シートコピーしたあたりで、
メモリ不足となりコピー処理が失敗するようになります。

一度失敗するとそれ以降のシートのコピー処理は全て失敗します。

簡易的な対策としては基本となるシートそのものをコピーするのではなく、
新しいシートを追加したあとに、基本となるシートの内容のみコピーする方法があります。
しかし、これではコピーできるシート数が増えるだけで根本的な解決にはなりません。

根本的に解決するためには、「コピー処理を行っているシートを定期的に閉じる」
という処理を行う必要があります。
閉じることでそれまでにたまったメモリを解放し、コピー処理を続けることができます。

以下がそのマクロ処理の流れです。

１．基本（親）となるエクセルブックを開く。
２．基本ブックのマクロが実際にコピー処理を行うエクセルブック（子）を作成する。
（Workbooks.Add.SaveAs Filename:="子ブックのファイル名をフルパス” で新しいブックを作成)
３．基本となるブックからコピー処理ブックにコピーに使うシートをコピーする。
４．基本ブックのマクロからコピーブックのシートをコピーする処理を行う。
５．一定回数コピー処理を行ったら、一度コピーブックを保存して閉じる。
（tmpBook.Close SaveChanges:=True：tmpBookはコピーブックを割り当てたWorkbook変数）
６．コピーブックを開き、シートのコピー処理を再開する。
７．すべてのコピー処理が終わるまで４～６を繰り返す。

これでコピーブックに作成したいシートがすべて作成されますが、
このままでは元は１つだったブックが２つに分かれてしまいます。
（しかも欲しい帳票は基本ブックではなく、コピーブック側につくられています）

最後にコピーブックに作成したシートをすべてリストアップし、
一括で基本ブックに移動する処理を行い、コピーブックの削除を行ってください。
（tmpBook.Worksheets(vSheets).Move after:=MainBook.Sheets(MainBook.Sheets.Count)　
　vSheetsはVariant型でコピー対象の全てのシート名がカンマ区切りで入っている）
これで目的のエクセルブックが得られるでしょう。