엑셀(EXCEL) VBA로 시트 이름이 변경할 때 겹치는 이름 처리하기(중복되는 시트 지우고 다시 만들기와 번호 붙여서 만들기)

 

 

엑셀에서는 보통 여러개의 시트를 사용해서 문서를 구성합니다. 

VBA에서 이름이 겹치는 경우 바로 에러를 발생하면서 종료되는데요.

시트 이름 겹치기 에러에 대해서 처리하는 방법에 대해서 알아보겠습니다.

 

 

엑셀 시트 리스트

 

일반 엑셀 인터페이스에서는 이름은 겹치지 않도록 되어있고 만일 겹치게 되면, 

"이미 사용되고 있는 이름입니다. 다른 이름을 선택해주세요."라는 메시지가 출력됩니다.

올바른 이름을 설정할 때까지 여기서 빠져나가지 못하게 되어 있습니다.

또 VBA에서는 '1004' 런타임 오류가 발생하였습니다. 라고 하면서 코드가 종료됩니다.

 

시트 이름 에러

 

 

이 '1004' 런타임 에러는 꼭 회피 해야 하는 현상 중 하나입니다.

엑셀 내에서는 이름이 같은 시트를 허용하지 않고 예외처리도 안되게 되어 있습니다.

이번 포스팅에서 시트를 만들어서 이름을 할당할 때 중복되는 시트를 어떻게 처리하는지 알아보겠습니다.

 

 

 

1. 중복되는 시트가 있으면 지우고 새로 만들기 

 

깔끔하게 처리하는 방법입니다.

기존에 있는 시트에 예상하지 못한 작업물까지도 처리가 가능한 장점이 있습니다.

주로 임시로 발생하는 데이터를 저장하는 용도의 시트등에 사용가능합니다.

 

 

<< 코드 >>

Sub make_sheet()
' 시트 이름은 A로 지정하겠습니다.
On Error GoTo 만들기    '오류가 나면 만들기로 이동하기
    Set ws1 = ThisWorkbook.Sheets("A")
' 시트삭제시 나오는 경고 메시지 끄기
    Application.DisplayAlerts = False
        ThisWorkbook.Sheets("A").Delete
    Application.DisplayAlerts = True
만들기:
    Set wsTemp = ThisWorkbook.Sheets.Add
    wsTemp.Name = "A"
On Error GoTo 0 ' 오류 처리 종료

End Sub

 

 

① On Error GoTo 만들기 : 에러가 발생하면 "만들기" 항목으로 이동합니다.

② Set ws1 = ThisWorkbook.Sheets("A") : ws1이라는 개체에 시트 "A"를 할당합니다.

  - 시트 A가 있으면 아래줄로 이동합니다.

  - 시트 A가 없으면 오류가 남으로 "만들기"로 이동합니다.

③ Application.DisplayAlerts = False : 시트를 지울 때 뜨는 에러메시지를 잠시 끕니다.(사용자가 번거로움으로)

                                                            목적이 달성된 후에 다시 True로 변경합니다.

④ ThisWorkbook.Sheets("A").Delete : 시트 A를 지웁니다.

⑤ 만들기 : 

  - Set wsTemp = ThisWorkbook.Sheets.Add : 시트를 만듭니다.

  - wsTemp.Name = "A" : 만들어진 시트에 A라는 이름을 붙입니다.

 

결과 코드는 A라는 시트가 있으면 지우고 다시 만들고, 없으면 시트를 만들어 A라는 이름을 붙입니다.

 

 

 

2. 시트가 있으면 뒷 번호를 달아서 계속 만들기

 

자주 사용하는 방법입니다.

기존의 정보를 유지하면서 계속해서 시트를 만들어내서 정보의 손실이 없습니다.

다만 너무 많이 작업하면 늘어난 작업물을 따로 사용자가 삭제해야 합니다.

 

<< 코드 >>

Sub make_sheet_2()
' 시트 이름은 B로 지정하겠습니다.
' 워크시트 ws를 nothing으로 초기화 하기 위해서 선언
Dim ws As Worksheet

On Error Resume Next ' 오류 발생 하면 무시하고 다음 줄로 이동
    Set ws = ThisWorkbook.Sheets("B")
On Error GoTo 0 ' 오류 처리 종료

If Not ws Is Nothing Then
' 시트가 존재하면 IF문 실행하기
' 시트가 없을 때까지 DO - LOOP문 실행하기
    Do
        wsCount = wsCount + 1 ' 번호 올리기
        newPasteSheetName = "B" & wsCount   ' 번호 붙이기
        Set ws = Nothing
        On Error Resume Next
        Set ws = ThisWorkbook.Sheets(newPasteSheetName) ' 번호가 붙여져 있는 이름이 있는지 찾기
        On Error GoTo 0
    Loop While Not ws Is Nothing ' 있으면 다시 하기
Else
        newPasteSheetName = "B" ' 시트가 없으므로 이름 그대로 사용
    End If
    
' 새로운 시트 생성하여 이름 붙이기
    Set wsB = ThisWorkbook.Sheets.Add
    wsB.Name = newPasteSheetName

End Sub

 

 

① Dim ws As Worksheet : 워크시트 ws를 선언하면 ws가 nothing으로 초기화 되어있습니다.

                                           다른 방법으로 만들면 ws는 empty로 저장됨으로 뒤에 코드가 작동하지 않습니다.

② On Error Resume Next : 에러가 나면 무시하도록 합니다.

③ Set ws = ThisWorkbook.Sheets("B") : B라는 시트가 없으면 오류가 나고 무시함으로 ws는 계속 nothing입니다.

                                                                 B라는 시트가 잇으면 뭐라도 들어가게 됩니다.

④ If Not ws Is Nothing Then : ws가 있으면 동작하는 IF문입니다.(정확하게 nothing이 아니면 동작합니다.)

⑤ Do ~ Loop While Not ws Is Nothing : ws가 없을 때까지 반복합니다.(정확하게는 ws가 nothing 아니면 멈춤)

⑥ wsCount = wsCount + 1, newPasteSheetName = "B" & wsCount : 번호를 붙입니다.

⑦ Set ws = ThisWorkbook.Sheets(newPasteSheetName) : 번호가 붙은 시트가 있는지 확인합니다.

 

시트 이름이 없을 때 까지 번호를 붙여서 나아갑니다.

엑셀에서 허용하는 워크시트의 개수인 255개가 될때까지 반복해서 실행합니다.

 

---

 

오늘 사용하는 코드는 간간히 지인들에게 코드를 짜서 줄때 시트이름을 막 바꿔도 오류가 안나는 소스입니다.

제가 혼자 쓰는 경우에는 귀찮아서 작성하지 않다보니 자주 까먹고 다시 만들어야 하더군요.

이번 기회에 이렇게 정리해봅니다.

위에 이미 소스가 있어서 크게 예제가 필요하지는 않겠지만 첨부도 올려봅니다.

 

VBA 예제-시트 만들기.xlsm

0.02MB