VBA로 동일한 형식의 보고서 여러 장을 만들어보자.

포스트 목차.

• 01. 문제
• 02. 문제 해결 방법
• 03. 예제
• 04. 관련 포스트

 

 

01. 문제

누군가가 부탁한 문제이다.  심사위원회가 열리고 심사위원이 동일한 상황에서 특정 모델의 심사 결과표를 출력하는 엑셀파일을 만들어달라는 부탁이었다.

 

심사 결과표는 대략적으로 위와 유사한 형태이다.

 

그냥 만들어서 출력하면되는 일이지만, 심사해야할 모델이 많아서 저 페이지를 여러장 만들어야 하는 불편함이 있다고 한다.

 

심사 결과표는 모든 항목이 동일하지만, 접수번호와 모델명이 변경된다고 한다.

 

 

문제를 요약하면 다음과 같다.

 

1.  심사결과표를 여러장 출력해야됨

2.  심사결과표마다  접수번호와 모델명이 다름

3.  출력버튼 한번만 눌러서 여러 장의 심사결과표를 출력할 수 있어야 함

 

02. 문제 해결 방법

(1) 시트 구성

  - 설정: 접수번호와 모델명이 들어갈 시트로 심사결과표 생성을 위한 버튼을 포함하고 있다. 

  - 템플릿:  심사표 양식이 들어갈 시트

  - 심사결과표: 출력할 심사표들이 들어갈 시트

 

위와 같이 시트를 구성해보자.

설정 시트

템플릿 시트

 

(2) 심사 결과표 만들기

템플릿 시트에 들어갈 심사 결과표를 만드는 작업은 수작업이라고 볼 수 있다.  셀 높이, 셀 넢이, 셀 병합을 적절하게 활용하여 심사 결과표를 만들어 준다. 

 

출력 > 미리보기를 이용하여 한 페이지에 적절하게 출력되는지 확인해보자.

 

(3) 시트 전체의 내용 지우기

심사 결과표 양식을 다 만들었으면, 템플릿 시트에 있는 양식을 심사결과표 시트에 붙여넣기 해야한다. 그 전에 심사결과표 시트의 내용을 모두 삭제 하는 작업이 필요하다. 

 

Worksheets 객체의 Cells.Clear를 이용하여 시트 전체를 초기화할 수 있다.

    '심사결과표 시트 초기화
    Worksheets("심사결과표").Activate
    Worksheets("심사결과표").Cells.Clear

위의 코드는 심사결과표를 시트를 초기화 한다.

 

(4) 셀의 너비 높이를 복사하기

심사결과표 시트에 템플릿 시트에 있는 심사결과표 양식을 복사하면, 내용은 복사가 되지만 행의 높이와 열의 너비는 복사가 되지 않는다.

 

따라서 행의 높이와 열의 너비를 복사하는 과정이 필요한다.

 

특정 열의 너비에 접근하기 위해서는 Columns.ColumnWidth 속성을 사용해야한다. 템플릿 시트의 1열의 너비에 접근하는  방법은 다음과 같다.

Worksheets("템플릿").Columns(1).ColumnWidth

 

특정 행의 높이에 접근하기 위해서는 Rows.RowHeight 속성을 사용해야 한다. 템플릿 시트의 1행의 높이에 접근하는 방법은 다음과 같다.

Worksheets("템플릿").Rows(1).RowHeight

 

템플릿 시트의 행의 높이와 열의 너비를 저장한 후,  심사결과표 시트에 행의 높이와 열의 너비를 적용해보자.

 

(5) 셀의 내용을 복사하기

심사결과표 시트에 템플릿 시트에 있는 심사결과표 양식을 복사하기 위해서는  copy와  paste 메소드를 사용하면 된다.

 

 - 복사하는 과정

영역선택 -> copy 메소드 사용

 

- 붙여넣기 과정

 영역선택 -> paste 메소드 사용

 

주의할 점은 copy 메소드는 Range 객체의 메소드이고, paste 메소드는 워크시트 객체의 메소드라는 점이다.

 

템플릿에서 영역을 선택 후 복사하는 코드는 다음과 같다.

    Worksheets("템플릿").Activate
    Worksheets("템플릿").Range(Cells(1, 1), Cells(30, 11)).Select
    Selection.Copy

 

심사결과표의 영역을 선택 후 붙여넣기하는 코드는 다음과 같다.

    
    Worksheets("심사결과표").Activate
    Worksheets("심사결과표").Cells(startRow, startCol).Select
    Worksheets("심사결과표").Paste

 

03. 예제

이제 예제를 작성해보자.

 

예제는 다음과 같은 Sub 프로시저를 정의하여 사용하려고 한다.

 

서브 프로시저 1 (단추1_Click): 버튼을 클릭했을 때 실행되는 프로시저

 

서브 프로지저 2 (템플릿_페이지복사하기): 심사결과표에 들어갈 접수번호/모델명을 입력받아서, 템플릿 시트에 있는 양식을 복사한 후 심사결과표 시트에 있는 적절한 위치에 복사한다. 적절한 위치를 계산하기 위해 템플릿 시트에서 소용되는 행의 수와 지금 작업하는 심사결과표가 몇번째 표인지에 대한 정보를 함께 입력 받는다.

 

서브 프로시저3 (심사결과표_행높이_맞추기): 템플릿 시트의 행높이를 그대로 가져와서 심사결과표의 행높이를 맞춘다. 적절한 위치를 계산하기 위해 템플릿 시트에서 소용되는 행의 수와 지금 작업하는 심사결과표가 몇번째 표인지에 대한 정보를 함께 입력 받는다.

 

서브프로시저4 (심사결과표_열너비_맞추기):  템플릿 시트의 열너비를 그대로 가져와서 심사결과표의 열너비를 맞춘다. 여기서 출력되는 모든 심사결과표는 동일한 열너비를 갖기 때문에 해당 작업은 한번만 해주면 된다.

 

 

가장 핵심 역할인 서브 프로지서2의 코드는 다음과 같다.

 

Sub 템플릿_페이지복사하기(applNo As Variant, modelNm As Variant, depth As Integer, index As Integer)
    '템플릿페이지에서 유효한 영역을 선택 후 복사한다.
    Worksheets("템플릿").Activate
    Worksheets("템플릿").Range(Cells(1, 1), Cells(depth, 11)).Select
    Selection.Copy
    
    '심사결과표 시트에서 복사할 영역의 주소를 구한다.
    startRow = (index - 1) * depth + 1
    startCol = 1
    
    '접수번호와 모델명이 들어갈 주소를 계산한다.
    appNoRow = 4
    modelRow = 9
    appNoCol = 3
    modelCol = 3
    
    appNoRow = appNoRow + (index - 1) * depth
    modelRow = modelRow + (index - 1) * depth
    
    '복사한 템플릿 시트의 내용을 심사결과표에 붙여넣기한다.
    Worksheets("심사결과표").Activate
    Worksheets("심사결과표").Cells(startRow, startCol).Select
    Worksheets("심사결과표").Paste
    
    '접수번호와 모델명을 해당 주소의 셀에 기입한다.
    Worksheets("심사결과표").Cells(appNoRow, appNoCol) = applNo
    Worksheets("심사결과표").Cells(modelRow, modelCol) = "(모델명: " & modelNm & ")"
 
End Sub

 

열너비와 행높이를 조정하는 서브프로세저의 코다는 다음과 같다.

Sub 심사결과표_행높이_맞추기(depth As Integer, index As Integer)
    '행높이 저장을 위해 적절한 크기의 배열 사용
    Worksheets("템플릿").Activate
    Dim heightArray(100) As Integer
    
    '템플릿 시트의 행높이를 배열에 저장한다.
    For i = 1 To depth
        heightArray(i) = Worksheets("템플릿").Rows(i).RowHeight
    Next i
    
    '심사결과표 시트의 행 높이를 배열에 저장된 값을 기준으로 조정한다.
    Worksheets("심사결과표").Activate
    startRow = (index - 1) * depth
            
    For i = 1 To depth
       Worksheets("심사결과표").Rows(startRow + i).RowHeight = heightArray(i)
    Next i
End Sub
 
 
Sub 심사결과표_열너비_맞추기()
    '열너비 저장을 위해 적절한 크기의 배열 사용
    Worksheets("템플릿").Activate
    Dim widthArray(20) As Integer
    
    '템플릿 시트의 열너비를 배열에 저장한다.
    For i = 1 To 20
        widthArray(i) = Worksheets("템플릿").Columns(i).ColumnWidth
    Next i
    
    '심사결과표 시트의 열 너비를 배열에 저장된 값을 기준으로 조정한다.
    Worksheets("심사결과표").Activate
    For i = 1 To 20
       Worksheets("심사결과표").Columns(i).ColumnWidth = widthArray(i)
    Next i
End Sub

 

마지막으로 버튼을 클릭했을 때, 위의 프로시저들을 실행하는 메인작업을 담당하는 프로시저의 코드는 다음과 같다.

Sub 단추1_Click()
    '입력한 데이터의 수 알아내기
    '첫번째 줄은 제목이기 때문에 2번째 줄부터 시작
    startRow = 2
    Worksheets("설정").Activate
    '마지막 데이터가 있는 줄 찾기
    endRow = Worksheets("설정").Cells(Rows.Count, 1).End(xlUp).Row
    '만들어야할 심사결과표의 수 구하기
    numOfPrints = endRow - startRow + 1
      
        
    '심사결과표 시트 초기화
    Worksheets("심사결과표").Activate
    Worksheets("심사결과표").Cells.Clear
    
    
    '템플릿 페이지의 열너비와 심사결과표 페이지의 열너비를 일치하게 설정한다.
    심사결과표_열너비_맞추기
    
    '만들어야 하는 심사결과표에 맞게 복사작업 반복하기
    For i = 1 To numOfPrints
        '설정 시트에 있는 접수번호와 모델명을 가져온다.
        Worksheets("설정").Activate
        applicationNo = Worksheets("설정").Cells(startRow + i - 1, 1)
        modelName = Worksheets("설정").Cells(startRow + i - 1, 2)
        '심사결과표의 행높이를 템플릿 시트의 행높이와 일치하게 설정한다.
        심사결과표_행높이_맞추기 depth:=30, index:=Int(i)
        '템플릿페이지를 복사한다.
        템플릿_페이지복사하기 applNo:=applicationNo, modelNm:=modelName, depth:=30, index:=Int(i)
    
    Next i
    
End Sub

 

설정 시트가 다음과 같을 때의 실행 결과를 알아보자.

 

여러장의 심사 결과표가 심사결과표 시트에 생성되는 것을 확인할 수 있다.

 

04. 관련 포스트

 

(1) VBA로 선택하여 붙여넣기

https://eggdrop.tistory.com/5

엑셀 매크로 (VBA) - 001. 특정 행을 복사하여 삽입하기

 

(2) VBA로 셀의 높이 너비 조정하기

https://eggdrop.tistory.com/154

엑셀 VBA 셀의 너비와 높이 가져오기 , 셀의 너비와 높이 변경하기

 

(3) VBA 버튼 컨트롤

https://eggdrop.tistory.com/21

Excel 매크로 (VBA) - 009. 버튼 컨트롤 (Excel Form vs. ActiveX )

 

(4) 데이터가 있는 셀의 범위 알아내기

https://eggdrop.tistory.com/9

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