회계업무 자동화 사례공유: 엑셀 매크로 활용 방법 #4

지금까지 3편에 걸쳐 회계 업무 자동화를 위해 기존 업무를 분석하고 개선하는 과정을 자세히 알아보았습니다. 이번 편에서는 엑셀 매크로를 활용한 회계 업무 자동화의 마지막으로 개발한 전체 소스 코드를 공유하고 설명하도록 하겠습니다.

회계업무 자동화 사례공유: 엑셀 매크로 활용 방법 #4 - 전체 소스코드

최적화된 소스는 아니지만 실무에서 효과가 검증됐기 때문에 현재 회계 업무를 하시는 분들중 필요하신 분들은 참고하시면 좋을 것 같습니다.

엑셀 매크로를 활용한 회계업무 자동화 - 전체 소스코드

Sub 선수수익_EAs외()

Dim ws_rev As Worksheet
Dim ws_eas As Worksheet
Dim ws_cho As Worksheet
Dim ws_mal As Worksheet

Set ws_rev = ThisWorkbook.Sheets("매출")
Set ws_eas = ThisWorkbook.Sheets("선수수익-EAs 외")
Set ws_cho = ThisWorkbook.Sheets("선수수익(초)")
Set ws_mal = ThisWorkbook.Sheets("선수수익(말)")

Dim roww As Integer

'결산월 체크
If Format(Date, "mm") = 1 Then
cm = (Format(Date, "yyyy") - 1 & "12") * 1
Else
cm = (Format(Date, "yyyymm") - 1) * 1
End If

roww = 3
bcf = 0 '이전월 시작 row
bce = 0 '이전월 끝 row


Do While ws_eas.Cells(roww, "A").Value <> ""
If ws_eas.Cells(roww, "A").Value = cm - 1 Then
If bcf = 0 Then
bcf = roww
Else
bce = roww
End If

'K ~ M 을 값으로 치환
ws_eas.Cells(roww, "K") = ws_eas.Cells(roww, "K")
ws_eas.Cells(roww, "L") = ws_eas.Cells(roww, "L")
ws_eas.Cells(roww, "M") = ws_eas.Cells(roww, "M")

End If
roww = roww + 1
Loop

rv = msgshow("전월 데이터를 복사합니다.")

'이전월의 데이터를 결산월로 복사
Dim sourceRange As Range
Dim destinationRange As Range

Set sourceRange = ws_eas.Range("A" & bcf & ":O" & bce)
Set destinationRange = ws_eas.Range("A" & bce + 1)
sourceRange.Copy destinationRange

roww = bce + 1

rv = ull(roww) '굵은 언더라인

Do While ws_eas.Cells(roww, "A") <> ""
ws_eas.Cells(roww, "A") = cm
ws_eas.Cells(roww, "K") = Replace("=$H<>-$M$1", "<>", roww)
ws_eas.Cells(roww, "L") = Replace("=IF(XLOOKUP((A<>-1)&B<>&C<>&E<>,$N:$N,$M:$M,0)=0,_
       0, XLOOKUP((A<>-1)&B<>&C<>&E<>,$N:$N,$M:$M,0)-M<>)", "<>", roww)
ws_eas.Cells(roww, "M") = Replace("=IF(K<><=0, 0, ROUND((I<>/J<>)*K<>,0))", "<>", roww)
roww = roww + 1
rv = msgshow("복사한 데이터의 선수수익, 정리액, 잔액에 수식을 입력하고 있습니다. ( " & roww & " 행 처리중)")
Loop

rv = msgshow("선수수익(초)의 기존 데이터를 삭제합니다.")

'선수수익(초) 기존 데이터 삭제
lastRow = 0
lastRow = ws_cho.Cells(Rows.Count, 1).End(xlUp).Row
If lastRow <= 1 Then
lastRow = 2
End If
ws_cho.Range("A2:BH" & lastRow).ClearContents

'선수수익(초) 데이타 작성
roww = bce + 1
row1 = 2
rno = 1

'결산월의 마지막 날을 알아내기
cms = cm & ""
Dim targetDate As Date
Dim lastday As Integer
targetDate = DateSerial(Left(cms, 4) * 1, Right(cms, 2) * 1 + 1, 1) - 1
lastday = Day(targetDate)

Do While ws_eas.Cells(roww, "A") <> ""
If ws_eas.Cells(roww, "L").Value > 0 Then
If ws_eas.Cells(roww, "O").Value <> "해외법인" Then
acc1 = "21280200"
acc2 = "51990103"
Else
acc1 = "21280200"
acc2 = "51990104"
End If

ws_cho.Cells(row1, "A").Value = rno '구룹핑번호
ws_cho.Cells(row1 + 1, "A").Value = rno
ws_cho.Cells(row1, "B").Value = "X" '헤더지시자
ws_cho.Cells(row1, "D").Value = cm & lastday '증빙일
ws_cho.Cells(row1 + 1, "D").Value = cm & lastday
ws_cho.Cells(row1, "E").Value = cm & lastday '전기일
ws_cho.Cells(row1 + 1, "E").Value = cm & lastday
ws_cho.Cells(row1, "F").Value = "4" '전기기간
ws_cho.Cells(row1, "G").Value = "SB" '전표유형
ws_cho.Cells(row1, "H").Value = "1000" '회사코드
ws_cho.Cells(row1, "I").Value = "KRW" '통화
ws_cho.Cells(row1, "L").Value = "[선수수익][" & Left(cms, 4) & "-" & Right(cms, 2) & "] 수익인식" '전표헤더텍스트
ws_cho.Cells(row1, "N").Value = "40" '전기키
ws_cho.Cells(row1 + 1, "N").Value = "50"
ws_cho.Cells(row1, "O").Value = acc1 '계정
ws_cho.Cells(row1 + 1, "O").Value = acc2
ws_cho.Cells(row1, "S").Value = ws_eas.Cells(roww, "L").Value '전표통화금액
ws_cho.Cells(row1 + 1, "S").Value = ws_eas.Cells(roww, "L").Value
ws_cho.Cells(row1, "W").Value = "1018" '사업장
ws_cho.Cells(row1 + 1, "W").Value = "1018"
ws_cho.Cells(row1, "Y").Value = "5000" '사업장
ws_cho.Cells(row1 + 1, "Y").Value = "5000" '사업영역
ws_cho.Cells(row1, "AA").Value = ws_eas.Cells(roww, "B").Value 'WBS NO
ws_cho.Cells(row1 + 1, "AA").Value = ws_eas.Cells(roww, "B").Value
ws_cho.Cells(row1, "AK").Value = ws_eas.Cells(roww, "C").Value '참조키 1
ws_cho.Cells(row1 + 1, "AK").Value = ws_eas.Cells(roww, "C").Value
ws_cho.Cells(row1, "AL").Value = ws_eas.Cells(roww, "D").Value '참조키 2
ws_cho.Cells(row1 + 1, "AL").Value = ws_eas.Cells(roww, "D").Value
ws_cho.Cells(row1, "AP").Value = ws_eas.Cells(roww, "F").Value '텍스트(SGTXT)
ws_cho.Cells(row1 + 1, "AP").Value = ws_eas.Cells(roww, "F").Value
row1 = row1 + 2
rno = rno + 1
End If

roww = roww + 1

rv = msgshow("선수수익(초) 데이터를 양식에 맞게 입력하고 있습니다. ( " & rno & " 번째 데이터 입력중)")
Loop

'매출 시트에서 반영이 "Y"인 IT자산 항목을 "선수수익-EAs 외" 로 복사
roww = bce + (bce - bcf) + 2 '마지막행 다음행
rv = ull(roww) '굵은 언더라인

row1 = 5 '매출시트 시작행
Do While ws_rev.Cells(row1, "A").Value <> ""
If ws_rev.Cells(row1, "AT").Value = "Y" And ws_rev.Cells(row1, "I").Value = "IT 자산 유지보수" Then
ws_eas.Cells(roww, "A").Value = cm '월
ws_eas.Cells(roww, "B").Value = ws_rev.Cells(row1, "A").Value 'WBS
ws_eas.Cells(roww, "C").Value = Trim(ws_rev.Cells(row1, "C").Value) '거래처코드
ws_eas.Cells(roww, "D").Value = ws_rev.Cells(row1, "D").Value '고객사
ws_eas.Cells(roww, "E").Value = ws_rev.Cells(row1, "F").Value '계약명
ws_eas.Cells(roww, "F").Value = ws_rev.Cells(row1, "D").Value & " - " & ws_rev.Cells(row1, "F").Value '전표헤더텍스트
ws_eas.Cells(roww, "G").Value = ws_rev.Cells(row1, "K").Value '계약 기산일
ws_eas.Cells(roww, "H").Value = ws_rev.Cells(row1, "L").Value '계약 만기일
ws_eas.Cells(roww, "I").Value = ws_rev.Cells(row1, "R").Value '계약금액
ws_eas.Cells(roww, "J").Value = ws_eas.Cells(roww, "H").Value - ws_eas.Cells(roww, "G").Value + 1 '총일수
ws_eas.Cells(roww, "K").Value = Replace("=$H<>-$M$1", "<>", roww)
ws_eas.Cells(roww, "L").Value = Replace("=IF(XLOOKUP((A<>-1)&B<>&C<>&E<>,$N:$N,$M:$M,0)=0, 0,_
           XLOOKUP((A<>-1)&B<>&C<>&E<>,$N:$N,$M:$M,0)-M<>)", "<>", roww)
ws_eas.Cells(roww, "M").Value = Replace("=IF(K<><=0, 0, ROUND((I<>/J<>)*K<>,0))", "<>", roww)
ws_eas.Cells(roww, "N").Value = Replace("=A<>&B<>&C<>&E<>", "<>", roww)
'ws_eas.Cells(roww, "O").Value = "해외법인 확인"
Dim currentCell As Range
Dim aboveRow As Range
Set currentCell = ws_eas.Cells(roww, "O")
Set aboveRow = currentCell.Offset(-1)
aboveRow.Copy currentCell

If ws_eas.Cells(roww, "O").Value = "해외법인" Then '해외법인일 경우 원화를 찾아 입력
ws_eas.Cells(roww, "I").Value = Replace("=XLOOKUP(매출!A<>&매출!R<>,미성공사!$X:$X,미성공사!$O:$O)", "<>", row1)
End If

roww = roww + 1
End If
row1 = row1 + 1

rv = msgshow("선수수칙-EAs외에 신규 추가 데이터를 추가하고 있습니다. ( " & roww & " 행 추가중)")
Loop


'선수수익(말) 기존 데이터 삭제
lastRow = 0
lastRow = ws_mal.Cells(Rows.Count, 1).End(xlUp).Row
If lastRow <= 1 Then
lastRow = 2
End If
ws_mal.Range("A2:BH" & lastRow).ClearContents

roww = bce + (bce - bcf) + 2 '신규 항목 시작 행
row1 = 2
rno = 1

'선수수익(말) 데이터 입력
Do While ws_eas.Cells(roww, "A") <> ""
If ws_eas.Cells(roww, "M").Value > 0 Then
If ws_eas.Cells(roww, "O").Value <> "해외법인" Then
acc1 = "21280200"
acc2 = "51990103"
Else
acc1 = "21280200"
acc2 = "51990104"
End If

ws_mal.Cells(row1, "A").Value = rno '구룹핑번호
ws_mal.Cells(row1 + 1, "A").Value = rno
ws_mal.Cells(row1, "B").Value = "X" '헤더지시자
ws_mal.Cells(row1, "D").Value = cm & lastday '증빙일
ws_mal.Cells(row1 + 1, "D").Value = cm & lastday
ws_mal.Cells(row1, "E").Value = cm & lastday '전기일
ws_mal.Cells(row1 + 1, "E").Value = cm & lastday
ws_mal.Cells(row1, "F").Value = "4" '전기기간
ws_mal.Cells(row1, "G").Value = "SB" '전표유형
ws_mal.Cells(row1, "H").Value = "1000" '회사코드
ws_mal.Cells(row1, "I").Value = "KRW" '통화
ws_mal.Cells(row1, "L").Value = "[선수수익][" & Left(cms, 4) & "-" & Right(cms, 2) & "] 선수수익" '전표헤더텍스트
ws_mal.Cells(row1, "N").Value = "50" '전기키
ws_mal.Cells(row1 + 1, "N").Value = "40"
ws_mal.Cells(row1, "O").Value = acc1 '계정
ws_mal.Cells(row1 + 1, "O").Value = acc2
ws_mal.Cells(row1, "S").Value = ws_eas.Cells(roww, "M").Value '전표통화금액
ws_mal.Cells(row1 + 1, "S").Value = ws_eas.Cells(roww, "M").Value
ws_mal.Cells(row1, "W").Value = "1018" '사업장
ws_mal.Cells(row1 + 1, "W").Value = "1018"
ws_mal.Cells(row1, "Y").Value = "5000" '사업장
ws_mal.Cells(row1 + 1, "Y").Value = "5000" '사업영역
ws_mal.Cells(row1, "AA").Value = ws_eas.Cells(roww, "B").Value 'WBS NO
ws_mal.Cells(row1 + 1, "AA").Value = ws_eas.Cells(roww, "B").Value
ws_mal.Cells(row1, "AK").Value = ws_eas.Cells(roww, "C").Value '참조키 1
ws_mal.Cells(row1 + 1, "AK").Value = ws_eas.Cells(roww, "C").Value
ws_mal.Cells(row1, "AL").Value = ws_eas.Cells(roww, "D").Value '참조키 2
ws_mal.Cells(row1 + 1, "AL").Value = ws_eas.Cells(roww, "D").Value
ws_mal.Cells(row1, "AP").Value = ws_eas.Cells(roww, "F").Value '텍스트(SGTXT)
ws_mal.Cells(row1 + 1, "AP").Value = ws_eas.Cells(roww, "F").Value
row1 = row1 + 2
rno = rno + 1
End If

roww = roww + 1

rv = msgshow("선수수익(말) 에 양식에 맞게 데이터를 입력하고 있습니다. ( " & rno & " 행 입력중)")
Loop

rv = msgshow("")

MsgBox "끝"

End Sub

Function ull(r As Integer)
Sheets("선수수익-EAs 외").Range("A" & r & ":O" & r).Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
Selection.Borders(xlEdgeLeft).LineStyle = xlNone
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.ColorIndex = xlAutomatic
.TintAndShade = 0
.Weight = xlThick
End With
End Function

Function msgshow(s As String)

Application.StatusBar = s

End Function

이번 편을 마지막으로 총 4편의 엑셀 매크로를 활용한 회계업무 자동화 사례를 공유드렸습니다. 복잡하거나 어려운 것은 아니지만 처음 보는 분들에게는 어렵게 느껴지실 수도 있을 것 같습니다. 그러나 이번 사례를 스터디 하신다면 현업에서 충분히 활용하실 수 있을 거라 생각합니다. 많은 분들이 단순반복작업에서 해방되시길 진심으로 기원드립니다.

회계업무 자동화 사례공유: 엑셀 매크로 활용 방법 #4 - 전체 소스코드

회계업무 자동화 사례공유: 엑셀 매크로 활용 방법 #4 - 전체 소스코드

엑셀 매크로를 활용한 회계업무 자동화 - 전체 소스코드

Popular Posts

Most Recent

Contact form