[엑셀/VBA] 원하는 시트를 다른 엑셀 파일로 복사하기

 

  엑셀 VBA 중 원하는 시트를 다른 엑셀 파일로 복사하는 방법을 포스팅할 것이다. 시트를 이동 시키는 경우는 여러가지가 있겠지만 대표적으로 많이 쓰일 것같은 방식은 아래 2가지이다.

 

-. 현재 실행중인 파일의 특정 시트를 새로운 엑셀파일로 이동시키기

-. 현재 실행중인 파일에서 다른 엑셀 파일의 시트를 읽어오기

 

  이 포스팅에서는 현재 실행중인 파일의 특정 시트를 새로운 엑셀파일을 생성하여 이동/복사시키는 방법을 다룬다.

 

 

1. 시트 이동 및 복사 메서드 : Copy, Move 사용하기

 

 

  엑셀 VBA에서는 엑셀의 시트를 복사하거나 이동시키는 메서드를 제공한다. Copy와 Move이다. 아래와 같이 예시를 통해 사용법을 알아보자.

 

 

test.xlsm

 

  먼저 가상의 매크로 파일을 만들어보자. "test.xlsm"이라는 매크로 파일이며, 해당 파일에는 위 이미지처럼 2개의 시트가 있다. 시트명은 "투손플레이스", "TwoHandasPlace"이다.

 

 

<VBA 개발 프로젝트 창>

 

 

   Alt+F11을 눌러 엑셀 VBA 개발 창을 열어보자. 그럼 프로젝트 메뉴에 위 시트명과 함께 Sheet1, Sheet2라는 번호도 확인할 수 있다. 이건 엑셀의 기본적인 특징인데 어떤 시트명을 가지든 고유의 시트 번호를 가지게 되어있다. VBA에서 시트에 접근할 땐 이 시트번호 또는 시트명으로 접근할 수 있다.

 

  Copy, Move 메서드를 설명하기 전에 가상의 코드를 아래처럼 작성하였다.

 

 

<전체 코드>

Sub moveSheet()

    #wb라는 이름을 가진 Workbook 객체 생성
    Dim wb As Workbook
    
    #새로운 엑셀 생성(통합문서)
    Set wb = Application.Workbooks.Add
    
    #지금 실행하는 워크북 시트 중 시트번호 2번 복사(새로운 엑셀 파일의 sheet1 오른쪽에 복사)
    ThisWorkbook.Worksheets(2).Copy After:=wb.Worksheets(1)
  
    #새로 생성한 엑셀파일에 sheet1 삭제
    wb.Sheets("sheet1").Delete
    
    #절대경로로 지정하여 result.xlsx로 저장
    wb.SaveAs ("C:\Users/Desktop\VBA CODE\result.xlsx")
       
    #닫기
    wb.Close
    

End Sub

 

 

  위 코드는 엑셀파일의 Sheet2 ("TwoHandsPlace")라는 시트를 새로운 파일에 복사하여 result.xlsx라는 엑셀파일로 저장한다. 위 코드 그대로 실행시켜 결과를 확인해보자. 아래 이미지에서 확인할 수 있지만 코드를 돌리면 " result.xlsx"라는 파일이 생성된 것을 확인할 수 있다.

 

<결과>

<매크로 실행 과정 - 클릭시 확인 가능>

 

  result.xlsx를 실행시키면 아래처럼 TwoHadnsPlace 시트만 그대로 복사된 것을 확인할 수 있다.

 

result.xlsx

 

 

위 코드의 가장 핵심은 아래 부분이다. 코드를 각각 세분화해서 설명해보도록 하겠다.

 

 

<코드>

 #지금 실행하는 워크북 시트 중 시트번호 2번 복사(새로운 엑셀 파일의 sheet1 오른쪽에 복사)
 ThisWorkbook.Worksheets(2).Copy After:=wb.Worksheets(1)

 

 

-. ThisWorkbook :

  현재 매크로 파일(실행 중인 파일)을 의미한다. 방법에 따라서는 다른 엑셀파일을 열어서 사용하는 방법도 있다. 이 방법은 공부해서 다른 포스팅에서 정리해보도록 하겠다.

 

-. Worksheets(2) :

  위에서 언급했던 시트번호를 의미한다. 즉, Sheet2를 의미한다. 이 부분을 시트명으로 접근하고 싶다면 sheets( )로 대체할 수 있다. 해당 방식으로 표현해보면 sheets("TwoHandsPlace)이다.

 

-.  Copy After := wb.Worksheets(1)

  Copy After는 말그대로 뒤에 복사한다는 의미이다. Copy before일 경우 앞에 복사한다. wb라는 워크북의 Sheet1를 기준으로 앞,뒤로 복사한다는 의미이다. 만약, 복사가 아니라 시트를 이동시키고 싶다면 Move After, Move before를 사용하면 된다. 위에서 언급했지만 Worksheets(1)은 sheets("시트명")으로 대체할 수 있다.

 

 

  그래서 위 내용을 정리해서 여러가지 방식으로 구문을 표현해보자.

 

 

<코드>

#이동시킬 엑셀파일의 3번시트 오른쪽에 현재 Sheet2를 복사할 것
ThisWorkbook.Worksheets(2).Copy After:=wb.Worksheets(3)

#이동시킬 엑셀파일의 1번시트 왼쪽에 현재 "투손플레이스" 시트를 복사할 것
ThisWorkbook.Sheets("투손플레이스").Copy before:=wb.Worksheets(1)

#이동시킬 엑셀파일의 "연습시트"라는 시트 오른쪽에 "TwoHandsPlace" 시트를 이동시킬 것
ThisWorkbook.Sheets("TwoHandsPlace").Move After:=wb.Sheets("연습시트")

  

 

  마지막으로 아래 Sheet1을 삭제하는 부분에 대해서 설명해보도록 하겠다.

 

 

<코드>

#새로 생성한 엑셀파일에 sheet1 삭제
wb.Sheets("sheet1").Delete

 

  사실 생락해도 상관없는 부분이지만 엑셀 Workbook을 새로 생성하면 "Sheet1"이라는 시트가 새로 생성되어있다. 해당 시트를 삭제하려면 위 구문을 넣어주면 된다.

 

새로운 액셀파일 생성시 Sheet1 생김

 

 

 

 

2. 응용 : 엑셀파일의 모든 시트 이동/복사하기

 

 

  위 내용을 활용해 한가지 더 응용코드를 짜보았다. 1번 예시의 경우 총 시트가 2개이다. 근데 만약 시트의 수가 많아서 한번에 이동/복사하고 싶거나 특정 시트만 이동시키는 방법을 쓰려면 여러가지 방법을 고안할 수 있다. 보통 시트수가 많은 경우에는 for 반복문을 사용하면 된다.

 

  아래 코드는 엑셀파일의 모든 시트의 수를 count하고 시트 수만큼 for 문 실행하여 모두 복사하는 매크로이다.

 

 

<전체 코드>

Sub moveAllSheet()
	#wb라는 이름을 가진 Workbook 객체 생성
    Dim wb As Workbook 

    #현재 실행중인 엑셀파일의 시트 수 세기
    ws_count = ThisWorkbook.Worksheets.Count
    
    #새로운 엑셀 파일 생성(통합문서)
    Set wb = Application.Workbooks.Add
    
    #위 시트 수 ws_count 활용하여 for 구문 실행
    #시트 No로 접근하여 모두 복사
    For no = 1 To ws_count
        ThisWorkbook.Worksheets(no).Copy AFter:=wb.Worksheets(1)
    Next no
    
    #시트1 삭제
    wb.Sheets("sheet1").Delete
    wb.SaveAs ("C:\Users\VBA CODE\result.xlsx")
    
    #워크북 닫기
    wb.Close
    
End Sub

 

 

  위 코드를 실행시키면 모든 시트가 복사된다. 주석에 자세한 내용을 달아놨기 때문에 추가 설명은 생략한다.

 

 

모든 시트 복사 - result.xlsx

 

 

---

참고링크1 : 

엑셀 시트 이름 가져오기

참고링크2 : 

VBA(엑셀 매크로)시트 이동, 복사