[Excel] VBA를 사용하여 현재 시트의 특정 값을 가진 자료를 다른 시트로 옮기기

회사에서 엑셀을 만지다 문득 현재 시트의 자료 중 특정 자료들을 다른 시트로 자동으로 옮겨주는 기능이 있으면 좋겠다 생각을 하여 퇴근하고 한 번 끄적여 보았습니다.

 

1. 테스트 데이터 생성

테스트 데이터는 2023년 4월 행정안전부 주민등록 인구통계 자료의 일부를 복사하여 생성하였습니다.

 

2. 목표 설정

목표는 서울특별시 시트의 자료 중 종로구 자료는 종로구 시트에, 중구 자료는 중구 시트에, 용산구 자료는 용산구 시트로 옮기는 것입니다.

 

3. 매크로 생성

3-1. 현재 시트의 자료를 다른 시트로 복사하기

Sub MOVE()
    'ActiveSheet : 현재시트
    'Range       : 범위지정
    'Copy        : 복사
    ActiveSheet.Range("A2:D49").Copy

    'Sheets : 특정 시트
    'Select : 선택
    Sheets("종로구").Select

    'Paste       : 붙여넣기
    ActiveSheet.Paste
End Sub

먼저 현재 시트의 자료를 다른 시트로 복사하는 매크로를 만들어보았습니다.

 

서울특별시 시트의 자료를 종로구 시트로 옮기는 것은 성공했으나 이상한 위치로 옮겨졌습니다.

원인을 확인해 보니 매크로 수행 전 종로구 시트의 H10 셀을 선택하고 있었습니다.

 

3-2. 현재 시트의 자료를 다른 시트의 원하는 자리로 복사하기

Sub MOVE()
    ActiveSheet.Range("A2:D49").Copy

    Sheets("종로구").Select

    '붙여넣기 전 붙여넣을 자리 선택
    ActiveSheet.Range("A2").Select

    ActiveSheet.Paste
End Sub

기존에 선택하고 있던 셀에 복사한 자료를 붙여 넣는 것을 보고 붙여 넣기를 수행하기 전 붙여 넣을 셀을 선택하는 코드를 추가하였습니다.

 

복사한 데이터를 원하는 자리에 잘 붙여넣었습니다.

 

3-3. 메인 시트의 특정 값을 가진 행을 값과 동일한 이름을 가진 시트의 원하는 자리로 복사하기

Sub MOVE()
    Dim main As Worksheet
    Dim rowCnt As Integer, shtCnt As Integer
    Dim i As Integer, j As Integer

    Set main = Sheets("서울특별시")
    rowCnt = main.Cells(rows.Count, "A").End(xlUp).Row  '서울특별시 시트의 row수
    shtCnt = Worksheets.Count                           '총 시트수

    For i = 2 To rowCnt                                 '1번 row는 헤더니 2번 row부터 메인 시트의 row수까지
        For j = 2 To shtCnt                             '1번 sheet는 메인 시트니 2번 sheet부터 총 시트수까지
           If main.Cells(i, "B") = Sheets(j).Name Then  'Bi 셀의 값이 j번 시트의 이름과 같으면
                main.rows(i).Copy                       '메인시트의 i번 행을 복사
                Sheets(j).Select                        'j번 시트를 선택하고
                Sheets(j).Range("A2").Select            '원하는 위치를 선택하고
                Sheets(j).Paste                         '붙여넣는다
            End If
        Next
    Next
End Sub

어... 갑자기 조금 어려워졌죠? 하지만 새로 추가된 개념은 별로 없습니다.

B열의 값이 특정 시트의 이름과 같은 행을 찾아 복사하여 해당 시트에 붙여 넣는 코드입니다.

사실 중간에 특정 값과 시트의 이름을 상수로 박아 넣고 찾아 나가는 단계가 들어가야 하는데... 작성하다 보니 바로 작성해 버렸습니다.

 

위 매크로의 수행결과입니다.

특정 값을 가진 행을 해당 값과 동일한 이름의 시트의 원하는 위치에 넣는 것까진 성공하였으나 동일한 위치에 덮어씌워 최종적으로 마지막에 붙여 넣은 값만 남게 되었습니다.

 

3-4. 메인 시트의 특정 값을 가진 행을 값과 동일한 이름을 가진 시트의 원하는 자리에 줄을 바꿔가며 복사하기

Sub MOVE()
    Dim main As Worksheet
    Dim rowCnt As Integer, shtCnt As Integer
    Dim i As Integer, j As Integer

    Set main = Sheets("서울특별시")
    rowCnt = main.Cells(rows.Count, "A").End(xlUp).Row
    shtCnt = Worksheets.Count

    For i = 2 To rowCnt
        For j = 2 To shtCnt
           If main.Cells(i, "B") = Sheets(j).Name Then
                main.rows(i).Copy Sheets(j).Cells(rows.Count, "A").End(xlUp).Offset(1)
            End If
        Next
    Next
End Sub

If문 안에 있던 코드가 단순해졌습니다. Copy 명령어에 대해 알아보니 복사한 자료를 붙여 넣을 목적지를 설정하는 옵션이 있더라고요.

여기에 메인 시트의 rowCount를 셀 때 사용했던 로직을 적용한 후 Offset(1)을 이용하여 한 칸 아래로 내린 위치를 목적지로 넣어주니 원하는 기능도 적용하면서 코드가 많이 단순해졌습니다.

 

위 매크로 수행결과입니다. 원하는 대로 깔끔하게 데이터가 복사되었습니다.

 

4. 마무리

Sub MOVE()
    Dim main As Worksheet
    Dim rowCnt As Integer, shtCnt As Integer
    Dim i As Integer, j As Integer

    Set main = Sheets("서울특별시")
    rowCnt = main.Cells(rows.Count, "A").End(xlUp).Row
    shtCnt = Worksheets.Count

    For i = 2 To rowCnt
        For j = 2 To shtCnt
           If main.Cells(i, "B") = Sheets(j).Name Then
                main.rows(i).Cut Sheets(j).Cells(rows.Count, "A").End(xlUp).Offset(1)
            End If
        Next
    Next
End Sub

최초 목표는 메인 시트의 데이터를 서브 시트로 복사하는 것이 아닌 옮기는 것이었기에 Copy를 Cut으로 변경하였습니다.