요약 Sheet 만들고 자동 링크 걸기 (엑셀VBA) : 네이버 블로그

VBA 자동화

요약 Sheet 만들고 자동 링크 걸기 (엑셀VBA)

잔마왕

2020. 1. 30. 12:20

이웃추가

엑셀로 작업을 하다 보면 반복되는 패턴의 Sheet에서 key에 해당하는 내용만 발췌하여 첫페이지에 요약 페이지를 만들고 Link를 연결하는 작업을 할 때가 있습니다.

예를 들어, 다음의 예시 파일과 같이 동일 Format의 PJT명을 가진 sheet가 여러장 있고

그 첫장에 Summary sheet로 다음과 같이 정리하고 hyperlink를 거는 작업을 자동으로 하는 매크로를 만들어 보겠습니다.

먼저 요약 Sheet를 만드는 부분은 비교적 쉽게 만들수 있습니다.

2번째 Sheet 부터 마지막 Sheet까지 숫자를 증가시켜가면서, 요약에 필요한 칸의 위치만 정확히 연결시켜주는 코드를 작성해 주면 됩니다.

예시에서 사용된 Format의 경우 다음과 같이 해당 칸의 위치가 정의되는것을 알 수 있습니다.

PJT 명 : C2

시작일 : C3

종료일 : E3

리더 : H2

비용 : H3

이를 소스로 풀어쓰면 다음과 같습니다.

Sub Summarize() Dim i As Integer Dim TarSh As Variant For i = 2 To Sheets.Count Set TarSh = Sheets(i) Sheet1.Range("B" & i + 1).Value = TarSh.Range("C2").Value Sheet1.Range("C" & i + 1).Value = TarSh.Range("C3").Value Sheet1.Range("D" & i + 1).Value = TarSh.Range("E3").Value Sheet1.Range("E" & i + 1).Value = TarSh.Range("H2").Value Sheet1.Range("F" & i + 1).Value = TarSh.Range("H3").Value Next i End Sub

이제 PJT명에 Hyper Link를 걸고 싶은데, 소스 코드 작성 요령은 잘 모르겠지만 엑셀에서 링크를 거는 방법은 알고 있습니다. 이럴 때는 매크로 기록 기능을 적극 활용하는것도 좋은 방법입니다.

개발도구 → 매크로 기록 → 확인 버튼을 누르고 기록을 시작합니다.

하이퍼 링크를 걸 칸(PJT명 열)에서 마우스 우클릭 → 링크를 선택합니다.

현재문서 → Sheet → 참조할 셀을 선택한 다음 "확인" 버튼을 눌러 하이퍼 링크를 삽입합니다.

여기까지 한다음 개발도구 → 기록중지를 누릅니다.

이제 VBA 화면에 들어가서 보면 새로운 모듈에 방금 기록한 매크로가 저장된것을 볼 수 있습니다.

Sub 매크로1() ' ' 매크로1 매크로 ' ' ActiveSheet.Hyperlinks.Add Anchor:=Selection, Address:="", SubAddress:= _ "YT!A1", TextToDisplay:="업무의 잔머리 (Youtube & Blog)" End Sub

여기서 Hyper Link를 거는데 필요한 명령어와 설정해야하는 변수정보를 유추할수 있으니 이를 응용하도록 하겠습니다.

"ActiveSheet" → 내가 값을 넣을 Sheet를 선택하면 되니 "sheet1"로 변경하면 되겠습니다.

"Anchor:=" → 하이퍼 링크를 걸 Range를 선택하면 되니 PJT명이 씌여진 Range로 연결 하겠습니다.

"Address:="" " → 외부 파일에 연결시 필요한 값으로 지금은 내부 링크를 사용하니 공란으로 유지하겠습니다.

"SubAddress:=" → 링크를 걸 Sheet의 이름과 셀의 위치이니 변수 입력하여 연결하면 되겠습니다.

그런데, Sheet이름에 공란이 있을 경우 Sheet이름은 "'"로 감싸야 정상동작하므로,

Sheet 이름은 기본적으로 "'"로 감싸지게 만들겠습니다.

"TextToDisplay:=" → 하이퍼 링크가 걸린칸에 표시되는 글자 내용입니다.

이와 같은 하이퍼 링크 명령어를 보면 하이퍼 링크를 걸때 해당 칸의 정보까지 바꾸므로 PJT명 칸에 PJT명 이름을 쓰는 소스코드를 바꿔서 다음과 같이 소스를 변경해 주면 되겠습니다.

Sub Summarize() Dim i As Integer Dim TarSh As Variant For i = 2 To Sheets.Count Set TarSh = Sheets(i) Sheet1.Hyperlinks.Add Anchor:=Sheet1.Range("B" & i + 1), Address:="", SubAddress:= _ "'" & TarSh.Name & "'" & "!A1", TextToDisplay:=TarSh.Range("C2").Value Sheet1.Range("C" & i + 1).Value = TarSh.Range("C3").Value Sheet1.Range("D" & i + 1).Value = TarSh.Range("E3").Value Sheet1.Range("E" & i + 1).Value = TarSh.Range("H2").Value Sheet1.Range("F" & i + 1).Value = TarSh.Range("H3").Value Next i End Sub

같은 요령으로 각 Sheet의 가장 첫번째칸 (A1)에 요약 Sheet로 되돌아가는 하이퍼링크를 만들면 다음과 같이 소스코드가 완성됩니다.

Sub Summarize() Dim i As Integer Dim TarSh As Variant '2번째 Sheet 부터 마지막 Sheet까지 반복 For i = 2 To Sheets.Count '현재 정보를 넣는 Target Sheet 선언 Set TarSh = Sheets(i) 'Summary Sheet에 PJT명 정보를 입력하면서 Hyper Link 설정 Sheet1.Hyperlinks.Add Anchor:=Sheet1.Range("B" & i + 1), Address:="", SubAddress:= _ "'" & TarSh.Name & "'" & "!A1", TextToDisplay:=TarSh.Range("C2").Value 'Summary Sheet 각 칸에 필요한 정보 가져오기 Sheet1.Range("C" & i + 1).Value = TarSh.Range("C3").Value Sheet1.Range("D" & i + 1).Value = TarSh.Range("E3").Value Sheet1.Range("E" & i + 1).Value = TarSh.Range("H2").Value Sheet1.Range("F" & i + 1).Value = TarSh.Range("H3").Value 'Target Sheet의 첫칸(A1)에 요약 Sheet로 돌아가는 Hyper Link 설정 TarSh.Hyperlinks.Add Anchor:=TarSh.Range("A1"), Address:="", SubAddress:= _ Sheet1.Name & "!A1", TextToDisplay:="Summary" Next i End Sub

마지막으로 개발도구 → 삽입 → 버튼을 추가한다음 만들어진 매크로와 연결해 주면 실행 버튼을 만들고 마무리가 되겠습니다.