데이터 변경되면 자동으로 실행하는 엑셀 매크로

https://youtu.be/kU0_jKsRXZg

엑셀 매크로를 어느 정도 만들 수 있게 되면, 데이터 변경될 때마다 별도의 단축키나 호출을 통해서 매크로를 실행하는 것도 불편해 지는 단계가 되는데 이때 사용할 방법을 알려 드립니다.

우선 특정 시트의 내용이 변경될 때 진행할지, 모든 시트의 내용이 변경될 때 진행할지에 따라 시작하는 위치가 달라집니다. 오늘은 특정 시트의 내용이 변경될 때 특정 매크로를 실행하도록 하겠습니다. 

우선 오늘의 시나리오는 다음과 같습니다. Sheet1에 특정 학생들의 과목별 점수가 학생이름(무작위 순서)단위로 입력되고, 그 중에서 주요과목인 국어, 영어, 수학의 점수만 Sheet2에 복사해서 붙여 넣고, 해당 학생들의 이름 순서대로 정렬을 한 후에 정렬된 내용을 가로 세로를 바꿔서 보여주는게 최종 목표라고 가정하겠습니다.

 표로 보여드리면 다음과 같습니다. 아래는 최초 입력된 내용입니다.

최초 입력딘 내용

그런데 인원별 성적 입력순서가 매번 무작위로 입력된다는 가정입니다. 그래서 해당 입력에 대해 Sheet2에 주요 과목만 내용을 복사해 와야 합니다. 순서가 바뀌더라도 매번 동일한 값을 가져오도록 vlookup함수를 써서 가져오겠습니다.

B2셀에 다음과 같이 입력해 줍니다.

=VLOOKUP($A2,Sheet1!$A$2:$G$5,2,FALSE)

B3=VLOOKUP($A3,Sheet1!$A$2:$G$5,2,FALSE)

B4=VLOOKUP($A4,Sheet1!$A$2:$G$5,2,FALSE)

그리고 나서 B3부터 B5까지도 주욱 끌어주면 각각 항목이 증가되면서 복사될것입니다.

B3셀에는 가져오는 데이터 열이 변경됩니다.

C2 =VLOOKUP($A2,Sheet1!$A$2:$G$5,5,FALSE)

D2 =VLOOKUP($A2,Sheet1!$A$2:$G$5,6,FALSE) 

나머지 셀은 B2,B3,B4 에서 주욱 늘리면 채워집니다. 그 결과는 다음과 같습니다.

주요 과목만 복사

이제부터는 매크로 하나를 제작해야 합니다. 매크로 기록을 누르고 시작합니다.

먼저 해당 내용을 값만 복사해 줍니다. 위치는 바로 아래에 잡았습니다. 그리고 그것을 이름순으로 정렬합니다. 그리고 나서 다시 가로 세로를 변경해서 복사/붙여넣기 합니다. 해당 매크로는 다음과 같습니다. 

Sub 매크로2()
    Sheets(2).Activate
    Range("A1.D5").Select
    Selection.Copy
    Range("A7").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
        
    ActiveWorkbook.Worksheets("Sheet2").Sort.SortFields.Add Key:=Range("A8:A11") _
        , SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
        
    With ActiveWorkbook.Worksheets("Sheet2").Sort
        .SetRange Range("A7:D11")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
    
    Application.CutCopyMode = False
    Selection.Copy
    Range("A13").Select
    Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True
    Application.CutCopyMode = False
End Sub

 

이제 Sheet1의 데이터가 변경될 때마다 매크로를 실행할 차례입니다. VBA Editor에서(Alt+F11) 해당 시트명(Sheet1)을 클릭해 주고 아래와 같이 입력해 줍니다.

 

데이터 변경 범위를 "A1:D5"로 잡았는데 해당 범위의 셀이 변경되면 매크로2를 호출하는 식으로 되어 있습니다. 물론 매크로2의 내용을 그대로 붙여 넣어도 되지만 단순히 호출만 하는게 편리합니다. 실제로 Sheet1에서 데이터를 변경해 보면 바로 매크로가 실행되는 것을 볼 수 있습니다. 한가지 주의할 사항은 기존에 만든 매크로는 Sheet2에서 만들었으므로 Sheet2가 activate된 상태에서 실행된 경우라서 첫줄에 Sheets(2).Activate라는 내용을 추가해 주셔야 Sheet1에서 위 내용을 추가했을때 정상적으로 실행됩니다. 그렇지 않을 경우 Sheet1기준으로 실행이 되므로 주의하시기 바랍니다.

매크로의 나머지 내용은 자동으로 만든 내용 그대로입니다.

아래는 최종적인 엑셀 매크로 파일입니다.

데이터변경시자동실행매크로수정.xlsm

0.02MB

매번 데이터를 취합해서 특정 업무를 하시는 분들께 도움이 되길 바랍니다.