0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

More than 3 years have passed since last update.

EXCEL VBA シートから一覧作成マクロ

Posted at

たまに、シートが多いファイルを利用する時とかシートで分けて作成して、目次を作る必要がある場合もあります。
その場合、量が多いと、手動でするのはつらいので、作成して見ました。

SheetListMake
Sub SheetListMake()

    Dim list_sht As Worksheet
    Dim sht As Worksheet
    Dim i As Integer
    Dim t1 As Range
    Dim t2 As Range
    Dim condition As Range
    Dim d1 As Range
    Dim d2 As Range
    Dim d3 As Range

    'リストを書き出すシート名
    Set list_sht = Worksheets("list_sheet_name")
    i = 2
 
    For Each sht In Worksheets
        Set condition = sht.Range("A1")
        If condition = "targetText" Then
            Set t1 = sht.Range("C1")
            Set t2 = sht.Range("C2")
            Set d1 = list_sht.Range("B" & i)
            Set d2 = list_sht.Range("F" & i)
            Set d3 = list_sht.Range("J" & i)           

            d1.Value = "=Hyperlink(""#" & sht.Name & "!A1"", """ & t1.Text & """)"
            d2.Value = t2.Text
            Debug.Print sht.Name & " | " & t1.Interior.ColorIndex

            If t1.Interior.ColorIndex = 16 Then
                d3.Value = "N"
            Else
                d3.Value = "Y"
            End If

            i = i + 1
        End If
    Next sht

End Sub
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?