데이터(레코드)별로 양식 시트에 기록하기
1. 문제
아래와 같은 데이터를
레코드(행)별로 시트를 추가해서 아래 양식 데이터에 기록하려고 합니다.
2. 관련 메서드 및 속성 등
시트를 레코드 개수에 따라 추가하거나 삭제해야 하고,
행별로 반복처리해야 합니다.
가. 시트 추가
구문 : Sheets.Add (Before, After, Count, Type)
| 인수명 | 필수/선택 | 데이터 형식 |
설명 |
| Before | Optional | Variant | An object that specifies the sheet before which the new sheet is added. |
| After | Optional | Variant | An object that specifies the sheet after which the new sheet is added. |
| Count | Optional | Variant | The number of sheets to be added. The default value is the number of selected sheets. |
| Type | Optional | Variant | Specifies the sheet type. Can be one of the following XlSheetType constants: xlWorksheet, xlChart, xlExcel4MacroSheet, or xlExcel4IntlMacroSheet. If you are inserting a sheet based on an existing template, specify the path to the template. The default value is xlWorksheet. |
예: Sheets.Add After:=Sheets(2)
괄호를 넣으면 오히려 에러가 납니다.
나. 시트 삭제
구문 : Worksheet.Delete
Worksheet를 삭제하는데,
Sheets(2) 또는 Sheets("Sheet1")식으로 지정합니다.
예 : Sheets(2).Delete
다. 시트의 개수
구문 : Sheets.Count
직접 실행창에
? sheets.count라고 입력하면
시트의 개수 3이 구해집니다.
라. 일정한 범위의 값을 배열에 넣기
Dim rng As Range
Dim record As Variant
'첫번째 시트의 데이터 영역을 변수와 배열에 저장
Sheets(1).Select
Set rng = Range("a1").CurrentRegion
record = rng
A1셀을 기준으로 CurrentRegion(연속된 범위)을 범위 변수 rng에 넣고,
다시 record = rng로 하면 범위가 record 배열에 저장됩니다.
위 화면 record의 오른쪽으로 가면 Variant/Variant(1 to 3, 1 to 11)로 표시되는데,
1 to 3는 행(row)이고,
1 to 11은 열(column)입니다.
마. Application.DisplayAlerts
시트명에서 마우스 오른쪽 버튼을 누른 후 삭제를 누르면
아래와 같이 시트를 영구적으로 삭제할 것인지 묻는데,
이 창을 표시할 것인지 여부를 설정하는 것이
Application.DisplayAlerts입니다.
Application.DisplayAlerts = False라고 하면 위 창이 나타나지 않고 바로 시트가 삭제됩니다.
원래대로 돌리려면
Application.DisplayAlerts = True라고 하면 됩니다.
3. 데이터(레코드) 별로 양식 시트에 기록하기
Option Explicit
Sub input_form()
Dim i As Integer, j As Integer, k As Integer
Dim rng As Range
Dim record As Variant
Dim ws As Worksheet
'화면 업데이트 안함
Application.ScreenUpdating = False
'첫번째 시트의 데이터 영역을 변수와 배열에 저장
Sheets(1).Select
Set rng = Range("a1").CurrentRegion
record = rng
'레코드 개수로 시트 개수 맞추기
i = Sheets.Count
'작다면 시트를 추가하고
If i < UBound(record, 1) Then
'레코드의 개수보다 작을 동안 양식 시트 복사해서 추가
While i < UBound(record, 1)
Sheets(2).Copy After:=Sheets(Sheets.Count)
i = i + 1
Wend
'크다면 시트 삭제
ElseIf i > UBound(record, 1) Then
For i = Sheets.Count To UBound(record, 1) + 1 Step -1
'삭제시 경고 대화 상자 표시 안함
Application.DisplayAlerts = False
Sheets(i).Delete
Next
'삭제시 경고 대화 상자 표시 원래대로
Application.DisplayAlerts = True
End If
'양식의 9행부터 기록함
k = 9
'한 줄씩 처리
For i = 2 To UBound(record, 1)
Set ws = Sheets(i)
'한 열씩 처리
For j = 1 To UBound(record, 2)
'Raw Data의 데이터를 맞는 위치에 기록
ws.Cells(k, "E") = Sheets(1).Cells(i, j)
'2줄씩 아래에 기록
k = k + 2
Next
'시트의 이름을 이름으로 변경
ws.Name = ws.Range("e9") & "." & ws.Range("E15")
'k값 초기화
k = 9
Next
Application.ScreenUpdating = True
End Sub