모든 Excel 시트 끝에 명명 된 시트를 추가하는 방법은 무엇입니까?

모든 Excel 시트 끝에 명명 된 시트를 추가하는 방법은 무엇입니까?

---

모든 기존 시트 끝에 "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

---

다음 1 개의 라이너를 사용하십시오.

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "new_sheet_name"

---

ThisWorkbook.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

---

이렇게하면 다음 옵션이 제공됩니다.

1. 이름이 같은 탭을 덮어 쓰거나 유지합니다.
2. 시트를 모든 탭의 끝 또는 현재 탭 옆에 배치합니다.
3. Select your New sheet or the Active one.

---

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

---

Try switching the order of your code. You must create the worksheet first in order to name it.

Private Sub CreateSheet()
    Dim ws As Worksheet
    Set ws = Sheets.Add(After:=Sheets(Sheets.Count))
    ws.Name = "Tempo"
End Sub

thanks,

---

This is a quick and simple add of a named tab to the current worksheet:

Sheets.Add.Name = "Tempo"

참고URL : https://stackoverflow.com/questions/20697706/how-to-add-a-named-sheet-at-the-end-of-all-excel-sheets