我正在尝试创建一个列表,该列表将根据我正在处理的文件而更改,以便我可以使用它在另一个工作表中创建索引。
以下是我用来用工作表名称填充列的内容,但我不知道如何将其转换为名为"的列表;SheetList";。我在VBA方面的经验是我在尝试修改StackOverflow上的内容时学到的。
Private Sub CommandButton1_Click()
Dim myValue As Variant
Dim y As Workbook
Dim x As Workbook
myValue = InputBox("Enter File Name to OPEN IDM Quicksheet e.g. 123.xlsm")
Set y = Application.ActiveWorkbook
Set x = Workbooks(myValue)
For i = 2 To x.Sheets.Count
y.ActiveSheet.Range("A" & i).Value = x.Sheets(i).Name
Next i
End Sub
嘿,我假设SheetList
应该在y中,那么:
' Create "SheetList" sheet in the end of workbook y
y.Sheets.Add(After:=Sheets(Sheets.Count)).Name = "SheetList"
Dim ys As Worksheet
Set ys = y.Sheets(y.Sheets.Count)
' The worksheet names in x will be listed, starting from 2nd row
For i = 2 To x.Sheets.Count + 1
ys.Range("A" & i).Value = x.Sheets(i - 1).Name
Next i