엑셀 시트 이름 가져오기


이번 시간에는 엑셀 시트 이름 가져오기를 해보도록 하겠습니다. 

시트를 많이 사용하지 않으시는 분들은 굳이 필요하지 않겠지만 제 블로그에 오신 분 중 시트 100개를 관리하시는 분이 있으시더라고요.





시트를 이동하려면 밑에 화살표를 누르거나 해야 할 텐데 만약 회사에서 이렇게 많은 시트를 관리하려면 어떻게 해야 할까 생각하다가 책 앞에 있는 목차처럼 시트 이름만 있는 시트를 만들고 하이퍼 링크를 걸어 해당 시트로 바로 이동하면 좋을 것 같아서 시작하게 되었습니다.


그런데 문제가 있더라고요. 

첫 번째 문제는 100개의 시트의 이름을 하나씩 셀에 입력을 해야 하느냐는 것이고, 두 번째 문제는 하이퍼링크를 일일이 걸어 줘야 하는 것이 문제였습니다.



그래서 우선 엑셀 파일에 있는 시트의 이름을 가져오는 방법부터 해결하였습니다.

(지식인에 어떤 분이 답변으로 소스코드를 달아 놓으셨더라고요. 그런데 엑셀에 초보시거나 매크로를 한 번도 안 해보신 분들이 있으실 것 같아 다시 정리를 했습니다. 정말 감사합니다.) 



엑셀 시트 이름 가져오기


1. 첫 번째 시트에 이름을 "시트목록"으로 변경을 해줍니다.



2. 메뉴에서 [개발 도구]- 2번 [삽입] - 3번을 클릭하여 화면에 그려 줍니다.



4. [새로 만들기]를 클릭하여 새로운 매크로를 만들어 줍니다.




5. 코드를 입력할 수 있는 입력 창이 나타납니다. sub ~ End sub 사이에 코드를 넣어 줍니다.


아래 코드를 복사하여 붙여넣기를 하면 됩니다. 다운로드:  엑셀 시트 코드.txt


 Dim Ws As Worksheet

  Dim ix As Integer, it As Integer


  Set Ws = Sheets("시트목록")

  With Ws

     it = Worksheets.Count

     .Columns("A:B").EntireColumn.ClearContents

     .Range("a1:b1") = Array("No", "시트명")

     For ix = 1 To it

        .Cells(ix + 1, 1) = ix

        .Cells(ix + 1, 2) = Sheets(ix).Name

    Next ix

    .Columns("A:B").EntireColumn.AutoFit

  End With



6. "시트목록"이라고 표시한 시트에 아래 시트 이름을 A1, B1 열에  No, 시트명을 적고 두 번째 행부터 시트명을 넣어라는 뜻입니다.



7. 화살표 부분 X를 클릭하여 닫고 나옵니다.



8. [단추 1] 오른쪽 마우스로 선택하면 [텍스트 편집]이 나오는데 안에 이름을 변경할 수 있습니다.  




9. 버튼을 왼쪽 마우스로 클릭하면 매크로가 실행되어 시트 이름을 가져옵니다.


한 파일에 있는 모든 시트의 이름을 가져올 수 있습니다. 만약 시트 이름이 변경되었다면 다시 버튼을 클릭하여 시트 이름을 불러오면 됩니다.


여기서 각 시트명에 하이퍼 링크를 걸어 주면 되는데요. 하나씩 선택하고 오른쪽 마우스를 클릭하여 하이퍼링크를 걸어주는 방법이 있고 하이퍼링크 함수를 사용하여 거는 방법이 있습니다.


하이퍼링크를 사용하여 링크를 거는 방법은 다음 시간에 알아보도록 하겠습니다.


댓글(23)

  • 지니
    2017.02.13 16:54

    좋은 자료 잘 봤습니다.
    데이터 통합작업중이였는데 딱 필요한 내용이네요.
    궁금한게 있는데 시트이름과 동시에 그 시트의 내용을 가져오는 방법은 없을까요?

  • 지니
    2017.02.14 13:51

    답변감사합니다~
    추가질문이 있습니다.
    불러온 시트명중에 "시트목록"시트는 빼고 두번째 시트부터 1번으로 하는 방법은 없는지요?

    그리고 시트 파일 전체를 합치는게 아니라 일부분의 데이터만 추출해서 정렬(?)하는 방법은 없을까요?
    여기저기 검색해봐도 원하는 방식이 안나와서요
    질문이 많아서 죄송합니다.

    • 2017.02.15 16:24 신고


      ^^ 별말씀을요. 저도 한번 변경을 해주려고 했는데요. 그럼 다시 복잡해지더라고요. 숫자 1를 2로 변경해주면 되는데요 그렇게 되면 또 2번으로 시작을 합니다.

      쉽게 하는 방법은 위 코드를 그대로 사용하고 1번을 삭제하고 번호만 새로 매기는 방법을 사용했습니다. ^^;;

      그리고 시트에 내가 원하는 부분만 하는 방법은 모두 고정이 되어 있는 상태라면 모르겠지만 시트마다 다르다면 매우 어려울 것 같습니다. ^^;;

  • 지니
    2017.02.15 17:20

    매번 감사합니다.
    실례가 안된다면 다음파일 검토 부탁드립니다.
    https://drive.google.com/open?id=0B9GK8bVyMdNnRG1FWUFjT29mLUE
    아빠달님이 알려주신 시트명 불러오기 기능을 사용해서 일자별 일부 데이터를 data시트로 가져오고자 합니다.

    • 2017.02.15 19:00 신고

      ^^ INDIRECT 함수를 사용하여 작업을 해뒀습니다.

      불러오기를 클릭하여 시트 이름을 가지고 오면 C12셀부터 H12셀까지 불럭을 씌운 다음 아래로 드래그 하면 자동으로 시트 이름을 찾아 다이렉트로 값을 넣어 줄 것입니다.

      업무에 조금이라도 도움이 되셨으면 좋겠네요^^

  • 지니
    2017.02.16 09:37

    정말 감사합니다~~~
    알려주신 INDIRECT로 비슷하게 성공했습니다 ^^
    이번주내내 여기저기 사이트 다 다녀봐도 모른다 안된다 그랬는데 아빠달님 덕분에 해결했습니다.
    보내주신 파일보니 제꺼보다 간단하게 잘하신게 이래서 전문가는 다르구나를 느낍니다~
    다시한번 감사드립니다.

    • 2017.02.16 15:09

      비밀댓글입니다

  • 효진
    2017.03.16 11:40

    이해하기 쉽게 올려주셔서 많은 도움이 됩니다.
    감사합니다.
    질문이 있습니다. 제 엑셀에는 메뉴탭에 보기 옆이 비어있습니다..
    개발도구 메뉴가 없어요... 이런 경우에는 어떻게 하나요?

    • 2017.03.16 20:17 신고

      개발도구는 파일-옵션-리본사용자지정에서 오른쪽 항목에 개발도구 항목에 체크를 하면 나타납니다. ^^

  • muey
    2017.05.24 11:29

    딱 제가 찾던 내용 올려주셨어요! 매크로로 시트이름 불러오는것도 참고했어요~ 감사합니다!

    • 2017.05.25 14:56 신고

      도움이 되셨다나 올린 보람을 느끼네요^^. 댓글도 달아주셔서 감사합니다. ^^

  • fsysy
    2017.07.18 10:40

    감사합니다 덕분에 시간을 많이 아꼈네요. 유용하게 쓰겠습니다.

  • 김채원
    2018.08.02 15:44

    정말 도움되었어요 ㅜㅜ 찾고 있었는데 쉽게 알게되서 감사합니다.
    위에 글에 적어두셨던데요.... 목록한걸 링크로 바로 갈수도 있는방법 .... ㅎㅎ
    혹 아직 업뎃 안되셨는지요..??
    저는 배워도 아직 갈길이 멀어... 염치없지만 부탁드려요 ~~~
    무더운날씨가 건강조심하시구요 ... 정말 잘쓰겠습니다 ^^

    • 2018.08.03 15:44 신고

      http://appadal.tistory.com/295
      ^^ 보시면 쉽게 보실 수 있으실 꺼예요~

  • 2018.08.30 10:27

    비밀댓글입니다

    • 2018.10.20 07:27 신고

      소스코드 다운로드 받을수 있도록 파일을 올려 놓았습니다.

  • Shirley
    2018.10.19 10:58

    딱 궁금하던게 바로 설명 해 주셔서 좋은거 같아요!
    1. 여기 블로그에서 코드 복사가 안되요 ㅠㅠ
    2. 그리고 혹시 저거 작업 해서 목록이 수정/생성 된 후에,
    그 목록 이름을 클릭하면 자동으로 그 시트로 넘어가는 방법은 없나요?
    댓글 젤 위에 잇는거 봣는데, 그건 시트 안에 있는 내용을 합치는 거고,
    저는 지금 페이지까지 다 하고 난 후에,
    시트페이지 목록에서 목록을 클릭 하면 해당 시트화면으로 넘어가는 방법은 없나궁금하네요!
    여기 페이지 너무 좋은거 같아서, 여기서 엑셀 공부 해야겠어요! ㅎㅎ
    너무 좋은 내용 감사합니다!

    • 2018.10.20 07:26 신고

      ^^
      https://appadal.tistory.com/295
      엑셀 하이퍼링크 함수 시트 이동하기편을 보시면 해결하실수 있으것 같아요^^
      많이 놀러오시고여 다른분들이게도 널리 알려주세요 ^^

  • Name
    2019.03.08 16:54

    A5에서부터 시작하며 넘버없이 시트1 제외하고 시트2 부터 입력할려면 어떻게 고쳐야되나요...엑셀 기본뿐이 몰라서 혼자서 이리고치고 저리고치고 해도 안나오네요

    • 2019.03.11 12:53 신고

      ix 값을 2로 변경한 다음 시작하시면 될 것 같습니다.

  • 궁금이
    2019.10.24 17:02

    컴파일 오류가 뜨네요. 뭐가 뭔지 모르겠어요. 저장도 안되고...ㅠ.ㅠ

Designed by JB FACTORY