エクセルVBAで変数で定義したシート名を数式に組み込む方法

前へ 次へ
このQ&Aのポイント
  • エクセルVBAで数式の中に変数で定義したシート名を入力する方法について教えてください。
  • VBAに書き込む数式が長すぎて書き込めない場合、シート名を変数として定義し、省略する方法があるか知りたいです。
  • 具体的な変数の設定方法と数式への組み込み方を教えてください。
回答を見る
  • ベストアンサー

エクセルVBA　数式の中に、変数で定義したシート名を入力するには

いつもお世話になります。 =SUMPRODUCT(('[BOOK1.xls]SHEET1!$D$20:$D$1000=$E$4)*('[BOOK1.xls]SHEET1!$D$20:$D$1000!$O$20:$O$235>=$B9)*('[[BOOK1.・・・ という長い数式を、VBAに書き込みたいのですが、関数が長すぎるせいか、書き込めません。 そこで、 Dim SH1 As Worksheet SET SH1 = Workbooks("BOOK1.xls").Worksheets("SHEET1") として、[BOOK1.xls]SHEET1!をSH1に省略したいのですが、どのように数式に組み込めば良いでしょうか。

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

  • ベストアンサー
  • hogehage
  • ベストアンサー率50% (54/107)
回答No.1

オブジェクト変数ではなく、文字列の変数として & で繋げてしまえばよいと思います。 （例） T_SHEET = "[BOOK1.xls]Sheet1!" Range("B2").Formula = "=SUM(" & T_SHEET & "$A$1)"

7-samurai
質問者

お礼

"[BOOK1.xls]Sheet1!"ではうまくいきませんでしたが、前後に「'」をつけるとうまく行きました。 ありがとうございました。

  1. カテゴリ
  2. パソコン・スマートフォン
  3. ソフトウェア
  4. オフィス系ソフト

関連するQ&A

  • VBAのオブジェクト変数について

    人に教えなければいけないことなので、、、 困っています。 あるVBAのテキストを見て、そのテキストをそのまま入力しても実行できません。 （条件としては、Book1.xlsとBook2.xlsというファイルを開いた状態で、Book1.xlsのほうに、以下のモジュールを入力します。） Sub Set1() Dim myBook As Workbook Dim mySheet As Worksheet Dim myCell As Range Set myWBook = Workbooks("Book2.xls") Set myWSheet = Worksheets("Sheet2") Set myCell = Range("A1:D10") myWBook.Activate myWSheet.Activate myCell.Value = "ABC" End Sub これを実行すると、アクティブな状態のファイルにしか、値"ABC"が入ってこないのです。テキストでは、Book2.xlsのSheet2のA1:D10に値"ABC"が入ってくると言っていますが、Book1.xlsに値が入ってしまったりします。 長くなってしまってすみません。 もちろん、他の方法で実現することができるのはわかるのですが、なぜこのコードが実行できないのかがわかりません。 理由を教えていただけたら・・・と思います。 よろしくお願いいたします。

  • エクセルVBA　無駄な部分をおしえてください

    VBA初心者です。 多数のシートを条件によって二つのブックに分ける、というVBAを作ろうとしています。 なにぶん素人なので、無駄な文章が多いのではないかと心配で、 お知恵を拝借できればと思い投稿いたしました。どうぞよろしくお願いいたします。 やりたいこと：Book1のA列に100程度の文字列があり、そのいずれかと一致するシート名（Book1のSheets(2)以降）を持つシートはBook2の最終シートの後ろへ、どの文字列ともシート名が一致しないシートはBook3の最終シートの後ろへ移動。（「最終シートの後ろへ移動」がうまくいっていません） VBAの内容：Book1のH1に「=countif(A:A,G1)」と入力しておき、G1にシート名を入力させ H1>0ならば該当シートをBook2へ、それ以外はBook3へ移動　の繰り返し 　　Application.ScreenUpdating = False Dim j As Integer, k As Integer j = Workbooks("Book2.xls").Worksheets.Count k = Workbooks("Book3.xls").Worksheets.Count Do While Workbooks("Book1.xls").Sheets.Count > 1 Range("G1").Value = Worksheets(2).Name If Range("H1").Value > 0 Then Worksheets(2).Move after:=Workbooks("Book2.xls").Sheets(j) Else Worksheets(2).Move after:=Workbooks("Book3.xls").Sheets(k) End If Loop

  • excel vba

    テーブル情報に基づきシートをコピーするＶＢＡマクロを記述したい。 ＥｘｃｅｌのBook111のSheet1に次のような データが入っています。 （１、２）セルにｎ=3という数字が入っているものとします。 その数値に合わせて、この場合は３なので Sub Sample01() Workbooks("Book3.xls").Worksheets("booksheet3").Copy After:=Workbooks("Book111.xls").Sheet（1) End Sub のようなつまり、booksheet3なるシートをBook111(固定なＢＯＯＫ)にコピーしようとしています。 このようなことをＶＢＡで書くにはどうすればいいのでしょうか。 ただしBook名とシート名はあくまでデータの値にもとづいたデータをもってくることになります。 要するに間接参照したデータに基づき処理するのをどのように記述するかという質問です。 Ａ列 Ｂ列 1 回数 ｎ=3 2 　 　 3 　 　 4 Book1.xls book1sheet ←n=1 5 Book2.xls book2sheet ←n=2 6 Book3.xls book3sheet ←n=3 7 Book4.xls book4sheet 8 Book5.xls book5sheet 9 Book6.xls book6sheet ←n=6 10 Abc,xls Defsheet ←n=7

  • 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のファイル操作とは違い、どのように行えば良いのか検索しても同じような部分サンプルのようなものしか無く、理解できておりません。 参考でも結構ですのでご教授いただけませんでしょうか？ よろしくお願いいたします。

  • Excel VBA 指定シートの取込

    こんにちは。 ExcelのVBAを使用して、異なるBookのシートを取込みたいのですが、 シートが無かった場合の処理方法がわかりません。 現在のコードは下記の様になっております。 With Workbooks.Open"BOOK1.xls" .Worksheets("Sh1").Cells.Copy ThisWorkbook.Sheets("Sheet1").Range("A1") .Worksheets("Sh2").Cells.Copy ThisWorkbook.Sheets("Sheet2").Range("A1") .Worksheets("Sh3").Cells.Copy ThisWorkbook.Sheets("Sheet3").Range("A1") .Close End With Book1に指定したシートが無い場合、何もしないようにしたいのですが、 どの様に書き換えれば宜しいでしょうか？ よろしくお願いします。

  • ExcelのVBAについてです。

    例えば"Book1.xls"という名前のブックの"Sheet1"という名前のシートの一部を削除する。 これをVBAでやらせようと思うんですが、"Book1.xls"を開かずに行うことはできるんでしょうか。 Workbooks("Book1.xls").Worksheets("Sheet1").Range("C7:M51").ClearContents のようにしてるんですがうまくいきません。 "Book1.xls"はLAN上のブックで実際はフルパスで指定してます。 よろしくお願いします。

  • エクセル 何故かシート間の値のコピーが出来ない

    いつもお世話になります。 開いているブックのシート「リスト1～3」に、Book1.xlsの「リスト1～3」の値をコピーする為に、下記のマクロを作成しました。 Dim SH1, SH2, SH3, SH4, SH5, SH6 As Worksheet Set SH1 = ThisWorkbook.Worksheets("リスト1") Set SH2 = ThisWorkbook.Worksheets("リスト2") Set SH3 = ThisWorkbook.Worksheets("リスト3") Set SH4 = Workbooks("Book1.xls").Worksheets("リスト1") Set SH5 = Workbooks("Book1.xls").Worksheets("リスト2") Set SH6 = Workbooks("Book1.xls").Worksheets("リスト3") 'リスト1をコピーする D = SH4.Range("A1").CurrentRegion.Rows.Count E = SH4.Range("A1").CurrentRegion.Columns.Count SH1.Range(Cells(1, 1), Cells(D, E)).Value = SH4.Range("A1").CurrentRegion.Value 'リスト2をコピーする F = SH5.Range("A1").CurrentRegion.Rows.Count G = SH5.Range("A1").CurrentRegion.Columns.Count SH2.Range(Cells(1, 1), Cells(F, G)).Value = SH5.Range("A1").CurrentRegion.Value 'リスト3をコピーする H = SH6.Range("A1").CurrentRegion.Rows.Count I = SH6.Range("A1").CurrentRegion.Columns.Count SH3.Range(Cells(1, 1), Cells(H, I)).Value = SH6.Range("A1").CurrentRegion.Value 以上を実行すると、「アプリケーション定義またはオブジェクト定義のエラーです」とエラーメッセージが出てしまいます。 それぞれのシートの処理の時に、 SH1.Select SH2.Select SH3.Select を入れて、シートを選択してから実行すると問題なく動くのですが、何故このようなことが起こるのでしょう？

  • ブック名、シート名を他のモジュールからも参照可能にする方法

    マクロを書く場合に次のように宣言しています。 Sub test() '(1)始まり Dim WB1 As Workbook Dim SH1 As Worksheet Set Wb1 = Workbooks("Book1") Set Sh1 = Wb1.Worksheets("Sheet1") '(1)終わり Msgbox Sh1.Cells(1, 1) end Sub 新たにマクロを書く場合には、その都度上記コードの「'(1)始まり～'(1)終わり」を書いていますが、面倒です。 「Public ～」などのように一箇所に宣言しておけば,他のモジュールからもブックやシートを参照できるようにする方法は無いでしょうか？ よろしくお願いします。

  • AccessでExcelのSheet間のコピーをしたい

    ACCESSのデータを読み込み、エクセルの元帳からレイアウトをコピー、新しいExcelのSheetにレイアウトとデータを書き込もうとしていますがうまく動きません。どこが悪いのでしょうか？次のように書きました。 Private Sub NEW_BOOK() Dim xlBookOrig As Workbook Dim xlBookNew As Workbook Dim xlSheetOrig As Worksheet Dim xlSheetNew As Worksheet Set xlApp = CreateObject("Excel.Application") xlApp.Application.Visible = True Set xlBookOrig = xlApp.Workbooks.Open("C:\元帳.xls") Set xlSheetOrig = xlBookOrig.Worksheets(1) Set xlBookNew = xlApp.Workbooks.Add Set xlSheetNew = xlBookNew.Worksheets(1) xlSheetOrig.Copy xlSheetNew.Paste xlSheetNew.Name = Left$(strMODL, 9) xlSheetNew.Cells(5, 1).Value = strSTAT xlSheetNew.Cells(4, 3).Value = strITEM ..... xlSheetNew.SaveAs "C:\'" & Left$(strMODL, 7) & "'.xls" これで実行すると、BOOK1 BOOK2 の2つが生成され、 BOOK1にはSHEET名、各データが書き込まれ、BOOK2には元帳のレイアウトがコピーされレイアウトとデータが一つになりません。 どこが間違っているのでしょうか？ 宜しくお願いします

  • エクセルのシートのコピーについて

    シートのコピーをＶＢＡで行いたいのですが、エラーになってしまいます。 間違っている箇所が分からないのでご教授お願いします。 貼り付けというブックにマクロが組まれています。 ”データ”のブックにあるシート名が”貼り付けのブックのリスト”のシートに記載されています。 リストのシートに記載されているシートを貼り付けのブックにコピーしたいです。 よろしくお願いします。 Sub シートコピー() 行数 = 2 Do Until IsEmpty(Cells(行数, 3).Value) コピー元 = Workbooks("貼り付け.xls").Worksheet("リスト").Cells(行数, 3) Workbooks("データ.xls").Worksheet(コピー元).Copy After:=Workbooks("貼り付け.xls").Sheets(Workbooks("貼り付け.xls").Sheets.Count) 行数 = 行数 + 1 Loop End Sub

注目のQ&A

カテゴリ

専門家に質問してみよう

職業から探して質問する

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

質問する