想自动在excel中创建工作表 [英] Want to create worksheets in excel automatically

问题描述

基本上我想要创建一个索引，我想链接我创建的每个表单，我命名为主要的第一个页面，试图记录一些宏，然后与他们一起工作，但我意识到我不知道如何工作与vb，因为我不能使它的工作，因为我只有一点知识在java我以为我可以做一些事情。
编辑：我的想法是每当我创建这样的表单时，自动添加我创建的页面（一列），在创建索引后，完成所有的页面。
一旦我创建它，就尝试hipplink页面，一旦我从一个特定的行输入一个单元格，就创建一个工作表。任何想法如何让它工作？这是我的两次尝试。

  Sub Sheetcreation（）
 If（Sheets.Add（After））= True然后[Main.Hyperlinks.Add Anchor：= Selection，Address：=，SubAddress：= _ 
Hoja1！A1，TextToDisplay：=（SheetName）] [Else []] 
 End Sub 
 
 Sub Sheetcreate（）
 Principal.Hyperlinks.Add Anchor：= Selection，Address：=，SubAddress：= _ 
Hoja1！A1，TextToDisplay：=（ SheetName）
 End Sub 
  

提前感谢，有一个愉快的一天。 >

解决方案

将此代码放在您想要链接驻留的工作表的模块中：

  Private Sub Worksheet_Change（ByVal Target As Range）
如果Target.Column = 1和Target.Value<> 然后调用Module1.SheetCreation（Target.Value，Target.Item（1））
 End Sub 
  

然后把这个代码放在一个添加的模块中：

  Sub SheetCreation（n As String，r As Range）
'创建工作表
 Worksheets.Add（After：= Worksheets（Worksheets.Count））。Name = n 
 r.Parent.Activate 
 
'添加超链接
 r.Parent.Hyperlinks.Add _ 
 Anchor：= r，_ 
地址：=，_ 
 SubAddress：='& n& '！A1，_ 
 TextToDisplay：= n 
 End Sub 
  

您会注意到，如果更改过程名称，或者您的模块未命名为Module1，则必须相应更改 Worksheet_Change 中的代码。 / p>

添加代码后，如果需要更改代码，您可以在选择的工作表的列A中键入值。然后，代码将创建一个以您输入的值命名的新工作表，然后创建一个到该工作表的超链接。

Basically I want to create something like an index, I want to link every sheet I create to the fist page which I named Main, tried to record some macros and then work with them but I realize i have no idea how to work with vb since I can´t make it work, since I only have a little knowledge in java I thought I could do something. edit: My idea is to add automatically the pages I create to the index (a column) whenever I create such sheet, not creating the index "after" finishing all the sheets. Tried to hiperlink the page as soon as I created it and "create" a worksheet as soon as I typed something into a cell from a specific row. Any ideas how could I make it work? here are my two derpy attempts.

Sub Sheetcreation ()
    If (Sheets.Add(After)) = True Then [ Main.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "Hoja1!A1", TextToDisplay:=(SheetName) ] [ Else [  ] ]
End Sub

Sub Sheetcreate ()
  Principal.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _
    "Hoja1!A1", TextToDisplay:=(SheetName)
End Sub

Thanks in advance guys, have a nice day.

解决方案

Put this code in the module for the sheet you want your links to reside in:

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Column = 1 And Target.Value <> "" Then Call Module1.SheetCreation(Target.Value, Target.Item(1))
End Sub

Then put this code in an added module:

Sub SheetCreation(n As String, r As Range)
    ' Create the sheet
    Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = n
    r.Parent.Activate

    ' Add hyperlink
    r.Parent.Hyperlinks.Add _
        Anchor:=r, _
        Address:="", _
        SubAddress:="'" & n & "'!A1", _
        TextToDisplay:=n
End Sub

You'll notice that if you change the name of the procedure, or if your module is not named "Module1", you will have to change accordingly the code in Worksheet_Change.

Once the code is added, and changed if needed, you can type values into column A of the sheet you chose. The code will then create a new worksheet named after the value you inputted, and then create a hyperlink to that sheet.

这篇关于想自动在excel中创建工作表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！