[엑셀VBA] 필터별로 파일 분리해서 저장하기

엑셀에서 필터별로 따로 파일을 만들고 싶을 때가 있습니다. 

필터를 누른 다음 하나하나 클릭해서 복사하자니 시간이 너무 오래 걸립니다. 

예시) 데이터 열 선택 → 필터 선택 → 데이터 선택 → 복사 → 새파일 열기 → 붙여넣기 → 저장하기

이 과정을 100번 이상 해야한다고 가정하면 정말 힘들 것 같습니다.

귀찮고 지루한 과정을 엑셀VBA를 통해 간단히 만들 수 있습니다.

#01. 필터별 저장하기 예시

아래 예시는 이전 블로그에서 설명한 가짜 데이터입니다.

2023.07.16 - [파이썬(Python)] - 파이썬으로 가상의 데이터 만들기(Feat. Faker)

필터별로 데이터 저장 예시

#02. 결과 미리보기

실행 전 빈 폴더('파일분리')라는 폴더에서 매크로 실행 후 Company 열 데이터를 하나씩 만들었음을 볼 수 있습니다. 

안에 내용도 필터링 되어 저장되었습니다 (영상 만들 때 파일 여는 것까진 보여드리는 걸 깜박했네요)

 

#03. 매크로 활용 전 사전준비사항

매크로 파일로 모든 것을 자동화하면 좋겠지만 그렇게 만들 경우 난이도가 더욱 높아집니다.

저희는 활용할 수 있는 수준에서 쉽게 만들기 위해 사전작업을 살짝 해줍니다.

 

1. 필터별로 보고싶은 데이터를 미리 두 번째 시트에 저장해둡니다.

2. 동일 폴더 경로에 '파일분리'라는 새 파일을 하나 만들어 둡니다.

 

1번의 경우 예시 파일의 경우에 'Company' 라는 열의 '유한회사 권', '(유) 신김박', '유한회사 김이박' 3개의 데이터를 'Sheet2'에 저장해두었습니다. 또한 첫 번째 전체파일이 있는 시트의 이름은 'RawData'라고 저장해두었습니다. 

 

사전준비1) 두 번째 시트에 중복제거된 값 'A'열에 넣어둠

사전준비2) 동일경로에 '파일분리'라는 새 파일 만들기

'파일분리'라는 파일에 우리가 필터별로 저장할 파일을 깔끔하게 정리하기 위해 만들었습니다.

 

#04. VBA 완성코드 미리보기

Sub Filtering_Data()

Dim company_lst As String
Dim i As Long

Application.ScreenUpdating = False
Application.DisplayAlerts = False

For i = 2 To Sheets("Company").Cells(Rows.Count, "a").End(xlUp).Row

    company_lst = Sheets("Company").Range("a" & i).Value
    
    Sheets("RawData").Range("a3").CurrentRegion.AutoFilter Field:=1, Criteria1:=company_lst
    Sheets("RawData").Range("a3").CurrentRegion.Copy
    
    Workbooks.Add
    ActiveWorkbook.Sheets(1).Paste
    Columns("A:D").AutoFit
    Range("A1").Select
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\파일분리\" & company_lst
    ActiveWorkbook.Close
       
Next

Sheets("RawData").Range("A3").CurrentRegion.AutoFilter

Application.ScreenUpdating = True
Application.DisplayAlerts = True

MsgBox "File Save Completed"

End Sub

 

#05. 코드설명 ① For ~ Next문 , Sheets("Company").Cells ~ 구문

앞서 설명드렸듯, 이번에는 시트 간 이동이 있기 때문에 시트명도 잘 보셔야 합니다.

Sheet1의 경우, 'RawData'

Sheet2의 경우, 'Company'라고 저장하였습니다.

 

먼저 For 반복문 내용부터 살펴보겠습니다.

For i = 2 To Sheets("Company").Cells(Rows.Count, "a").End(xlUp).Row
Next

For ~ Next 문의 경우, 항상 같이 쓰며 안에 있는 명령을 언제까지 반복하라는 내용입니다.

2 To 4라고 작성했다면, 2 ~ 4 까지 반복입니다.

지금 예시의 경우 2 To Sheets("Company").Cells(Rows.Count, "a").end(xlup).Row로 작성되어 있는데, 뒷 부분은 변수로 이루어져 있는 명령어 입니다.

Sheets("Company") → "Company" 시트로 가서

Cells(Rows.Count, "a") → "A"열의 맨 마지막으로 가(엑셀 행의 마지막인 1,048,576행)

end(xlup) → "Ctrl + ↑"와 동일한 동작 ( 데이터가 있는 마지막 행으로 이동합니다)

Rows → 마지막 행의 번호를 알려줍니다. 

	A열
1행	Company
2행	유한회사 권
3행	(유) 신김박
4행	유한회사 김이박

현재 엑셀 데이터 예시를 보게되면 4행이 마지막입니다. 그러므로 반복문은 2행부터 4행까지 총 3회 반복합니다(2행, 3행, 4행) 그럼 2 To 4라고 쓰는 게 편한데, 왜 복잡하게 썼냐면 다음에 행이 변경될 경우(추가 또는 삭제)에는 변수로 처리하는 게 대응이 편합니다 ! 

For i = 2 To Sheets("Company").Cells(Rows.Count, "a").End(xlUp).Row
    company_lst = Sheets("Company").Range("a" & i).Value
Next

#06. 코드설명 

For i = 2 To Sheets("Company").Cells(Rows.Count, "a").End(xlUp).Row
    company_lst = Sheets("Company").Range("a" & i).Value

이제 company_lst에 Sheet2에 있는 A2행 ~ 4행까지 데이터가 들어가게 됩니다.

['유한회사 권', '(유) 신김박', '유한회사 김이박'] 데이터가 들어가 있습니다.

 

    Sheets("RawData").Range("a3").CurrentRegion.AutoFilter Field:=1, Criteria1:=company_lst

다음은 Sheets("RawData")는 첫 번째 시트 이름인 RawData로 이동하여 Range("a3") (= A3셀)을 선택합니다.

현재 데이터에서는 Company라고 되어 있는 부분이겠네요. 현재 기준으로 .CurrentRegion은 엑셀의 "Ctrl + A" 기능과 동일합니다.

RawData 시트의 테이블 전체가 블록지정 되어 있겠네요

 

.AutoFilter의 경우 엑셀의 필터기능이며 Field:=1은 첫 번째 열입니다. 

Criterial1:=company_lst의 경우 Company 필드를 필터 걸어서 Sheet2에 있는 '유한회사 권'을 가지고 오는 기능입니다.

 

    Sheets("RawData").Range("a3").CurrentRegion.Copy

 

'유한회사 권' 필터를 걸어둔 상태에서 .CurrentRegion은 다시 전체 선택을 하게되고 .Copy는 복사입니다("Ctrl + C" 기능)

 

    Workbooks.Add '새로운 엑셀파일을 엽니다
    ActiveWorkbook.Sheets(1).Paste '엑셀파일 1번 시트에 복사한 내용을 붙여넣기 합니다
    Columns("A:D").AutoFit ' A ~ D열까지 열너비를 조정합니다
    Range("A1").Select '시트1의 A1셀을 선택합니다
    ActiveWorkbook.SaveAs ThisWorkbook.Path & "\파일분리\" & company_lst '미리 만들어두었던 파일분리 폴더 내에 엑셀 파일 저장합니다
    ActiveWorkbook.Close '새로 만든 엑셀파일을 닫습니다

 

이제 필터 걸려있는 걸 풀어줍니다

	Sheets("RawData").Range("A3").CurrentRegion.AutoFilter

 

이제 폴더 내에 필터가 걸린 데이터가 잘 들어가 있음을 확인할 수 있습니다 ~ 

 

파일분리 자동화 GIF

 

 

완성된 매크로에서 시트 1번 범위와 시트2번만 수정하셔서 사용하시면 됩니다 ~ !

엑셀 매크로로 업무시간 단축하시길 바랍니다.