Excel [지식인 해결 시리즈] 005. 다른 시트에 있는 특정 데이터를 복사해오기

전체 목차

001. lookup 함수를 이용한 양도세 구간 구하기

002. 공백 셀 제거 후 정렬

003.데이터 분석 후 행 추가하기 (나누기, 나머지, 정수)

004. 목록상자, 토글버튼 연동

005. 다른 시트에 있는 특정 데이터를 복사해오기

006. 근무시간에서 특정시간 구하기 (야간 근무 인정 시간)004. 목록상자, 토글버튼 연동

 

 

 

포스트 목차.

01. 문제

02. 문제해결 방법

03. 예제 및 설명

 

01. 문제

 

다음과 같은 두 개의 시트가 있다. 

1) DB 시트

DB시트에는 거래처4곳에 대한 날짜/단가/수량/총액에 대한 데이터가 들어있다.  

 

2) 종합시트

종합시트의 B3 셀에 거래처 이름을 입력하고 버튼을 누르면 DB시트에 있는 해당 거래처 데이터가 B7부터 복사되야 된다.

 

문제를 정리하면 다음과 같다.

 1) 시트 "DB"에  여러 거래처의 거래일/단가/수량/총액 정보가 들어있음

 2) 시트 "DB"에 있는 정보들은 거래처를 기준으로 뭉쳐있으며 거래처마다 한열의 간격을 두고 채워져 있음

 3) 종합시트에서 거래처명을 입력한 후 버튼을 누르면 밑에 시트"DB"에 있는 해당 거래처 내용들이 복사되야 함

 

02. 문제해결 방법

 

이전 포스트에서 콤보상자 컨트롤에 대해 다룬적이 있으므로, 문제를 조금만 더 복잡하게 만들어보자.

- 종합시트에서 거래처명을 입력하는 대신 시트"DB"에 있는 거래처를 조회해서, 콤보상자 컨트롤에 넣어준다.

- 초기화 버튼을 만들고 해당 버튼을 누르면 콤보상자 컨트롤의 내용을 채우는 역할을 하게 한다.

- 사용자가 콤보상자 컨트롤에 입력되어 있는 거래처명을 선택한 후, 버튼을 클릭하면 해당 거래처 내용들이 복사되게 한다.

변경된 문제에 맞게 변경한 종합시트

 

 

 

문제를 다음과 같이 쪼개 보자.

 

 - 시트"DB"에 있는 거래처명을 가져오기

 - 거래처명을 이용하여 시트"DB"에서 복사할 영역 알아내기

 - 종합시트에 복사한 내용을 붙여넣기

 

1) 시트"DB"에 있는 거래처명을 가져오기

다음과 같은 컨트롤 3개를 종합시트에 추가한다.

초기화 버튼을 누르면, 거래처명을 조회하여, 콤보박스(거래처드롭다운)에 넣어준다.

 

거래처명을 구하기 위해서 시트"DB"의 구조를 다시 한번 살펴보자.

 

6행에 있는 숫자는 이해를 위해 넣은 숫자이다.  거래처별로 저장되어 있는 데이터의 종류는 4종이며, 거래처마다 한 칸을 띄기 때문에 거래처 정보들이 있는 열의 인덱스는 2, 7, 12, 17로 볼 수 있다. 허나 거래처가 나중에 4개를 초과하여 확장될 수 있으므로 거래처의 수를 구하는 것부터 시작하자.

 

데이터가 있는 열의 최대 값을 구하는 것은 이전 포스트에서 다룬 적이 있다. 

https://eggdrop.tistory.com/9

Excel 매크로 (VBA) - 004. 데이터가 있는 셀의 범위 알아내기

 

StartRowNum을 7이라고 하면(위에서 데이터의 머리글행이 7행부터 시작하므로) 다음과 같이 구할 수 있다.

Col = Worksheets("DB").Cells(StartRowNum, Columns.Count).End(xlToLeft).Column

 

2에서 시작하여, 위에서 구한 최대열의 수(Col)까지, 5(아이템수4+공백1)씩 증가하는 루프를 만들면 거래처가 존재하는 모들 셀의 열인덱스를 커버하는 루프를 만들 수 있다. 

For i=2 to Col Step 5

거래처가 있는 행은 7행으로 고정되어 있으므로 Cells(7,i).Value로 거래처들을 순회할 수 있을 것이다.

 

2) 거래처명을 이용하여 시트"DB"에서 복사할 영역 알아내기

사용자가 거래처명을 콤보박스에서 선택하면, 콤보박스 내의 인덱스(1부터 시작)를 구할 수 있다. 이 인덱스 자체가 몇번째 거래처인지를 나타내므로 복사할 영역은 다음과 같이 구할 수 있다.

 

거래처셀: 거래처셀의 행 주소는 7로 고정이다. 거래처셀의 열주소는 2 + (인덱스 - 1)*5 로 구할 수 있다. 

마지막행 데이터의 최우측셀: 마지막행 데이터의 최우측셀의 열주소는 거래처셀에서 +3(아이템수 -1)을 더하면 된다. 마지막 행 데이터의 최우측셀의 행주소는 다음과 같이 구할 수 있다.(위에서 소개한 데이터 범위 구하는 포스트를 참고하자)

myStartCol을 거래처를 나타내는 열이라고 하자.(거래처열의 마지막 행번호 = 마지막 행 데이터의 최우측셀의 행번호)

myEndRow = Worksheets("DB").Cells(Rows.Count, myStartCol).End(xlUp).Row

 

다음 그림은 두번째 거래처를 사용자가 선택했을 때의 상황이다. 

복사는 Range.Copy를 이용하면된다.

 

3) 종합시트에 복사한 내용을 붙여넣기

붙여넣기는 Range.PasteSpecial을 이용해보자.

https://eggdrop.tistory.com/6

엑셀 매크로 (VBA) - 002. 선택하여 붙여 넣기 기능 정리

 

03. 예제 및 설명

 

위의 풀이 방법에 맞게 작성한 코드는 다음과 같다.

 

'거래처에서 다루는 아이템의 종류
Dim NumOfItems As Integer
'시작 행 번호
Dim StartRowNum As Integer
'시작 열 번호
Dim StartColNum As Integer
 
 
 
Sub 실행버튼_Click()
    
    '날짜,단가,수량,총액 4개의 데이터로 구성
    NumOfItems = 4
    '7행에 데이터 시작(행이름 포함)
    StartRowNum = 7
    'B열부터 데이터 존재
    StartColNum = 2
    
    '기존 데이터 지우기
    Worksheets("종합시트").Select
    Worksheets("종합시트").Range(Cells(StartRowNum, StartColNum), Cells(Rows.Count, StartColNum + NumOfItems - 1)).Select
    Selection.ClearContents
        
    '선택한 거래처 번호
    selectedValue = Worksheets("종합시트").Shapes("거래처드롭다운").ControlFormat.Value
         
    '거래처 이름이 들어가 있는 열번호
    myStartCol = StartColNum + (selectedValue - 1) * (NumOfItems + 1)
    '마지막 데이터가 있는 행번호
    myEndRow = Worksheets("DB").Cells(Rows.Count, myStartCol).End(xlUp).Row
    '데이터가 없는경우 아무것도 하지 않는다.
    If myEndRow > StartRowNum Then
        'DB 시트 선택
        Worksheets("DB").Select
        '해당거래처가 있는 영역 선택
        Worksheets("DB").Range(Cells(StartRowNum, myStartCol), Cells(myEndRow, myStartCol + NumOfItems - 1)).Copy
        '종합시트 선택
        Worksheets("종합시트").Select
        '종합시트에서 복사할 첫 셀 선택
        Worksheets("종합시트").Cells(StartRowNum, StartColNum).Select
        '복사하기
        Selection.PasteSpecial
    End If
 
    
End Sub
 
'거래처 목록을 가져온다
Sub 초기화버튼_Click()
        
    '날짜,단가,수량,총액 4개의 데이터로 구성
    NumOfItems = 4
    '7행에 데이터 시작(행이름 포함)
    StartRowNum = 7
    'B열부터 데이터 존재
    StartColNum = 2
    
    '가장 오른쪽에 있는 열의 수 구하기
    Col = Worksheets("DB").Cells(StartRowNum, Columns.Count).End(xlToLeft).Column
    '종합시트에 있는 거래처드롭다운 컨트롤의 모든 아이템 제거
    Worksheets("종합시트").Shapes("거래처드롭다운").ControlFormat.RemoveAllItems
    
    '아이템 수보다 1만은 크기로 가장 오른쪽 열까지 탐색하며 거래처 이름 구하기
    For I = StartColNum To Col Step (NumOfItems + 1)
        '거래처 이름 저장
        tmpValue = Worksheets("DB").Cells(StartRowNum, I)
        '거래처드롭다운에 거래처 이름 추가
        Worksheets("종합시트").Shapes("거래처드롭다운").ControlFormat.AddItem tmpValue
    Next I
    
End Sub
 

 

실행 결과 (02거래처 선택 시)

 

실행 결과 (04거래처 선택 시)