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

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

新規登録して質問してみよう
ただいま回答率
85.50%
VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

Q&A

解決済

3回答

66878閲覧

【VBA】Sheet1から他のBookのSheet1へCopyする方法がわからないです????

PotePui

総合スコア69

VBA

VBAはオブジェクト指向プログラミング言語のひとつで、マクロを作成によりExcelなどのOffice業務を自動化することができます。

0グッド

0クリップ

投稿2019/04/01 16:04

前提・実現したいこと

掲題の件ですが、簡単に言うと、
0. "Book1"の"Sheet1"の全データ範囲
0. "Book2"の"Sheet1"のTableの"A1"
上記1のBook1の全データ範囲をCopyして、Book2のDTの"A1"から同じ範囲に値のみ、貼り付けを行いたい。です。

上記ブック間のCopy&Paste機能を実装中に以下のエラーメッセージが発生しました。
インデックスが有効範囲にありません。

発生している問題・エラーメッセージ

エラーが有効範囲にありません

該当のソースコード

VBA

1Option Explicit 2Dim MaxRow As Long 3Dim MaxColumn As Integer 4 5Public Sub CopySheet1_PasteMaster() 6Dim stTestFile As String 7stTestFile = "test.xlsx" 8Range("A1").Select 9MaxRow = Range("A1").SpecialCells(xlLastCell).Row 10MaxColumn = Range("A1").SpecialCells(xlLastCell).Column 11 '##################################### 12 ' Book.xlsxのシート1の全データ範囲を、stTestFileのSheet1のA1へ貼り付ける処理 13 '##################################### 14 Workbooks.Open Filename:=stTestFile  ' ファイルを開く 15   ' データ範囲をコピーし、Tableが設定済みのSheet1のA1セルを起点として値のみ張り付けを行う。 16 Workbooks("Book1.xlsx").ActiveSheet.Range("A1", Cells(MaxRow, MaxColumn)).Copy _ 17 Destination:=Workbooks(stTestFile).Worksheets("Sheet1").Range("A1", Cells(MaxRow, MaxColumn)) 18 Range("A1").Select 19End Sub

試したこと

VBA

1Destination:=Workbooks(stTestFile).Worksheets("Sheet1").Range("A1", Cells(MaxRow, MaxColumn))

上記コード、Paste先で、インデックスがダメなようです。
3時間ほど色々試行錯誤しましたが、ググってもSheet間でのやりとりがほとんどで、
Bookの手順を見つけて同じようにしているつもりですが、どうしても無理だったので、
ご質問に至りました。

補足情報(FW/ツールのバージョンなど)

Excel 2016

気になる質問をクリップする

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

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

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

guest

回答3

0

解決済みですが、ご参考までに。

まず、エラーの原因ですが、おそらく下記のコードが原因でしょう。

Workbooks("Book1.xlsx").ActiveSheet.Range("A1", Cells(MaxRow, MaxColumn)).Copy _

この前で、貼付先ブックをOpenしてますので貼付先ブックがアクティブになってます。
Cells(MaxRow, MaxColumn)はアクティブなブックのシートのセルになります。
別のシートのセルをRangeの引数にしているのでエラーになります。

ここは、
Workbooks("Book1.xlsx").ActiveSheet.Range("A1", Cells(MaxRow, Workbooks("Book1.xlsx").ActiveSheet.MaxColumn)).Copy _
というようにどのシートのセルかを省略せずに記述する必要があります。

このようなミスを避けるためにも、下記の方針にするといいでしょう。

  • SelectやActivateはしない

(しなくてもコピーはもちろん、大抵の操作はできます。また、Selectすると遅くなります。)

  • Cells() や Range() というようにブックやシートの参照を省略しない。

省略しないと上のコードのように長くなりますので、参照するブックやシートは変数に代入して使用するか、Withステートメントを使用する。

上記の方針でコーディングすると下記のようになります。

vba

1Public Sub CopySheet1_PasteMaster() 2 Dim stTestFile As String 3 stTestFile = "C\test\test.xlsx" 4 5 'コピー元セル範囲 6 Dim FromRng As Range 7 With ThisWorkbook.Worksheets("Sheet1") 8 Set FromRng = .Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell)) 9 End With 10 11 '貼付先ブック 12 Dim wb As Workbook 13 Set wb = Workbooks.Open(Filename:=stTestFile) 14 15 '貼付先セル範囲 16 Dim ToRange As Range 17 Set ToRange = wb.Worksheets("Sheet1").Cells(1, 1).Resize(FromRng.Rows.Count, FromRng.Columns.Count) 18 '貼付先がテーブルなら下記のコードで 19 'Set ToRange = wb.Worksheets("Sheet1").ListObjects(1).DataBodyRange.Resize(FromRng.Rows.Count, FromRng.Columns.Count) 20 21 FromRng.Copy Destination:=ToRange '書式もコピーされる 22 'ToRange.Value = FromRng.Value '値だけの場合はこちらのコードで 23 24End Sub

どうでしょうか。
シンプルだし、コピー元、貼付先が明確で読みやすいコードになっていると思いませんか。


ちなみに、

.Range(.Cells(1, 1), .Cells.SpecialCells(xlCellTypeLastCell))

UsedRange
は微妙に異なります。(下記リンク参照)。どちらが質問者さんのご希望にそっているか分かりませんので、その辺は違いを認識して適切な方を選択してください。

No.8 ワークシートの最終行、最終列を取得する

投稿2019/04/02 03:30

編集2019/04/02 08:52
hatena19

総合スコア33620

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

PotePui

2019/04/05 00:14

ありがとうございます。 実はオブジェクト型の操作は少し前はまだ知らなくて、 つい先日勉強して、習得しました。 ご提示いただきましたコードも今なら読めるように。 すごく見やすくなっていますね。 このようなコードが書けるよう、頑張りたいと思います。
guest

0

期待する動作になったら問題ないけど、
日本語をVBA語に直したらこんな感じじゃないかな。。。

VBA

1Sub test() 2 'Book1ブックのSheet1シートの全データ範囲をCopyして、 3 Workbooks("Book1.xlsx").Worksheets("Sheet1").UsedRange.Copy 4 'Book2ブックのSheet1シートのDTの"A1"から同じ範囲に値のみ、貼り付ける。 5 Workbooks("Book2.xlsx").Worksheets("Sheet1").ListObjects(1).Range.Range("A1").PasteSpecial Paste:=xlPasteValues 6End Sub

期待する動作になるかは試してないので不安ですが^^;

投稿2019/04/02 02:35

mattuwan

総合スコア2136

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

PotePui

2019/04/05 00:16

日本語をVBAにしていただいて、ありがとうございます。 今回は他の方のを参考にいたしましたが、またよろしくお願いいたします。
guest

0

ベストアンサー

これでできます。

VBA

1copyBook = ThisWorkbooks.Name 2stTestFile = "test.xlsx" 3 4Workbooks.Open Filename:=stTestFile 5 6Workbooks(copyBook).Activate 7Sheets(1).Copy _ 8Before:=Workbooks(stTestFile).Sheets(1)

また、現在使用中のデータ範囲のみ(例えば、Range("C2:F30"))を別ブックのRange("A1")から貼り付ける場合は下記でもできます。

VBA

1copyBook = ThisWorkbooks.Name 2stTestFile = "test.xlsx" 3 4Workbooks.Open Filename:=stTestFile 5 6copyBook.Activate 7copyBook.UsedRange.Copy 8 9Workbooks(stTestFile).Activate 10Range("A1").PasteSpecial

投稿2019/04/01 16:26

編集2019/04/01 16:34
ELBE

総合スコア305

バッドをするには、ログインかつ

こちらの条件を満たす必要があります。

PotePui

2019/04/01 23:57

ありがとうございます。 早速試して、うまくCopyができました!!
guest

あなたの回答

tips

太字

斜体

打ち消し線

見出し

引用テキストの挿入

コードの挿入

リンクの挿入

リストの挿入

番号リストの挿入

表の挿入

水平線の挿入

プレビュー

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

ただいまの回答率
85.50%

質問をまとめることで
思考を整理して素早く解決

テンプレート機能で
簡単に質問をまとめる

質問する

関連した質問