거래내역을 DB 형식으로 저장하기

안녕하세요.

거래명세서를 작성하고 발송한 뒤 거래내역을 '거래내역' 시트에 저장하겠습니다.

이 작업을 수작업으로 하려면 두 시트를 열어 하나씩 복사해야 하고, 더구나 수식으로 작성된 항목은 [선택하여 붙여넣기 - 값]으로 붙여넣는 작업을 해야 합니다.

VBA로 '거래명세서' 시트의 거래내역을 복사해 '거래내역' 시트에 붙여 넣도록 작성해 보겠습니다.

 

실습 파일 다운로드

거래내역을 DB형식으로 저장_실습.xlsm

0.06MB

완성 파일 다운로드

거래내역을 DB형식으로 저장_완성.xlsm

0.07MB

 

먼저 거래명세서를 확인해 보겠습니다.

저장될 거래내역도 확인해 보죠.

거래내역 시트에 저장될 목록은 거래일자, 업체명, 도서명, 가격, 할인금액, 수량, 판매금액입니다.

거래일자는 거래명세서 시트 [B3] 셀 값을 가져옵니다.

업체명은 거래명세서 시트 [E4] 셀 값을 가져옵니다.

도서명은 거래명세서 시트 [D9] 셀부터 각 항목을 가져옵니다.

가격은 거래명세서 시트 [J9] 셀 값을 가져옵니다.

할인금액은 가져온 가격에 20%를 곱한 수식으로 구합니다.

수량은 거래명세서 시트 [G9] 셀 값을 가져옵니다.

마지막으로 판매금액은 가져온 (가격 - 할인금액) * 수량으로 계산합니다.

 

<Alt + F11>키를 눌러 VBE를 실행합니다.

프로시저를 만들려면 먼저 모듈을 삽입해야 합니다.

[삽입]-[모듈]을 선택합니다.

모듈이 삽입되면 프로시저를 만듭니다.

[삽입]-[프로시저]를 선택합니다.

[프로시저 추가] 대화상자에서 프로시저 이름은 '거래내역저장'으로 작성합니다.

형식과 범위는 기본값으로 두고 [확인] 단추를 누릅니다.

거래내역저장 프로시저가 만들어져 있습니다.

 

거래 내역을 저장하려면 두 시트를 왔다 갔다 하며 필요한 부분을 복사해 붙여 넣는 작업을 합니다.

복사 대상이 있는 거래명세서 시트에서 거래일자와 업체명은 고정된 셀입니다.

거래항목은 [B9] 셀부터 시작합니다.

거래내역 마지막 항목 위치를 확인해야 하는데, B열에서 시작 행인 9행 밑으로 하나씩 확인해 가다 빈 셀이 나오면 더 이상 거래내역이 없다는 뜻입니다.

이렇게 특정 위치부터 순차적으로 확인해 특정 조건이 만족하거나 만족하지 않는 경우 멈출 때 사용할 명령문은 Do ~ Loop입니다.

조건은 현재 선택한 셀이 빈 값이냐를 확인하고 빈 값이 아닌 동안 반복 실행하도록 합니다.

i 변수에 작업 시작 셀의 행번호인 9를 대입합니다.

거래명세서를 작성했다는 것은 최소한 한 개 이상의 거래항목이 있다는 것이기 때문에 Do 행에서 조건을 비교하는 것이 아니라 Loop 문 뒤에서 조건을 비교합니다.

Do ~ Loop 명령문 안에서 행 번호를 의미하는 변수 i 값이 1씩 증가하도록 코드를 입력합니다.

Public Sub 거래내역저장()
    i = 9
    
    Do
        
        i = i + 1
    Loop While Sheets("거래명세서").Range("B" & i).Value <> ""
End Sub

 

거래내역 시트에 이미 예전 거래내역이 입력되어 있으므로 입력된 내용 아래 새 거래내역을 입력해야 합니다.

다음 작업을 진행하기 전에 현재 입력된 거래내역의 마지막 항목 위치를 알아야 하고, 그 다음 행 번호를 우리가 사용할 겁니다.

거래내역 시트 [B2] 셀에 커서를 두고 <Ctrl + 화살표 아래>키를 눌러 입력된 항목 마지막으로 이동합니다.

이 동작을 VBA 코드로 어떻게 적어야 하는지 확인하기 위해 매크로를 기록해 봅니다.

거래내역 시트 [B2] 셀에 커서를 두고 [개발 도구] - [매크로 기록]을 선택합니다.

코드 확인용으로만 사용하고 지울 매크로이기 때문에 이름은 그냥 기본값으로 둡니다.

매크로 기록이 시작되면 <Ctrl + 화살표 아래>를 한 번 누른 뒤 [기록 중지]를 누릅니다.

VBE에서 [프로젝트 탐색창]을 확인해 보면 'Module2'가 만들어져 있습니다.

'Module2'를 더블클릭 합니다.

 

지금 있는 셀이 거래내역 정보가 입력된 마지막 행이고, 우리가 원하는 건 이 다음 행 위치가 필요합니다.

그래서 다시 매크로를 작성합니다.

매크로를 작성하기 전에 [개발 도구]-[상대 참조로 기록]을 선택합니다.

[상대 참조로 기록]을 선택해야 현재 선택된 셀을 기준으로 다음 행을 구할 수 있기 때문입니다.

[개발 도구]-[매크로 기록]을 누릅니다.

이번에도 [매크로 이름]은 기본값으로 둡니다.

<화살표 아래>키를 한 번 누르고 [기록 중지]를 누릅니다.

다시 VBE로 가면 새로운 매크로가 만들어져 있습니다.

여기서 조금 해설이 필요한데, 매크로1 프로시저의 Selection은 처음 시작 위치였던 [B2] 셀을 의미합니다.

매크로2 프로시저의 ActiveCell은 [B2] 셀에서 <Ctrl + 화살표아래>키를 눌러 이동한 셀을 의미합니다.

그래서 이 두 코드를 합쳐 하나로 나타낼 수 있습니다.

Range("B2").End(xlDown).Offset(1,0)

이 셀의 행 번호가 필요합니다.

Range("B2").End(xlDown).Offset(1,0).Row

이렇게 합쳐서 하나로 만든 코드가 제대로 동작하는지 확인하기 위해 [직접 실행 창]을 나타냅니다.

[보기] - [직접 실행 창]를 선택합니다.

? range("b2").end(xlDown).offset(1,0).row

입력하고 <Enter>키를 누릅니다.

결과값 105는 거래내역 시트에 입력된 항목 마지막의 다음 행 번호가 맞습니다.

[직접 실행 창] 코드 중 물음표를 제외하고 복사합니다.

[프로젝트 탐색창]에서 'Module1'을 더블클릭합니다.

j=range("b2").End(xlDown).Offset(1,0).Row

여기서 코드를 조금 더 수정해야 합니다.

우리는 거래명세서 시트와 거래내역 시트, 두 시트를 옮겨가며 코딩하고 있기 때문에 [B2] 셀이 어느 시트 셀이지 명확하게 나타내야 합니다.

Public Sub 거래내역저장()
    i = 9
    
    j = Sheets("거래내역").Range("b2").End(xlDown).Offset(1, 0).Row
    
    Do
    
        i = i + 1
    Loop While Sheets("거래명세서").Range("B" & i).Value <> ""
End Sub

이제 필요 없는 [직접 실행 창]은 닫습니다.

 

나타낼 위치를 확인했으니 나머지 값을 나타내겠습니다.

거래일자를 나타냅니다.

Public Sub 거래내역저장()
    i = 9
    
    j = Sheets("거래내역").Range("b2").End(xlDown).Offset(1, 0).Row
    
    Do
        Sheets("거래내역").Range("B" & j).Value = Sheets("거래명세서").Range("B3").Value
        
        
        i = i + 1
    Loop While Sheets("거래명세서").Range("B" & i).Value <> ""
End Sub

다음 항목을 나타내기 위해서 앞에서 작성한 코드를 복사합니다.

새로 다 적는 것보다는 복사해서 붙여 넣고 바꿀 부분만 수정하는 것이 편합니다.

Public Sub 거래내역저장()
    i = 9
    
    j = Sheets("거래내역").Range("b2").End(xlDown).Offset(1, 0).Row
    
    Do
        Sheets("거래내역").Range("B" & j).Value = Sheets("거래명세서").Range("B3").Value
        Sheets("거래내역").Range("B" & j).Value = Sheets("거래명세서").Range("B3").Value
        Sheets("거래내역").Range("B" & j).Value = Sheets("거래명세서").Range("B3").Value
        
        
        i = i + 1
    Loop While Sheets("거래명세서").Range("B" & i).Value <> ""
End Sub

거래내역 시트 C열에 거래명세서 시트 [E4] 셀 업체명을 가져와 나타내고, 거래내역 시트 D열에 거래명세서 시트 [D9] 셀 도서명을 나타냅니다.

Public Sub 거래내역저장()
    i = 9
    
    j = Sheets("거래내역").Range("b2").End(xlDown).Offset(1, 0).Row
    
    Do
        Sheets("거래내역").Range("B" & j).Value = Sheets("거래명세서").Range("B3").Value
        Sheets("거래내역").Range("C" & j).Value = Sheets("거래명세서").Range("E4").Value
        Sheets("거래내역").Range("D" & j).Value = Sheets("거래명세서").Range("D" & i).Value
        
        i = i + 1
    Loop While Sheets("거래명세서").Range("B" & i).Value <> ""
End Sub

 

이렇게 작성을 하다 보니 시트명까지 길게 나타내야 해서 불편합니다.

변수 선언을 해서 코드 길이를 조금 줄이겠습니다.

S1, S2를 각각 개체 변수로 지정한 뒤 S1은 거래명세서 시트로 설정하고, S2는 거래내역 시트로 설정합니다.

Public Sub 거래내역저장()
    Dim S1 As Object
    Dim S2 As Object
    
    Set S1 = Sheets("거래명세서")
    Set S2 = Sheets("거래내역")
        
    i = 9
    
    j = Sheets("거래내역").Range("b2").End(xlDown).Offset(1, 0).Row
    
    Do
        Sheets("거래내역").Range("B" & j).Value = Sheets("거래명세서").Range("B3").Value
        Sheets("거래내역").Range("C" & j).Value = Sheets("거래명세서").Range("E4").Value
        Sheets("거래내역").Range("D" & j).Value = Sheets("거래명세서").Range("D" & i).Value
        
        i = i + 1
    Loop While Sheets("거래명세서").Range("B" & i).Value <> ""
End Sub

이렇게 시트 이름을 변수로 지정해 사용하면 시트명이 바뀌었을 때도 변수에 값을 대입한 부분만 수정하면 다른 부분을 수정하지 않아도 쓸 수 있게 됩니다.

시트명을 쓴 나머지 코드도 모두 수정합니다.

Public Sub 거래내역저장()
    Dim S1 As Object
    Dim S2 As Object
    
    Set S1 = Sheets("거래명세서")
    Set S2 = Sheets("거래내역")
        
    i = 9
    
    j = S2.Range("b2").End(xlDown).Offset(1, 0).Row
    
    Do
        S2.Range("B" & j).Value = S1.Range("B3").Value
        S2.Range("C" & j).Value = S1.Range("E4").Value
        S2.Range("D" & j).Value = S1.Range("D" & i).Value
        
        i = i + 1
    Loop While S1.Range("B" & i).Value <> ""
End Sub

가격과 수량 항목도 나타냅니다.

Public Sub 거래내역저장()
    Dim S1 As Object
    Dim S2 As Object
    
    Set S1 = Sheets("거래명세서")
    Set S2 = Sheets("거래내역")
        
    i = 9
    
    j = S2.Range("b2").End(xlDown).Offset(1, 0).Row
    
    Do
        S2.Range("B" & j).Value = S1.Range("B3").Value
        S2.Range("C" & j).Value = S1.Range("E4").Value
        S2.Range("D" & j).Value = S1.Range("D" & i).Value
        S2.Range("E" & j).Value = S1.Range("J" & i).Value
        
        S2.Range("G" & j).Value = S1.Range("G" & i).Value
        
        i = i + 1
    Loop While S1.Range("B" & i).Value <> ""
End Sub

할인금액과 판매금액은 계산식으로 나타낼 것이므로 Value 속성이 아니라 Formula 속성을 써야 합니다.

해당 셀에 나타날 완성된 엑셀 수식 모양을 생각하면 코드를 작성합니다.

Public Sub 거래내역저장()
    Dim S1 As Object
    Dim S2 As Object
    
    Set S1 = Sheets("거래명세서")
    Set S2 = Sheets("거래내역")
        
    i = 9
    
    j = S2.Range("b2").End(xlDown).Offset(1, 0).Row
    
    Do
        S2.Range("B" & j).Value = S1.Range("B3").Value
        S2.Range("C" & j).Value = S1.Range("E4").Value
        S2.Range("D" & j).Value = S1.Range("D" & i).Value
        S2.Range("E" & j).Value = S1.Range("J" & i).Value
        S2.Range("F" & j).Formula = "=E" & j & "*20%"
        S2.Range("G" & j).Value = S1.Range("G" & i).Value
        S2.Range("H" & j).Formula = "=(E" & j & "-F" & j & ")*G" & j
        
        i = i + 1
        j = j + 1
    Loop While S1.Range("B" & i).Value <> ""
End Sub

 

VBA 코드가 완성되었으니 제대로 동작하는지 확인해 봅니다.

먼저 실행하기 전에 저장부터 합니다.

[파일]-[저장]을 선택합니다.

VBE에서 단축키 <F5>키를 누르면 실행됩니다.

거래내역 시트 입력된 데이터 마지막 행 부분을 보면 거래내역 시트의 거래내역이 추가된 것을 확인할 수 있습니다.

 

완성된 매크로를 쉽게 실행할 수 있도록 거래명세서 시트에 실행 단추를 추가합니다.

거래명세서 시트를 선택합니다.

[개발 도구] - [컨트롤] - [삽입] 항목에서 [단추(양식 컨트롤)]을 선택한 뒤 [N3] 셀쯤에 그립니다.

[매크로 지정] 대화상자에서 '거래내역저장' 매크로를 선택하고 [확인] 단추를 누릅니다.

양식 컨트롤 텍스트는 '저장'으로 변경합니다.

비어 있는 다른 셀을 선택하면 단추 수정이 완료됩니다.

 

거래명세서를 작성한 뒤 '저장' 단추를 누르면 거래명세서 항목이 거래내역 시트 끝부분에 나타납니다.

 

마무리 정리 작업을 위해 단축키 <Alt + F11>키를 눌러 VBE를 실행합니다.

필요없는 Module2를 삭제합니다.

[프로젝트 탐색창]에서 Module2를 선택하고 마우스 오른쪽 버튼을 누른 뒤 [Module2 제거]를 선택합니다.

확인 창에서 [아니오] 단추를 선택합니다.

VBE 창을 닫습니다.

 

필요하신 분들께 도움이 되기를 바라며 마치겠습니다.

수고하셨습니다.