모든 엑셀 파일에서 작동하는 나만의 매크로 만들기

매크로 혹은 VBA, 엑셀을 좀 쓴다 하는 분이라면 한 번쯤 만들어 보았거나 적어도 들어보셨을 겁니다. 그런데 공들여 만든 매크로가 그 파일을 벗어나면 사라지는 경험 때문에 매크로를 포기하는 분들이 많습니다. 원인은 간단합니다. 매크로를 그 파일 안에만 작동되도록 저장했기 때문입니다.

엑셀에는 이런 제약이 없는 특별한 파일, PERSONAL.XLSB(개인용 매크로 통합 문서)가 있습니다. 여기에 매크로를 저장해두면 컴퓨터에서 여는 모든 엑셀 파일에서 그대로 작동합니다. 오늘은 이 파일을 만드는 방법과, 실무 매크로·VBA 네 가지를 소개합니다.

이번 영상에서 습득할 수 있는 주요 내용

매크로가 모든 엑셀 파일에서 실행되게 하기

오늘 소개하는 매크로 코드는 AI에게 요청하면 어렵지 않게 만들 수 있는 수준입니다. 중요한 건 코드를 어디에 저장해야 모든 파일에서 작동하느냐입니다. 그 답이 바로 PERSONAL.XLSB(개인용 매크로 통합 문서)입니다. 엑셀을 켤 때마다 보이지 않게 함께 열리는 파일이죠. 이 파일에 저장한 매크로는 모든 엑셀 파일에서 실행됩니다.

워크시트 상태에서 Alt + F11을 눌러 VBA 편집기를 엽니다. 아직 PERSONAL.XLSB가 없다면 [보기] > [매크로] > [매크로 기록]에서 저장 위치를 [개인용 매크로 통합 문서]로 지정한 뒤 바로 [기록 중지]를 누르면 파일이 만들어집니다. 이후 프로젝트 탐색기에서 PERSONAL.XLSB를 우클릭 > [삽입] > [모듈]을 선택해 코드를 붙여넣으면 됩니다.

1. 값과 서식 한 번에 붙여넣기

수식이 들어있는 표를 그대로 다른 사람에게 전달하면, 원본 시트가 없는 환경에서는 숫자가 깨질 수 있습니다. 그렇다고 [값 붙여넣기]만 하면 서식이 날아가고, [서식 붙여넣기]만 하면 값이 사라집니다. 엑셀 기본 기능으로는 이 둘을 한 번에 처리할 방법이 없어 매번 붙여넣기를 두 번씩 해야 합니다.

이 매크로는 값과 서식을 한 번에 붙여넣어 줍니다. 다만 매크로 대화상자를 여는 순간 복사 상태가 해제되기 때문에, [매크로 보기] 대화상자로 실행하면 오류가 납니다. 그래서 이 매크로는 빠른 실행 도구 모음(QAT)에 등록해서 사용합니다. QAT에 등록하면 Alt 키를 눌렀을 때 나타나는 번호로 Alt + 숫자 단축키 실행도 가능합니다.

2. 완전히 비어 있는 행만 삭제

중간에 빈 줄이 들어간 표는 정렬·필터·피벗 테이블에서 문제가 생깁니다. [이동 옵션] > [빈 셀]로 지우면 어떻게 될까요? 일부만 비어있는 칸이 하나라도 있는 행까지 통째로 지워지는 문제가 발생합니다.

이 매크로는 COUNTA로 한 행 전체의 값 개수를 세어, 결과가 0인 행, 즉 모든 칸이 완전히 빈 행만 골라서 삭제합니다. 행을 지우는 코드는 아래에서 위로 올라오면서 진행해야 밀림 현상 없이 안전하게 처리된다는 원리도 함께 다룹니다. 매크로로 삭제한 결과는 Ctrl + Z로 되돌릴 수 없으니, 중요한 파일은 실행 전 반드시 사본을 만들어야 합니다. 아무리 간단한 매크로도 '되돌리기' 할 수 없으므로 주의가 필요합니다.

3. 클릭 한 번으로 이동하는 '목차 시트' 만들기

시트가 수십 개인 파일에서 원하는 시트를 찾으려면 시트 탭을 계속 눌러 이동해야 합니다. 이 매크로는 통합 문서 안의 모든 시트로 이동하는 하이퍼링크 목차를 자동으로 만들어 줍니다. 기존 목차가 있으면 먼저 지우고 새로 만드는 구조라서, 시트를 추가·삭제·이름 변경한 뒤에도 다시 실행하기만 하면 항상 최신 상태로 갱신됩니다.

영상 앞부분에서 봤던 정산 금액 오류의 원인은 병합된 셀 때문이었습니다. 이 문제를 해결하는 매크로는 '풀 버전' 영상에서 코드와 함께 자세히 다룹니다.

4. 〔풀버전 전용〕병합된 셀을 한 번에 풀고 빈칸까지 채우기

병합된 셀은 겉보기에 여러 칸처럼 보이지만 실제 값은 맨 위 한 칸에만 들어있고 나머지는 비어 있습니다. 그래서 병합된 표를 그대로 집계하면 수식은 멀쩡해도 결과가 틀어질 수 있습니다. 손으로 처리하려면 병합 해제 후 [이동 옵션] > [빈 셀]로 위쪽 값을 채우고, 다시 값으로 복사해야 하는 번거로운 과정을 거쳐야 합니다.

이 매크로 하나면 이 과정이 한 번에 끝납니다. 병합된 셀을 만나면 MergeArea로 병합 덩어리 전체를 붙잡고, 맨 위 값을 기억해두었다가 병합을 해제한 뒤 그 영역 전체에 다시 채워 넣는 방식입니다.

멤버십 회원 전용 강의 (풀 버전)

PERSONAL.XLSB 만들기, 값·서식 붙여넣기, 빈 행 삭제, 시트 목차까지는 누구나 바로 따라 할 수 있지만, "병합된 셀 때문에 집계가 틀어지는" 문제는 원리를 이해해야 내 파일에 맞게 코드를 고쳐 쓸 수 있습니다.

풀 버전 강의에서는 병합된 셀을 한 번에 해제하고 빈칸까지 자동으로 채우는 매크로를 코드와 함께 자세히 다룹니다.

MVP TIP — 나만의 매크로 200% 활용 전략

1. PERSONAL.XLSB는 저장을 꼭 확인하세요

엑셀을 닫을 때 "개인용 매크로 통합 문서의 변경 내용을 저장하시겠습니까?" 창이 뜨면 반드시 [저장]을 눌러야 합니다. [저장 안 함]을 누르면 새로 만든 매크로가 모두 사라집니다.

2. 매크로 이름에는 공백을 쓸 수 없습니다

Sub 다음에 오는 이름에는 띄어쓰기를 넣을 수 없습니다. 띄어 쓰고 싶다면 언더바(_)로 연결하세요. 또한 숫자가 맨 앞에 오면 안 되고, 한글보다는 영어를 쓰는 것을 추천합니다. 영어 대소문자를 사용하면 가독성이 높아집니다(예: MyMacro_1, FillEmptyCells 등).

3. 행을 지우는 코드는 항상 아래에서 위로

행이나 열을 삭제하는 매크로는 마지막 행에서 첫 행 쪽으로 거꾸로 반복해야, 삭제 후 밀림 현상으로 빈 줄을 건너뛰는 문제를 막을 수 있습니다.

자주 묻는 질문 (FAQ)

마치며

PERSONAL.XLSB는 한 번만 만들어두면 컴퓨터를 바꾸기 전까지 계속 따라다니는, 매크로 활용의 첫 단추입니다. 값·서식 한 번에 붙여넣기, 완전히 빈 행만 삭제하기, 클릭 한 번으로 이동하는 시트 목차처럼 여러 통합 문서에서 자주 사용하는 매크로나 VBA 코드를 이 파일에 만들어 두면 업무 효율이 크게 향상됩니다.

여기에 병합된 셀 한 번에 해제하고 채우기까지 더하면, 받은 파일을 분석 가능한 데이터로 바꾸는 시간도 크게 줄여줍니다. 지금 바로 예제 파일을 열어, PERSONAL.XLSB부터 만들어 보는 것으로 시작해 보세요.