왜 범위 지정 오류?

1. 문제

다른시트에복사.xlsm

0.02MB

 
 
G2셀의 구분에 해당하는 값을 B열에서 찾아서 해당하는 A열에서 C열의 값을 i열부터 K열에 표시하려고 하는 것입니다.

 
 

가. Sheet1에서  '같은시트에복사' 매크로 실행

 
위 파일을 연 후 개발 도구 - 매크로를 누르고,

 
 
매크로 이름 목록에서 '같은시트에복사' 매크로를 클릭하고, 오른쪽 위 실행 버튼을 누르면

 
 
G2셀에 선택된 값과 동일한 구분에 해당하는 데이터만 i열부터 K열에 잘 복사됩니다.

 
 

나. Sheet1에서 '다른시트에복사' 매크로 실행

 
이번에는 Sheet1 시트가 선택된 상태에서 개발 도구 - 매크로를 누른 다음 '다른시트에복사' 매크로를 실행하고 Sheet2를 클릭해 보면

 
G2셀에 해당하는 값들이 잘 표시됩니다.

 
 

다. Sheet2에서 '다른시트에복사' 매크로 실행

 
그런데, Sheet2에서 '다른시트에복사' 매크로를 실행하면
'Range' 메서드('_Worksheet' 개체의)에서 오류가 발생하였습니다'란 에러 메시지가 표시됩니다.

 
이 상태에서 디버그 버튼을 누르면 
ws1.Range(Range("a" & i), Range("c" & i)).Copy ws2.Range("a" & j) 줄에서 멈추는데,

 
실행 메뉴에서 재설정을 누르거나,

 
표준 도구모음에서 재설정(중지) 아이콘을 눌러 매크로 실행을 끝냅니다.

 

2. 원인

'다른 시트에 복사' 매크로의 코드는 아래와 같습니다.

Sub 다른시트에복사()
    Dim i As Integer, j As Integer, endRow As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    endRow = ws1.Range("a1").End(xlDown).Row
    
    j = 2
    For i = 2 To endRow
        If ws1.Cells(i, "b") = ws1.Range("g2") Then
            ws1.Range(Range("a" & i), Range("c" & i)).Copy ws2.Range("a" & j)
            j = j + 1
        End If
    Next
End Sub

 
 
Sheet1에서 실행할 때는 문제없이 잘 되는데,  Sheet2에서 실행하면
ws1.Range(Range("a" & i), Range("c" & i)).Copy ws2.Range("a" & j)에서
 
맨 앞에서 Range가 ws1의 것을 지정하기 위해 ws1.을 앞에 붙였으므로 문제없을 듯한데
 
직접 실행 창에서 
?ws1.Range(Range("a" & i), Range("c" & i)).address
라고 입력해서 셀 주소(범위)가 어떻게 되는지 확인해보려고 했더니 여전히 '개체 정의 오류'가 발생합니다.

 
이유는 Range 앞에 WorkSheet명을 생략하면 ActiveSheet가 생략된 것으로 것으로 보기 때문입니다.
 
다시 말해 Sheet1에서 실행할 때는 맨 앞의 시트명이나 그 안의 시트명이나 모두 Sheet1이기 때문에 문제가 없으나,
 
Sheet2에서 실행할 때는 맨 앞의 Range 앞에는 ws1(Sheet1)을 지정했는데, 안쪽의 Range에는 시트명에는 시트명이 없어ActiveSheet인 Sheet2를 참고하게 되고, 따라서, 맨 앞의 Sheet1과 안쪽의 Sheet2가 짝이 맞지 않아서 에러가 발생하는 것입니다.
 
 

3. 해결

그렇다면 어떻게 해야 할까요?
 

가. 해법 1

맨 앞의 Range에 ws1을 붙인 것처럼, 괄호 안의 Range에도 모두 ws1을 붙이거나,
  → ws1.Range( ws1. Range("a" & i), ws1. Range("c" & i)).Copy ws2.Range("a" & j)
 
맨 앞의 ws1은 없애고, 괄호 안의 Range에만 ws1을 붙일 수 있습니다.
  → Range( ws1. Range("a" & i), ws1. Range("c" & i)).Copy ws2.Range("a" & j)
 
두번째 코드로 수정한 전체 코드는 아래와 같습니다.

Sub 다른시트에복사()
    Dim i As Integer, j As Integer, endRow As Integer
    Dim ws1 As Worksheet, ws2 As Worksheet
    
    Set ws1 = Sheets(1)
    Set ws2 = Sheets(2)
    endRow = ws1.Range("a1").End(xlDown).Row
    
    j = 2
    For i = 2 To endRow
        If ws1.Cells(i, "b") = ws1.Range("g2") Then
'            ws1.Range(Range("a" & i), Range("c" & i)).Copy ws2.Range("a" & j)
            Range(ws1.Range("a" & i), ws1.Range("c" & i)).Copy ws2.Range("a" & j)
            j = j + 1
        End If
    Next
End Sub

 
 
이제 엑셀로 돌아가서 Sheet2의 A2셀부터 C4셀까지 지우고,
'다른시트에복사' 매크로를 실행하면
에러 없이 잘 끝납니다.
 

나. 해법 2

Range안에 Range로 설정해서 문제가 되므로, 문자열로 연결해서 범위를 지정하면 WorkSheet를 맨 앞에 한 번만 지정해도 됩니다.
 
수정된 구문은 아래와 같이 됩니다.
ws1.Range("a" & i & ":" & "c" & i).Copy ws2.Range("a" & j)
 
A열 i행에서 C열 i행까지를 복사하는 것이고, 연속된 범위이므로 중간에 콜론을 사용해서
"a" & i & ":" & "c" & i
라고 입력한 것입니다.
 
엑셀로 돌아가서 A2셀부터 C4셀까지 지우고,
'다른시트에복사' 매크로를 실행하면
에러 없이 잘 끝납니다.

다른시트에복사(완성).xlsm

0.02MB