我想在所有现有的工作表末尾添加一个名为"Temp"的Excel表格,但是这段代码无法正常工作:
Private Sub CreateSheet()
Dim ws As Worksheet
ws.Name = "Tempo"
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
End Sub
你能告诉我为什么吗?
试一下这个:
Private Sub CreateSheet()
Dim ws As Worksheet
Set ws = ThisWorkbook.Sheets.Add(After:= _
ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
ws.Name = "Tempo"
End Sub
或者使用With
子句避免重复调用您的对象。
Private Sub CreateSheet()
Dim ws As Worksheet
With ThisWorkbook
Set ws = .Sheets.Add(After:=.Sheets(.Sheets.Count))
ws.Name = "Tempo"
End With
End Sub
如果在代码的其余部分中不需要调用同一工作表上的单元格,上述内容可以进一步简化。
Sub CreateSheet()
With ThisWorkbook
.Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Temp"
End With
End Sub
Sheets.Add(After:=Sheets(Sheets.Count)).Name = "new_sheet_name"
ThisWorkbook
或者ActiveWorkbook
。此外,在这里新的工作表对象没有被分配给一个变量,而之后你几乎总是想要使用它来将数据放入工作表中。 - ZygDThisWorkbook.Sheets.Add After:=Sheets(Sheets.Count)
ActiveSheet.Name = "XYZ"
当您添加一个工作表时,无论如何它都将成为活动工作表。
试试这个:
Public Enum iSide
iBefore
iAfter
End Enum
Private Function addSheet(ByRef inWB As Workbook, ByVal inBeforeOrAfter As iSide, ByRef inNamePrefix As String, ByVal inName As String) As Worksheet
On Error GoTo the_dark
Dim wsSheet As Worksheet
Dim bFoundWS As Boolean
bFoundWS = False
If inNamePrefix <> "" Then
Set wsSheet = findWS(inWB, inNamePrefix, bFoundWS)
End If
If inBeforeOrAfter = iAfter Then
If wsSheet Is Nothing Or bFoundWS = False Then
Worksheets.Add(After:=Worksheets(Worksheets.Count)).Name = inName
Else
Worksheets.Add(After:=wsSheet).Name = inName
End If
Else
If wsSheet Is Nothing Or bFoundWS = False Then
Worksheets.Add(Before:=Worksheets(1)).Name = inName
Else
Worksheets.Add(Before:=wsSheet).Name = inName
End If
End If
Set addSheet = findWS(inWB, inName, bFoundWS) ' just to confirm it exists and gets it handle
the_light:
Exit Function
the_dark:
MsgBox "addSheet: " & inName & ": " & Err.Description, vbOKOnly, "unexpected error"
Err.Clear
GoTo the_light
End Function
尝试使用以下方法:
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = "MySheet"
Function funcCreateList(argCreateList)
For Each Worksheet In ThisWorkbook.Worksheets
If argCreateList = Worksheet.Name Then
Exit Function ' if found - exit function
End If
Next Worksheet
Worksheets.Add (After:=Worksheets(Worksheets.Count)).Name = argCreateList
End Function
当函数创建完成后,可以从主Sub中调用它,例如:
Sub main
funcCreateList "MySheet"
Exit Sub
Private Sub CreateSheet()
Dim ws As Worksheet
Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
ws.Name = "Tempo"
End Sub
thanks,
Call CreateWorksheet("New", False, False, False)
Sub CreateWorksheet(sheetName, preserveOldSheet, isLastSheet, selectActiveSheet)
activeSheetNumber = Sheets(ActiveSheet.Name).Index
If (Evaluate("ISREF('" & sheetName & "'!A1)")) Then 'Does sheet exist?
If (preserveOldSheet) Then
MsgBox ("Can not create sheet " + sheetName + ". This sheet exist.")
Exit Sub
End If
Application.DisplayAlerts = False
Worksheets(sheetName).Delete
End If
If (isLastSheet) Then
Sheets.Add(After:=Sheets(Sheets.Count)).Name = sheetName 'Place sheet at the end.
Else 'Place sheet after the active sheet.
Sheets.Add(After:=Sheets(activeSheetNumber)).Name = sheetName
End If
If (selectActiveSheet) Then
Sheets(activeSheetNumber).Activate
End If
End Sub
这是一个快速简单的命名选项卡添加到当前工作表:
Sheets.Add.Name = "Tempo"
.Name
之前您尚未创建ws
。不要混淆:在尝试访问其属性之前,先实例化变量。 - WGS