宏可从“主"选项卡中的列表在Excel中创建新的选项卡，并在每个选项卡中使用相同的名称填充单元格 [英] Macro to create new tabs in Excel from a list in a Master tab and populate a cell in each tab with the same name

问题描述

我从主"选项卡中的列表中找到了用于创建和命名新选项卡的代码，但是我还需要在每个新选项卡的单元格中填充名称.此外，我需要每个新选项卡包含一个模板(与主选项卡不同)，该模板只是一个空白表，表头和某些列中内置的一些公式.每个新选项卡应具有完全相同的模板，但要填充一个单元格(表的标题)以匹配选项卡的名称.

I have found code for creating and naming new tabs from a list in a "Master" tab, but I need to also have the name be populated in a cell in each new tab. Further, I need each new tab to contain a template (different from the master tab) which is merely a blank table with headers and some formulas built in to some of the columns. Each new tab should have the exact same template but with one cell (a title for the table) being populated to match the name of the tab.

最后，我需要用户打开工作簿，在主"选项卡中填充一个列表(长度不总是相同，可能只是1个)，然后按一个按钮(运行宏)，并具有根据上述内容创建标签.似乎我可能需要创建一个包含要复制的模板的隐藏选项卡?这可能吗?这里的任何指导将不胜感激.谢谢！

In the end, I need the user to open the workbook, populate a list in the master tab (will not always be the same length and may be just 1), then press a button (run a macro), and have the tabs be created per the above. It seems that maybe I need to create perhaps a hidden tab that contains the template to be copied? Is this possible? Any guidance here would be greatly appreciated. Thanks!

推荐答案

假定您的母版表名为"Master"，而模板名为"Hidden".您应该能够根据需要调整以下代码.

Assuming your master sheet is named "Master" and template is named "Hidden". You should be able to adjust the code below to your needs.

(提交我的答案有点晚了，但是我认为这将为您提供更大的灵活性，因为事情更加清楚了)

(A bit late to submit my answer, but i think this will provide you with more flexibility as it is clearer what is happening)

Private Sub CommandButton1_Click()
Dim masterSheet As Worksheet
Dim hiddenSheet As Worksheet
Dim NewSheet As Worksheet
Dim myBook As Workbook
Dim lastRow As Long
Dim i As Long
Dim namesColumn

'Define your workbook - here set as the active workbook, assuming it contains masterSheet and hiddenSheet
Set myBook = ActiveWorkbook

'Define your worksheets - The sheets are named "Master" and "Hidden" respectively
Set masterSheet = myBook.Worksheets("Master")
Set hiddenSheet = myBook.Worksheets("Hidden")

'Define which column in your master tab the list is - here it's A i.e. column 1
namesColumn = 1

'Find the last row of the sheets list
lastRow = masterSheet.Cells(masterSheet.Rows.Count, namesColumn).End(xlUp).Row

'Cycle through the list - Assuming the list starts in column "A" from the 2nd row
For i = 2 To lastRow
    With myBook
        'New sheet
        Set NewSheet = .Worksheets.Add(After:=.Worksheets("Master"))
    End With

    'Find name of the tab and naming the tab
    tabName = masterSheet.Cells(i, namesColumn)
    NewSheet.Name = tabName

    'Copy from hidden template - You can choose the ranges if predefined or use .Cells(r,c) to do something fancier
    hiddenSheet.Range("A1:F6").Copy _
        Destination:=NewSheet.Range("A2:F7")

    'Paste in e.g. cell A1 i.e. (1,1) the tab name
    NewSheet.Cells(1, 1).Value = tabName
Next i

End Sub

这篇关于宏可从“主"选项卡中的列表在Excel中创建新的选项卡，并在每个选项卡中使用相同的名称填充单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！