Excel (VBA)

Excel VBAに関するフォーラムです。
  • 掲示板への投稿には会員登録(無料)が必要です。会員登録がまだの方はこちら
  • 掲示板ご利用上のお願い」に反するご記入はご遠慮ください。
  • Q&A掲示板の使い方はこちらをご覧ください
トピックに返信
質問

 
(指定なし : 指定なし)
別シートのセルの値を参照する方法
投稿日時: 19/08/01 12:06:08
投稿者: tomkan

知識不足のため、お力をお貸しいただけますと幸いです。
 
現在、マクロを書いております。
1枚目のシートが目次となっておりまして、
2枚目以降に、詳細を記入したシートが数百枚ある状況です。
 
1枚目の目次シートはA列が通し番号、B列に記載内容のタイトル、C列に該当シートへのリンクを記入しております。
今までは末尾へのシートの追加・シート名を変更・各詳細を記入したシート全てのA1セルに【=目次!B○(タイトルの該当セル)】手作業で行っておりましたが、1日に数十枚のシート追加があるため、負担が大きいためマクロで行いたいと考えております。
 
情報を参考に、末尾へシートの追加〜シート名変更のコードを記載してみました。
 
[/code]Sub ボタン1_Click()
 
 Dim WS As Object
 Dim SheetName As String
 Dim i As Integer
InputData:
 
 '
 SheetName = Application.InputBox(Prompt:="シート名を入力", Title:="シート名")
 
  '未入力の場合処理を終了させる
  If SheetName = "" Then Exit Sub
 
  'キャンセルの場合、処理を終了させる
  If SheetName = "False" Then Exit Sub
 
  '数字以外の文字の確認
  If Not IsNumeric(SheetName) Then
   MsgBox "数字のみ"
    GoTo InputData
  End If
 
 
  '同一シート名の有無確認
  For Each WS In Sheets
   If WS.Name = SheetName Then
     MsgBox "利用不可"
     GoTo InputData
   End If
  Next
 
  'シートを末尾に追加
  Worksheets.Add after:=Worksheets(Worksheets.Count)
   
  'シート全体の行の高さを調整
  Cells.Rows.RowHeight = 18
     
  'シート全体の列幅を調整
  Cells.Columns.ColumnWidth = 4
   
  'シート名変更
  ActiveSheet.Name = SheetName
   
End Sub
[/code]
 
こちらのコードは問題なく動くのですが、目次シートのB列のタイトルの中から、該当するものを追加シートのA1に記載することも、マクロで行えればなと思っております。
現状、目次シートのタイトルが頻繁に書き換えられてしまうこともあり、コピペではなく【=目次!B○(タイトルの該当セル)】の式を入力している状況です。
VLookupやINDIRECTを利用しマクロの作成を試みたところ、エラーが勃発してしまい、書き上げることができておりません。
 
【=目次!B○(タイトルの該当セル)】と同様のマクロをご教授いただけますと幸いです。
 
質問及び説明が分かりにくく申し訳ございません。
よろしくお願いします。

回答
投稿日時: 19/08/01 14:29:58
投稿者: Suzu

Range("A1").Formula = "=Sheet1!A1"
 
の様になるのですが、、、
 
・どのシートのA1セルに対し式を入れるのか不明確
  (このままだと、アクティブなシートに対しての操作になる
   マクロ実行中に、関係ないシートをアクティブにされるとそのシートに対し
   操作されます。他の列/幅/シート名も同様)
 
 
1.いちいちダイアログにてシート名を入れるのは面倒
2.どうせならダイアログに入れた値と同じ名称のシートを作り、
  さらにリストに追加できないかと思う
3.結局リストに入れるなら、先にリストに入れて、
  そのリストの値を元にシート名追加した方が楽。
 
  → てな訳で 。。。
 
 
・Sheet1 の A列 の値を元にシートを作成
・作成したシートのA1セルにSheet1の値を参照させる
 
の方がユーザーは楽ちんだと思います。
 
 
 
Sub Sumple()
 
Dim wst As Worksheet
Dim rng As Range
Dim SheetName As String
 
If Intersect(Selection, Worksheets("Sheet1").Columns("A")) Is Nothing Then
Else
  For Each rng In Selection
    For Each wst In Worksheets
      If wst.Name = rng.Value Then
        MsgBox rng.Value & " は 既に存在するシート名です"
        Exit Sub
      End If
    Next
  Next rng
 
  For Each rng In Selection
    'シートを末尾に追加
    With Worksheets.Add(after:=Sheets(Sheets.Count))
      'シート全体の行の高さを調整
      .Cells.Rows.RowHeight = 18
      'シート全体の列幅を調整
      .Cells.Columns.ColumnWidth = 4
      'シート名変更
      .Name = rng.Value
      .Range("A1").Formula = "=Sheet1!A" & rng.Row
    End With
  Next
End If
End Sub
 
 
でも、、

1日に数十枚のシート追加が

 
これ1ブックの中に何シートになるのでしょう?
ユーザー的についかづらくないでしょうか。

投稿日時: 19/08/01 15:14:26
投稿者: tomkan

Suzu さんの引用:

・どのシートのA1セルに対し式を入れるのか不明確
  (このままだと、アクティブなシートに対しての操作になる
   マクロ実行中に、関係ないシートをアクティブにされるとそのシートに対し
   操作されます。他の列/幅/シート名も同様)

式を入れたいのは追加したシートです。
目次シートのA列に記載されている通し番号=追加するシート名になるようにしているため、追加したシートのA1に式を入れ、目次シートの該当のタイトルが書いてあるセル(ダイアログに入れた値と同じ通し番号の列にある)の情報を、追加したシートに記載することが目的です。
 
引用:

でも、、
1日に数十枚のシート追加が
 
  
これ1ブックの中に何シートになるのでしょう?
ユーザー的についかづらくないでしょうか。

応対履歴まとめているのですが、月毎に新しいブックの作成をする決まりとなっており、平均ではありますが最終的には閑散期で300〜500枚、繁忙期だと1000枚超えるくらいのシート枚数になっております。
かなり使いづらく、上長にも改善要望は上げているのですが、許可が下りずな状況でして。

回答
投稿日時: 19/08/01 16:57:12
投稿者: Suzu

tomkan さんの引用:
式を入れたいのは追加したシートです。

 
質問者さんのその意図は判ります。
しかし【追加したシート】ではなく
【アクティブなシート】に対して 列幅、行幅、シート名を操作しています。
 
コードにより、ワークシートを追加した直後にアクティブなシートをユーザーが変えたら。。。
変えたシートに対し、列幅、行幅、を変更する事になります。
 
なので、「不明確」と表現しています。
 
追加したシートに対し列幅、行幅、シート名、(A1セルの数式)を変えるようにすべきです。
 
 
 
引用:
A1に式を入れ

その方法は、提示したコードで 示しています。
ご確認ください。
 
 
 
引用:
上長にも改善要望は上げているのですが、許可が下りずな状況でして。

それが仕様と諦めるのか、引き続き改善を要望するのかでしょう。
 
月々のファイルに分けるという事は、過去の履歴を調べたいとき
年月のアタリをつけてファイルを開くか、Windowsの検索機能からファイルを特定し開くか・・
しか無いのですよね。
 
私なら、データベースとして運用するべき案件と考えます。
 
ここで言うデータベースは
横方向に項目名、縦方向がシートの役割って事です。
そうすれば、年月に縛られず「項目」でフィルターを掛ければ良いですよね。
 
縦方向に並べて詳細を一画面で見る事や、印刷が必要なら
そういうシートなりフォームを作るという事です。

回答
投稿日時: 19/08/22 09:09:57
投稿者: WinArrow
投稿者のウェブサイトに移動

>1枚目の目次シートはA列が通し番号、B列に記載内容のタイトル、C列に該当シートへのリンクを記入しております。
現在の仕様では、シートの内容を変更したときに、タイトルも変更することがあると思います。
この時、わざわざ、目次シートを変更しなくていけなくなります。
(どの行に書かれているかを探す手間も増えます)
 
 
この仕様を少し変えてみるのもよいでしょう。
 
そもそもの「目次シート」の役割を、再確認してみましょう。
「目次」は、本体であるシートに一発でジャンプできる機能を持たせるものと考えます。
その機能は、書籍でいえば、索引簿ですから、本来は、各シートが完成後に作成するものです。
このような観点で見直すと・・・・
各シートから目次に書かれているタイトルを参照するのではなく、目次が各シートのタイトルを参照する
方がよいのではないでしょうか?
 
書籍の検索簿では、あいうえお順に並んでいますよね?
シートの件数が数百もあったら、まず目的のシートを探す
ということから作業が始まります。
A列の通し番号では、役に立ちませんから、当該シートの書かれている「タイトル」で探すものと思います。
しかし、その「タイトル」順に並べられていないと、探すのが大変だと思います。
 
そのためには、目次の中にシート名が必要であり、各シートでは、タイトルのセルを固定する
という仕様になります。
それから、シートを追加したら、タイトルで並べ替えする機能も必要になります。
 
A列を通し番号にするのではなく、シート名にしたらいかがですか?
つまり、番号をシート名にするということです。
そうすれば、シート名を入力するダイアログは不要になります。
 
思い付きで書いている部分もありますから、
もう一度、「目次」とは何か?・・(機能)を整理してみましょう。
 
 
 

トピックに返信