質問をすることでしか得られない、回答やアドバイスがある。

15分調べてもわからないことは、質問しよう!

ただいまの
回答率

87.50%

EXCEL VBA ブック間シートコピーでのエラー回避策

解決済

回答 3

投稿

  • 評価
  • クリップ 0
  • VIEW 6,159

score 12

EXCEL VBAで、新たにブックを生成して既存ブックのあるシートを
新規ブックにコピーするというコードを書いてみたのですが、

以下のようなエラーが発生しました。

移動先またはコピー先のブックの行列数が元のブックの行列数よりも少ないため、
シートを移動先またはコピー先のブックに挿入できません。
データを別のブックに移動またはコピーするには、データを選択して、
[コピー]コマンドと[貼り付け]コマンドを使用して移動先または
コピー先のブックのシートに挿入してください。

これを回避するために色々と調べた結果、
出来上がったコードが以下なのですが、
もっとスマートなコードで実現する方法がありましたら、
ご教示いただけますでしょうか。

    Dim myBook As String
    myBook = ActiveWorkbook.Name 'このブックのファイル名を保存

    Workbooks.Add '新規ワークブックを作成

    Dim fullPath As String
    fullPath = ActiveWorkbook.Path & "\newBook.xlsx"

    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs fullPath, xlOpenXMLWorkbook
    Application.DisplayAlerts = True

    'xlsx形式だとEXCELに認識させるために、一旦閉じてから再度オープンする
    ActiveWorkbook.Close
    Workbooks.Open fullPath

    Workbooks(myBook).Activate
    Worksheets("元シート").Activate

    ThisWorkbook.Sheets("元シート").Copy _
    Before:=Workbooks("newBook.xlsx").Sheets("Sheet1")
  • 気になる質問をクリップする

    クリップした質問は、後からいつでもマイページで確認できます。

    またクリップした質問に回答があった際、通知やメールを受け取ることができます。

    クリップを取り消します

  • 良い質問の評価を上げる

    以下のような質問は評価を上げましょう

    • 質問内容が明確
    • 自分も答えを知りたい
    • 質問者以外のユーザにも役立つ

    評価が高い質問は、TOPページの「注目」タブのフィードに表示されやすくなります。

    質問の評価を上げたことを取り消します

  • 評価を下げられる数の上限に達しました

    評価を下げることができません

    • 1日5回まで評価を下げられます
    • 1日に1ユーザに対して2回まで評価を下げられます

    質問の評価を下げる

    teratailでは下記のような質問を「具体的に困っていることがない質問」、「サイトポリシーに違反する質問」と定義し、推奨していません。

    • プログラミングに関係のない質問
    • やってほしいことだけを記載した丸投げの質問
    • 問題・課題が含まれていない質問
    • 意図的に内容が抹消された質問
    • 過去に投稿した質問と同じ内容の質問
    • 広告と受け取られるような投稿

    評価が下がると、TOPページの「アクティブ」「注目」タブのフィードに表示されにくくなります。

    質問の評価を下げたことを取り消します

    この機能は開放されていません

    評価を下げる条件を満たしてません

    評価を下げる理由を選択してください

    詳細な説明はこちら

    上記に当てはまらず、質問内容が明確になっていない質問には「情報の追加・修正依頼」機能からコメントをしてください。

    質問の評価を下げる機能の利用条件

    この機能を利用するためには、以下の事項を行う必要があります。

回答 3

checkベストアンサー

0

もっとスマートなコードで実現する方法がありましたら、
ご教示いただけますでしょうか。

何をスマートとするかはそれぞれでしょうが、

アクティブなブック、シートを対象に操作する方法だと、
Activateでフォーカスを切り替える必要があり、無駄な処理が発生する。
また、コードを読むとき何がActivateか意識する必要があり読みづらく、バグのもとになりやすい。

アクティブでなくてもたいていの操作はできるので、そのものを対象にして操作する、という方針でコーディングするといいでしょう。

    Dim myBook As Workbook
    Set myBook = ThisWorkbook 'コードの記述してあるブック ActiveWorkbook

    Dim newBook As Workbook
    Set newBook = Workbooks.Add '新規ワークブックを作成

    Dim fullPath As String
    fullPath = myBook.Path & "\newBook.xlsx"
    Application.DisplayAlerts = False
    ActiveWorkbook.SaveAs fullPath, xlOpenXMLWorkbook
    Application.DisplayAlerts = True

    'xlsx形式だとEXCELに認識させるために、一旦閉じてから再度オープンする←これ必要ですか?
    'newBook.Close
    'Set newBook = Workbooks.Open(fullPath)

    myBook.Sheets("元シート").Copy _
    Before:=newBook.Sheets(1)  

投稿

編集

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/11/06 11:26

    hatena19さん

    ありがとうございます。
    なるほど、Setを使うとすっきりしますね。

    一旦Closeして再Openしないと、
    シートのコピーの所で質問内容と同じエラーが発生しました。

    一旦Closeして再Openすると、動作します。

    キャンセル

  • 2019/11/06 12:06

    メニューのファイル→オプション→保存 の「ファイルの保存形式」が「Excel 97-2003ブック」になっていたら、「Excelブック(*.xlsx)」に変更してみてください。

    キャンセル

  • 2019/11/06 14:15

    hatena19さん

    設定を変えたら、Close/Openなしでも動作するようになりました。
    ありがとうございました。

    キャンセル

0

ThisWorkbookとはそのマクロが動いているエクセルのブックを表すので、原因はシートがないにも関わらず、"元シート"でコピー関数を実行しようとしているからだと思います。

こちらを参考にしてみて下さい。

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/11/06 11:02

    stdioさん

    ありがとうございます。

    ThisWorkBookとActiveWorkBookの理解と意識的に
    使い分けができるようにしていきます。

    キャンセル

0

二つのWorkbookを、それぞれWookbook型の変数に代入すると見通しが良くなりそうに思います。

ActiveWorkbookとかThisWorkbookは、コードのその箇所で何を参照しているのかが判りにくいです。
それに変えて、内容が判るような名前の変数を使うと、コードが理解しやすくなりますよ。

    Dim origWB as Workbook, newWB as Workbook '二つのWorkbookを入れる変数を用意する

    Set origWB = ActiveWorkbook 'もとのワークブックを変数 origWB に入れる
    Set newWB = Workbooks.Add '新規ワークブックを作成して、変数 newWB に入れる

    Application.DisplayAlerts = False
    newWB.SaveAs ActiveWorkbook.Path & "\newBook.xlsx" '作成したワークブックを名前を付けて保存
    Application.DisplayAlerts = False

    origWB.Sheets("元シート").Copy Before:=newWB.Sheets("Sheet1") 'シートのコピー

    newWB.Save   '新規ワークブックを保存
    newWB.close  '新規ワークブックを閉じる
    origWB.close '元のワークブックを閉じる

投稿

  • 回答の評価を上げる

    以下のような回答は評価を上げましょう

    • 正しい回答
    • わかりやすい回答
    • ためになる回答

    評価が高い回答ほどページの上位に表示されます。

  • 回答の評価を下げる

    下記のような回答は推奨されていません。

    • 間違っている回答
    • 質問の回答になっていない投稿
    • スパムや攻撃的な表現を用いた投稿

    評価を下げる際はその理由を明確に伝え、適切な回答に修正してもらいましょう。

  • 2019/11/06 11:18

    coco_bauerさん

    ありがとうございます。
    早速、試してみたのですが、シートのコピーの所で
    質問内容と同じエラーが発生しました。

    キャンセル

  • 2019/11/06 11:42

    hoge.xlsx内のシートを、hoge.xls内にコピーしようとしてませんか?

    キャンセル

  • 2019/11/06 14:11

    garuciaさん

    コメントありがとうございます。
    その点は大丈夫です。

    キャンセル

  • 2019/11/06 14:46

    hatena19さんのアドバイス

    メニューのファイル→オプション→保存 の「ファイルの保存形式」が「Excel 97-2003ブック」になっていたら、「Excelブック(*.xlsx)」に変更してみてください。

    これで、上記コメントの問題とそもそもの質問内容のエラーの両方が解決しました。

    キャンセル

15分調べてもわからないことは、teratailで質問しよう!

  • ただいまの回答率 87.50%
  • 質問をまとめることで、思考を整理して素早く解決
  • テンプレート機能で、簡単に質問をまとめられる

関連した質問

同じタグがついた質問を見る