A 열에 값 목록이 있고 각 셀에 다른 하이퍼 링크가 포함되어 있다고 가정하면 다음 스크린 샷과 같이 텍스트가없는 하이퍼 링크 만 다른 열 E에 복사하려고합니다. Excel에서이 작업을 직접 해결하는 방법은 없을 수 있지만 여기서는이를 처리하기위한 VBA 코드를 소개 할 수 있습니다. VBA 코드를 사용하여 한 셀에서 다른 셀로 하이퍼 링크 복사
|
엑셀 링크 걸린 셀 다른시트로 복사하기 셀주소 안변하게
엑셀은 수식을 복사할 수 있는 기능이 있어서 작업을 정말 빠르고 정확하게 할 수가 있죠 하지만 이런 수식 복사 기능이 때론 불편할때가 있습니다. 물론 일반적인 엑셀 작업을 할 때는 이런 일이 없죠! 그런데 수식이 바뀌지 않게 복사해야 하는 경우가 있습니다. 물론 절대주소로 바꾸면 수식이 바뀌지 않지만, 절대주소로 수식작성을 하면 수식복사기능을 사용하지 못하고 일일이 모두 수식 입력을 해야 합니다.
하이퍼링크 셀 다른 시트로 복사할때 셀주소가 바뀌어 링크가 바뀌는 문제에 봉착해서 이 문제를 어떻게 해결해야 할까 많이 고민했습니다. 이걸 하나 하나 절대주소로 수식을 입력(수정)해야 하나 다른 방법은 없을까? 물론 데이터의 양이 적다면 이런 고민은 할 필요가 없습니다. 그냥 절대주소로 작성하면 간단하니까요 하지만 데이터 양이 많아서 해결을 해야 했습니다. 문제 해결만 하는것이라면 다른 방법으로도 가능했는데 이 기회에 이걸 해결하는 방법을 캐봐야 겠다라는 생각으로 검색도 해보고 고민을 하던 중에 조금 복잡하지만 해결할 수 있었습니다. 제가 아는 방법으로는 엑셀에서 간단하게 해결하는 방법은 없는것으로 알고 있습니다. 간단하게 샘플을 만들어봤습니다. 링크가 걸린 d열의 값은 =HYPERLINK(주소!C4,주소!B4) 와 같습니다. 수식을 상대주소로 하여 작성 후 수식복사로 한방에 잘 해결했죠!
위의 사각형 부분의 셀을 다른시트나 다른 셀로 복사하면 아래와 같이 #ref 오류가 발생하거나 링크값이 바뀌게 됩니다.
이걸 해결하기 위해서 다음과 같은 방법으로 해결을 했습니다. 1. 수식 입력시 셀주소 앞에 시트이름을 넣는다. 2. 수식의 상대주소를 절대주소로 일괄변경하면 끝!
첫번째. 수식 입력시 셀주소앞에 시트이름 넣기 셀주소앞에 시트이름을 넣는것은 다른시트로 복사시 정확성을 높히기 위한 방법입니다. =hyperlink(c4,b4) (x) 위와 같이 하지 말고 아래와 같이 =hyperlink(시트명!c4,시트명!b4) (O) 시트명이 "사이트주소"라면 아래와 같이 입력해줍니다. =hyperlink(사이트주소!c4,사이트주소!b4) 두번째. 상대주소를 절대주소 일괄 변경하기 정말 다른 방법이 없었습니다. 절대주소로 변경하는 방법밖에는 문제는 많은양의 식을 절대주소로 일일이 수정하는것이 문제였습니다. 그러다 알게 된게 VBA로 해결하는 방법이었습니다. VBA로 한방에 해결하는건데 처음 한번은 VBA 작성을 해야 합니다. 뭐.. 그래도 작업해야 할 데이터 양이 많다면 이건 일도 아니죠! ^^ VBA 작성을 위해서 Alt + F11을 누릅니다. 그럼 아래와 같은 화면이 열립니다. "삽입-모듈" 을 차례로 누릅니다.
그러면 vba 프로그램을 입력할 수 있는 창이 열립니다.
Sub Realtive2Absolute() For Each cc In Selection If cc.HasFormula = True Then cc.Formula = Application.ConvertFormula(cc.Formula, xlA1, xlA1, xlAbsolute) End If Next cc End Sub 아래와 같이 입력하면 됩니다. 매크로나 프로그래밍을 안해본 분은 조금 생소할 수 있는데 오타만 안나게 그대로 입력하면 됩니다. 다 입력한 다음 창을 닫고 나갑니다.
상대주소를 절대주소로 한번에 바꾸기 이제 수식에 있는 상대주소를 절대주소로 변경할 범위를 지정를 지정합니다.
상대주소 아래와 같이 절대주소로 변경할 범위 지정을 합니다.
보기메뉴에서 매크로-매크로보기를 클릭합니다.
아래과 같이 매크로이름에 위에서 입력한 매크로가 나타납니다. 실행을 누릅니다.
짠 ~ 그러면 한번에 수식에 있는 상대주소가 절대주소로 바뀝니다.
전체 확인해볼까요? 수식보기(Ctrl + 1)를 눌러보면 모두 절대주소로 바뀌어 있는것을 확인할 수 있습니다.
모두절대주소로 바뀜 그리고 작성한 절대주소로 치환하는 매크로를 나중에 다시 사용하려면 파일 저장시 파일형식을 매크로 사용 통합문서로 저장하면 됩니다. |