• ベストアンサー

エクセルＶＢＡで非アクティブブックのRow取得

非アクティブシートの最終行のRowを取得するマクロが ｘｌｓ形式のファイルだと動いていたのですが、xlsm形式に変更後、エラーになってしまいました。 これは仕様の変更によるものなのでしょうか？ Activeteせずに対処する方法があれば教えて頂きたいです。 Sub ボタン1_Click() Dim 最終行 As Long 最終行 = Workbooks("1.xls").Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row MsgBox 最終行 End Sub

質問者が選んだベストアンサー

  • ベストアンサー
  • mt2008
  • ベストアンサー率52% (885/1701)
回答No.5

> Workbooks("1.xls").Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row > ↑ここでブックを指定してても取得するのはアクティブシートのRows.Countを取得するって事ですか？ そうです。 ブックとシートを指定しているのはCellsに対してだけです。 Rows.Countに対してはブックもシートも指定していませんのでアクティブシートが対象になります。 ですから、"1.xls"のSheet1のRows.Countを使用したいなら Workbooks("1.xls").Worksheets("Sheet1").Cells(Workbooks("1.xls").Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row と、書く必要があります（Withを使えばもう少しきれいになりますが…）。 でも、xlsの場合Rows.Count ＝ 65,536 と決まっているのですから、 Workbooks("1.xls").Worksheets("Sheet1").Cells(65536, 1).End(xlUp).Row で良いと思います。

h_kimura_jp
質問者

お礼

なるほど。納得しました。非常に勉強になりました。 長々と付き合っていただきありがとうございました。

その他の回答 (4)

  • mt2008
  • ベストアンサー率52% (885/1701)
回答No.4

> アクティブシートのRows.Countを取得するという点が理解できないのですが。 Rows.Countでは指定しない限りアクティブなシートの最終行を返します。 Rangeでも、ただ単にRange("A1")とするとアクティブなシートが対象ですよね。それと同じです。 もし、どうしても1.xlsの最終行を指定したいのでしたら以下の様にする方法も有ります。 Rows.Count ↓ Workbooks("1.xls").Worksheets("Sheet1").Rows.Count

h_kimura_jp
質問者

補足

Workbooks("1.xls").Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row ↑ここでブックを指定してても取得するのはアクティブシートのRows.Countを取得するって事ですか？ 何度もつまらない質問ですみません。

  • mt2008
  • ベストアンサー率52% (885/1701)
回答No.3

Rows.Count　が原因。 xlsxやxlsmのRows.Count（ワークシートの最終行）は、1,048,576ですがxlsは65,536 65,536行までしかないシートの1,048,576行を起点に使用している最終行を取得しようとしているからエラーになっています。 Rows.Count　を　やめて65536にしては？

h_kimura_jp
質問者

補足

その通りでした。xlsxだと取得できました。 ただ1つ疑問なのが、非アクティブシートの("1.xls")上でのRows.Countを取得するわけでは無いのでしょうか？ アクティブシートのRows.Countを取得するという点が理解できないのですが。 初歩的な質問ですみません。

noname#203218
noname#203218
回答No.2

私は2007ですが、そのマクロを実行させても問題無いですね。 ダミーファイルの拡張子はxlsxですが。 1.xlsのファイルを開いていない又はファイル名が間違っていれば、エラーになると思いますが。

h_kimura_jp
質問者

補足

Excelは2010です。ファイルはもちろん開いている状態で実行しています。マクロを実行するファイルの拡張子がxlsとxlsmの違いだけでエラーになってしまいます。

  • chie65535
  • ベストアンサー率43% (8520/19368)
回答No.1

＞xlsm形式に変更後 ＞Workbooks("1.xls"). xlsm形式に変えたら、拡張子が「.xls」から「.xlsm」「.xlsx」「.xlsb」に変わってると思いますが「1.xls」のままで大丈夫なんですか？ 最終行を求めたい「別ブックのシート」が、古いエクセル形式なら「1.xls」のままでも大丈夫でしょうけど…。 因みに、新形式のファイル拡張子と内容は、以下のようになっています。 「.xlsm」⇒新形式で、Open XML形式のテキストデータで、マクロが使える 「.xlsx」⇒新形式、Open XML形式のテキストデータ 「.xlsb」⇒旧来と同じバイナリ形式で、下位互換の為にある

h_kimura_jp
質問者

補足

分かりづらくてすみません。 マクロを実行しているのは"2.xlsm"で、 このファイルがアクティブの状態で "1.xls"が非アクティブです。 "2.xls"のファイルだとエラーにならないのに "2.xlsm"のファイルだとエラーになってしまいます。

  1. カテゴリ
  2. [技術者向] コンピューター
  3. プログラミング・開発
  4. Visual Basic

関連するQ&A

  • エクセルＶＢＡで非アクティブブックのRow取得

    先ほどの質問の文章がおかしかったので再度質問させていただきます。 非アクティブブックの最終行のRowを取得するマクロが ｘｌｓ形式のファイルだと動いていたのですが、xlsm形式に変更後、エラーになってしまいました。 これは仕様の変更によるものなのでしょうか？ Activeteせずに対処する方法があれば教えて頂きたいです。 マクロを実行しているのは"2.xlsm"で、 このファイルがアクティブの状態で "1.xls"が非アクティブです。 "2.xls"のファイルだとエラーにならないのに "2.xlsm"のファイルだとエラーになってしまいます。 Sub ボタン1_Click() Dim 最終行 As Long 最終行 = Workbooks("1.xls").Worksheets("Sheet1").Cells(Rows.Count, 1).End(xlUp).Row MsgBox 最終行 End Sub

  • エクセルVBAでエラー、Changeの使い方が×？

    エクセルVBAでBOOK1のsheet1とsheet2とsheet3があり、 sheet1とsheet2の全ての情報をsheet3にコピーしてまとめるようにしました。 マクロを実行するには、Visual Basicを開いてF5を押しています。 それをsheet1かsheet2の中身の一部分でも変更すると そのときに自動的にマクロが実行されるようにしたいです。 sheet1とsheet2とsheet3に Private Sub Worksheet_Change(ByVal Target As Range) Call マクロ() End Sub を入れ、 標準モジュールに Sub マクロ() Workbooks("BOOK.xlsm").Worksheets("sheet1").Range("C1:BE50").Copy _ Destination:=Workbooks("BOOK.xlsm").Worksheets("sheet3").Range("C1:BE50") Workbooks("BOOK.xlsm").Worksheets("sheet2").Range("C1:BE100").Copy _ Destination:=Workbooks("BOOK.xlsm").Worksheets("sheet3").Range("C51:BE150") Dim UsedCell As Range Dim Max_Row, RowCount As Integer Set UsedCell = ActiveSheet.UsedRange Max_Row = UsedCell.Cells(UsedCell.Count).Row Application.ScreenUpdating = False For RowCount = Max_Row To 1 Step -1 If Application.WorksheetFunction.CountA(Rows(RowCount)) = 0 Then Rows(RowCount).Delete End If Next Application.ScreenUpdating = True End Sub をやって、sheet1かsheet2のセルを変更すると エクセルが固まってしまいます。 デバックでは最初の Workbooks("BOOK.xlsm").Worksheets("sheet1").Range("C1:BE50").Copy _ Destination:=Workbooks("BOOK.xlsm").Worksheets("sheet3").Range("C1:BE50") がよくないようです。 書き方が間違っているのでしょうか？

  • VBA Next For でのコピペについて

    EXCEL VBA初心者です。 AシートEW44からGD44までをコピーしてBというシートの最終行へコピーしたいです。 今下記のように組んでいるのですが、うまく作動しません。 Private Sub CommandButton1_Click() Dim i As Integer For i = 153 To 186 row1 = Worksheets("B").Cells(Rows.Count, 27).End(xlUp).Row Worksheets("A").Cells(i, 44).Value = Worksheets("B").Cells(row1 + 1, 27).Value Next i End Sub アドバイスいただけませんでしょうか。

  • エクセルのブック間ででデータをコピーするPG

    エクセル2000でブック間のデータのコピーをやっていての質問です。 あるディレクトリ内に「統合するファイル.xls」と「統合されるファイル.xls」があります。両方のファイルにはC列からG列、2行目以下に数字のデータが入っています。（C2～G2、C3～G3・・・・というように続きます） 「統合されるファイル」のC列からG列のデータをコピーして「統合するファイル」のデータの下に貼り付けるプログラムを作っています。プログラムは以下です。 Dim sBase As String Dim sMyPath As String Dim lLine As Long Dim lStartRow As Long Application.ScreenUpdating = False '統合するファイル名・ディレクトリ名取得 sBase = ActiveWorkbook.Name sMyPath = ActiveWorkbook.Path & "\" Workbooks.Open Filename:=sMyPath & "統合されるファイル.xls" lLine = Workbooks("統合されるファイル.xls").Worksheets("Sheet1").Cells(ActiveSheet.Rows.Count, 3).End(xlUp).Row '統合するファイルの最終行を抽出 lStartRow = 0 lStartRow = Workbooks(sBase).Worksheets("Sheet1").Cells(ActiveSheet.Rows.Count, 3).End(xlUp).Row '統合されるファイルからコピーして統合するファイルの最終行より下に貼り付け Workbooks("統合されるファイル.xls").Worksheets("Sheet1").Range(Cells(2, 3), Cells(lLine, 7)).Copy Destination:=Workbooks(sBase).Worksheets("Sheet1").Range(Cells(lStartRow + 1, 3)) Workbooks("統合されるファイル.xls").Close SaveChanges:=False Application.ScreenUpdating = True 現在、このプログラムを走らせると、コピーして貼り付けるところで「アプリケーション定義またはオブジェクト定義のエラーです」という警告が出てコピーされない状況です。 何が悪いのでしょうか？

  • コピー後に値のみ貼り付け　エクセル、VBAの記述について

    マクロ初心者です。 エクセルで選択範囲を指定後コピーし、 自動的に別のシートの末尾に貼り付けられるようにしたのですが、 この内容のまま「貼り付け」を「値のみ貼り付け」に訂正する場合 どのように変更すればいいのか、教えてくださると嬉しいです。 宜しくお願いいたします。 Sub 選択範囲をコピー後、指定シートの末尾に貼り付け Worksheets("sheet1").Activate Range("b11:I17").Copy Workbooks("book2.xls").Worksheets("Sheet1").Activate 行 = Range("B1").CurrentRegion.Rows.Count + 1 ActiveSheet.Paste _ Destination:=Workbooks("book2.xls").Worksheets("Sheet1").Range("B" & 行) End Sub

  • エクセルVBAの別sheetの空白行削除について

    エクセルVBAでBOOK1のsheet1とsheet2とsheet3があり、 sheet1とsheet2の全ての情報をsheet3にコピーしてまとめるようにしました。 マクロを実行するには、Visual Basicを開いてF5を押しています。 それをsheet1かsheet2の中身の一部分でも変更すると そのときに自動的にマクロが実行されるようにしたいです。 sheet1とsheet2（sheet3は空白のまま）に Private Sub Worksheet_Change(ByVal Target As Range) Call マクロ() End Sub を入れ、 次に、標準モジュールに Sub マクロ() '下記よりsheet1とsheet2の内容をsheet3にコピーする Worksheets("sheet1").Range("C1:BE50").Copy _ Destination:=Workbooks("BOOK.xlsm").Worksheets("sheet3").Range("C1:BE50") Worksheets("sheet2").Range("C1:BE100").Copy _ Destination:=Workbooks("BOOK.xlsm").Worksheets("sheet3").Range("C51:BE150") '下記より上記sheet3の状態から余分な空白行を削除する Dim UsedCell As Range Dim Max_Row, RowCount As Integer Set UsedCell = ActiveSheet.UsedRange Max_Row = UsedCell.Cells(UsedCell.Count).Row Application.ScreenUpdating = False For RowCount = Max_Row To 1 Step -1 If Application.WorksheetFunction.CountA(Rows(RowCount)) = 0 Then Rows(RowCount).Delete End If Next Application.ScreenUpdating = True End Sub をやって、sheet1かsheet2のセルを変更すると エクセルが固まってしまいます。 デバックでは Rows(RowCount).Delete がよくないようです。 書き方が間違っているのでしょうか？

  • 別のブックへ貯蓄転記する方法を教えてください。

    請求書をエクセルで作ることになりました。 請求書自体はできたのですが、 請求書内容を別のブックに貯蓄保存がどうしてもできません。 ブック「A」のM1～R1のみが転記対象。VLOOKUPを使ってデータをひいています。 ブック「B」のA3～F3に貯蓄＆転記したいと考えています。 色々なサイトを見て、下記のコードを作ったのですが、貯蓄できません・・・ （上書保存のような状態になります） 初心者のため、何が間違っているのかわかりません。 ご教授いただければと思います。 よろしくお願いいたします。 Sub SAVE() Const Dest = "C:\Users\P\Desktop\Y\B.xlsx" Dim fromR As Long Dim fromRMax As Long Dim toR As Long Dim toRMax As Long '?????? toRMax = Workbooks("B.xlsx").Worksheets("Sheet1").Range("A65536").End(xlUp).Row fromRMax = Workbooks("A.xlsm").Worksheets("Sample").Range("A65536").End(xlUp).Row '?? For fromR = 2 To fromRMax 'Date Workbooks("B.xlsx").Worksheets("Sheet1").Cells(toRMax + fromR - 1, 1).Value = _ Workbooks("A.xlsm").Worksheets("Sample").Cells(fromR, 13).Value 'No. Workbooks("B.xlsx").Worksheets("Sheet1").Cells(toRMax + fromR - 1, 2).Value = _ Workbooks("A.xlsm").Worksheets("Sample").Cells(fromR, 14).Value 'Sub Workbooks("B.xlsx").Worksheets("Sheet1").Cells(toRMax + fromR - 1, 3).Value = _ Workbooks("A.xlsm").Worksheets("Sample").Cells(fromR, 15).Value '13% Workbooks("B.xlsx").Worksheets("Sheet1").Cells(toRMax + fromR - 1, 4).Value = _ Workbooks("A.xlsm").Worksheets("Sample").Cells(fromR, 16).Value '5% Workbooks("B.xlsx").Worksheets("Sheet1").Cells(toRMax + fromR - 1, 5).Value = _ Workbooks("A.xlsm").Worksheets("Sample").Cells(fromR, 17).Value 'Total Workbooks("B.xlsx").Worksheets("Sheet1").Cells(toRMax + fromR - 1, 6).Value = _ Workbooks("A.xlsm").Worksheets("Sample").Cells(fromR, 18).Value Next fromR End Sub

  • Excelマクロ（VBA）のブックとシートのコピーについて

    初めまして、宜しければVBAのブックやシートのコピー（操作）についてご教授お願いいたします。 Windows XP x64 OFFICE2003 を使用しております。 D:\Book1.elxのsheet1のシートをD:\test\Book2.elxのsheet1のシートに コピーする方法が恥ずかしながら理解できておりません。 以下が行いたい事です。 Sub ボタン1_Click() 'text1ブックを開く 'Workbooks.Open "D:\micro\test1.xls" 'ブック間のシートをコピー Workbooks("test2.xls").Worksheets("シート2").Copy _ After:=Workbooks("test1.xls").Worksheets("Sheet2") End Sub VBのファイル操作とは違い、どのように行えば良いのか検索しても同じような部分サンプルのようなものしか無く、理解できておりません。 参考でも結構ですのでご教授いただけませんでしょうか？ よろしくお願いいたします。

  • エクセルVBA の変数を使うべきでしょうか？

    はじめまして。エクセル初心者です。 書籍やサイトで勉強させてもらっていますが、VBAがなかなか難しくてすぐに壁にぶつかってしまいます。少々困ってしまい、詳しい方のアドバイスを頂ければと質問を投稿させていただきました。 どうか宜しくお願い致します。質問ですが、 以下のようなコードで、sheet5のB列の任意のセルをダブルクリックした場合、sheet5のBCD列の同じ行のセル値がsheet1の指定した列に入力されるという処理を作りました。 これで一応目的の動作はするのですが、数が増えると「コンパイルエラー・プロシージャが大きすぎます」というメッセージがでてしまいます。列や行には規則性があるので、もしかしたら変数というものを使ってコードを書き直せばいいのかなと思いネットで調べてみたのですが、今のところさっぱり理解できません。 申し訳ありませんが、分かりやすくご教授いただけないでしょうか。バージョンは2003を使っています。 また、下のコードですと、sheet5のBCDいずれかのセルに空白があった場合、sheet1の列に入力されるときに入力される行がずれてしまいます。今は空白を何かで埋めて対処しているのですが、この問題の解決策も教えて頂けると助かります。どうか宜しくお願い致します。 Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Excel.Range, cancel As Boolean) If Target.Address = "$B$2" Then Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B2") Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C2") Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D2") Worksheets("sheet1").Activate cancel = True End If If Target.Address = "$B$3" Then Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B3") Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C3") Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D3") Worksheets("sheet1").Activate cancel = True End If If Target.Address = "$B$4" Then Worksheets("sheet1").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Target.Value Worksheets("sheet1").Range("H" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("B4") Worksheets("sheet1").Range("K" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("C4") Worksheets("sheet1").Range("M" & Rows.Count).End(xlUp).Offset(1).Value = Worksheets("sheet5").Range("D4") Worksheets("sheet1").Activate cancel = True End If 　　・　　 　　・ 　　・　　 　　・ 　　・　　 　　・ End Sub

  • 横にコピーするには・・。

    教えてください・・。 01.xls・02.xls・03.xlsとあり、その中のシート（01・02・03）をコピーして、全部というシートにまとめたいと思っています。 列にデータを追加したいのですが、行にデータが追加されてしまいます・・。 どうしたらいいのか教えてください。 01のシートがＡからＤまで 02のシートがＥからＧまで 03のシートがＨからＬまで とコピーをしたいと思っています・・。 --------- PathName = ThisWorkbook.Path & "\" ArrBook = Array("01.xls", "02.xls", "03.xls") For i = LBound(ArrBook) To UBound(ArrBook) Workbooks.Open PathName & ArrBook(i) Next Set WS(1) = Workbooks("01.xls").Worksheets("01") Set WS(2) = Workbooks("02.xls").Worksheets("02") Set WS(3) = Workbooks("03.xls").Worksheets("03") Set WS(4) = ThisWorkbook.Worksheets("全部") With WS(4) .Cells.ClearContents .Cells(1, 1) = "Ａ" .Cells(1, 2) = "B" .Cells(1, 3) = "C" .Cells(1, 4) = "D" .Cells(1, 5) = "E" .Cells(1, 6) = "F" .Cells(1, 7) = "G" .Cells(1, 8) = "H" .Cells(1, 9) = "I" .Cells(1, 10) = "J" .Cells(1, 11) = "K" .Cells(1, 12) = "L" .Cells(1, 13) = "M" .Cells(1, 14) = "N" .Cells(1, 15) = "O" .Cells(1, 16) = "P" .Cells(1, 17) = "Q" LastRow(1) = WS(1).Range("A65536").End(xlUp).Row LastRow(2) = WS(2).Range("A65536").End(xlUp).Row LastRow(3) = WS(3).Range("A65536").End(xlUp).Row For i = 1 To 3 LastRow(4) = .Range("A65536").End(xlUp).Row + 1 WS(i).Rows("2:" & LastRow(i)).Copy .Cells(LastRow(4), 1) LastRow(4) = .Range("A65536").End(xlUp).Row End With For i = LBound(ArrBook) To UBound(ArrBook) Workbooks(ArrBook(i)).Close SaveChanges:=False Next End Sub

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

あなたにピッタリな商品が見つかる！ OKWAVEセレクト

質問する