VBA代码基于A列中的值创建图纸 [英] VBA Code to Create Sheets based on the values in column A

问题描述

我正在寻找一个代码来创建名称为A列的工作表.我使用了此代码，但它无法满足我的要求.代码是;

I am looking for a code to create sheets with the name in column A. I have used this code but it is not fulfulling my requirement. The code is ;

Private Sub CommandButton1_Click()    
Dim sheetCount As Integer    
Dim sheetName As String    
Dim workbookCount As Integer    

With ActiveWorkbook    
sheetCount = Sheets(1).Range("A2").End(xlDown).Row    
For i = 2 To sheetCount Step 1    
sheetName = .Sheets(1).Range("A" & i).Value    
workbookCount = .Worksheets.Count    
.Sheets.Add After:=Sheets(workbookCount)    
.Sheets(i).Name = sheetName    
'.Sheets(i).Range("A" & i, "F" & i).Value = .Sheets("sample").Range("A" & i, "F" & i).Value    
Next    
End With    

Worksheets(1).Activate    

End Sub

第一次运行此代码时，它将创建带有A列中存在的文本的工作表.但是问题是，当我在该列中输入新文本时，它也会生成以前的工作表.我正在寻找一个代码，该代码仅使用在该列中输入的新文本来创建工作表，而不创建已经完成的工作表.请帮我解决这个问题，因为我尝试了太多但没有找到任何代码.

Upon running this code in first go, it creates sheets with the text present in column A. But the problem is when i entered new text in that column, it makes previous sheets as well. I am looking for a code which only create the sheets with the new text being entered in the column and donot make sheets which are already made. Kindly help me out on this as i tried too much but didnt find any code.

谢谢

推荐答案

这对我有用，并经过了测试:注意，如果尝试使用保留的历史记录"之类的名称，则会出现错误.我不知道所有保留的名称.

This works for me, and is tested: Note, if you try to use a name like "History" that is reserved you will get an error. I am not aware of all the reserved names.

Private Sub CommandButton1_Click()
Dim lastRow As Long
Dim sheetName As String
Dim workbookCount As Long
Dim ws As Worksheet
Dim match As Boolean

lastRow = Sheets("Sheet1").Range("A2").End(xlDown).Row

For i = 2 To lastRow
    match = False
    sheetName = Sheets("Sheet1").Cells(i, 1).Text

    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name = sheetName Then
            match = True
        End If
    Next

    If match = False Then
        Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = sheetName
    End If

Next i

End Sub

添加了屏幕截图

这篇关于VBA代码基于A列中的值创建图纸的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！