vba로 엑셀의 특정 시트를 복사하고 이름을 변경하는 코드를 간단하게 만들어 보았습니다.
시트복사라는 Sub 프로시져가 실행되면 복사할 시트가 몇번째로 있는지 입력을 받고
시트를 몇개 복사하는 지 그리고 이름은 어떻게 하는지에 대한 정의로 시트이름을 드래그하면
드래그한 셀의 갯수만큼 복사가 되는 코드입니다.
Sub 시트복사()
'
' 시트복사 Macro
'
'
Dim Copysheetnumber As Integer '카피할 시트가 몇번째인지
Dim ReturnSel As Range '복사될 시트의 이름 리스트
Copysheetnumber = Application.InputBox("복사할 Sheet가 몇번째 Sheet인지 입력.", "복사할 Sheet 번호 입력", Type:=1)
Set ReturnSel = Application.InputBox("추가될 Sheet 이름들을 드래그하여 선택하세요.", "범위선택", Type:=8)
'사용자의 입력을 받은 후
Dim index As Integer
For index = 1 To ReturnSel.Count
AddCopiedSheet Copysheetnumber, ReturnSel.Cells(index, 1).value, ReturnSel.Cells(index, 1).Row
Next
End Sub
Function AddCopiedSheet(number, sheetname, rowvalue)
Worksheets(number).Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
'시트를 복사한 뒤
Sheets(ActiveWorkbook.Sheets.Count).Name = sheetname
'이름 리스트의 이름으로 시트명 변경
End Function
이때 Application.InputBox함수로 입력받는 Type의 종류는 7가지로
0은 formula(수식), 1은 숫자, 2는 string형, 4는 boolean형, 8은 셀의 범위, 16은 에러값(#N/A), 64는 배열을 입력 받을 수 있습니다.
위의 기초적인 코드를 활용하여 복사될 시트의 각 셀에 참조를 걸어두어
복사가 이루어지게 한 뒤 아래의 Replace 함수를 이용하여 시트별 참조경로를 업데이트하는 식으로 응용할 수 있습니다.
Sheets(ActiveWorkbook.Sheets.Count).Cells.Replace what:="변경할 값", Replacement:="변경 값", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
최종코드는 리스트를 활용하여 각 행별 시트들을 생성하는 코드입니다.
Sub 시트복사()
Dim Copysheetnumber As Integer
Dim replacerowvalue As Integer
Dim ReturnSel As Range
Copysheetnumber = Application.InputBox("복사할 시트가 몇번째 시트인지 입력하세요.", "복사할 Sheet 번호 입력", Type:=1)
Set ReturnSel = Application.InputBox("추가될 시트 이름들을 드래그하여 선택하세요.", "범위선택", Type:=8)
replacerowvalue = Application.InputBox("복사된 뒤 변경해야 할 숫자값을 입력하세요.", "변경할 숫자값 입력", Type:=1)
Dim index As Integer
For index = 1 To ReturnSel.Count
AddCopiedSheet Copysheetnumber, ReturnSel.Cells(index, 1).value, ReturnSel.Cells(index, 1).Row, replacerowvalue
Next
End Sub
Function AddCopiedSheet(number, sheetname, rowvalue, replacerowvalue)
Worksheets(number).Copy After:=Sheets(ActiveWorkbook.Sheets.Count)
Sheets(ActiveWorkbook.Sheets.Count).Name = sheetname
Sheets(ActiveWorkbook.Sheets.Count).Cells.Replace what:=replacerowvalue, Replacement:=rowvalue, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
SearchFormat:=False, ReplaceFormat:=False
End Function
만약에 학생 성적 리스트를 자동으로 한 시트씩 특정한 형식에 맟춰서 재작성 되어야 하는 경우를 예로 들었습니다.
먼저 만들 시트이름이 할줄씩 정의된 리스트와
기준이 될 "복사될시트"를 하나 생성을 한 뒤 내용은 리스트의 내용을 참조를 걸어둡니다.
코딩된 내용으로 매크로를 실행하면 복사할 시트가 몇번째 시트인지 입력하고
생성할 시트명들을 드래그한 뒤
복사될 시트에서 참조되고 있는 숫자가 3이므로 변경할 숫자 값을 3으로 입력하게 되면
다음과 같이 리스트에 맞춰 각각의 정보가 들어간 시트들이 자동으로 생성됩니다.