왜 범위 지정 오류?
1. 문제
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셀까지 지우고,
'다른시트에복사' 매크로를 실행하면
에러 없이 잘 끝납니다.