VBA 유튜브 학습기 7 - 자동 복사, 붙여넣기의 모든 것(feat. 파워 문과 기획자)
엑사남 님 강의 7강 듣고 왔습니다.
저번주 금요일에 6강 들은 후에 주말 팽팽 놀고 돌아오니까 사실 앞에 내용이 기억이 잘 안 나요.....😂
그래서 진도 나가는 걸 쉬고 오늘은 복습을 해야 하나 생각했는데...
일단은 다 듣고, 다시 듣더라도 목표한 진도는 나가 보기로 합니다.
잘하는 짓이겠죠?
오늘 배운 내용은 복사, 붙여넣기에 대한 건데요.
Ctrl + C, Ctrl + V로만 살아온 인생.
이제 저도 VBA 코드를 작성해서 그걸로 뭔가를 해 볼 수 있는 날이 오나 봐요. 🤩
예제1. 텍스트, 수식, 서식 모두 복사 - 붙여넣기
위 이미지의 F6 : L18까지의 표를 텍스트, 수식, 서식을 모두 통째로 복사해다가 O6 셀에 붙여넣을 거예요.
범위 지정해서 컨트롤 + C, V로도 가능하겠지만 조금 더 뭔가 있어 보이게 처리해 보는 거죠. ㅋㅋㅋ
Sub Test01()
Range("F6").CurrentRegion.Copy
Range("O6").PasteSpecial
End Sub
코드는 이렇습니다.
Range("F6").CurrentRegion.Copy 'F6셀을 시작으로 이어져 있는 모든 셀을 복사합니다.
* 셀.CurrentRegion : 해당 셀부터 이어져 있는 전체 범위
Range("O6").PasteSpecial 'O6셀에 복사한 것을 그대로 붙여넣습니다.
그리고 실행해 보면
이렇게 표가 그대로 복사돼 O6셀부터 붙여넣기 된 걸 볼 수 있는데요.
거슬리는 게 있죠?
복사한 표(F6 : L18) 테두리가 "여기 복사됐다." 라고 광고하고 있잖아요.
녹색 점선 테두리가 반짝이는 걸 전 몹시 거슬려 하고 있었는데요.
엑사남 선생님 완전 쪽집게.
바로 말씀해 주시더라고요.
코드에 무언가를 추가하면 저 반짝이는 테두리가 나타나지 않게 복사 - 붙여넣기가 가능하답니다.
방법을 알아보면,
Sub Test01()
Range("F6").CurrentRegion.Copy
Range("O6").PasteSpecial
Range("F6").Select
Application.Cutcopymode = false
End Sub
Range("F6").Select 'F6셀을 선택합니다.
Application.Cutcopymode = false '복사 모드를 취소합니다.
이 코드들을 추가해 주면 된다고 하네요.
코드를 추가하고 다시 실행해 보면
이렇게!
"여기를 복사했다!" 그런 표시 없이 복사 - 붙여넣기가 완료됐죠.
예제2. 값만 복사 - 붙여넣기
실무를 하다 보면 통째로 복사 - 붙여넣기보다는 값만 복사해 붙여넣는 걸 더 자주 쓰는 거 같아요.
저만 그런가요?
이렇게 엑셀 표 안에 수식이 적용될 때가 있잖아요.
수식은 됐고, 값만 가져다 쓰고 싶을 때.
그런 경우도 VBA 코드로 해결할 수 있습니다.
코드를 보면요.
Sub Test02()
Range("F6").CurrentRegion.Copy
Range("F6").PasteSpecial xlPasteValues
Range("F6").Select
Application.CutCopyMode = False
End Sub
나머지 부분은 예제1과 동일한데
붙여넣기 부분만 코드가 좀 바뀌었습니다.
Range("F6").PasteSpecial xlPasteValues 'F6 셀에 복사한 걸 붙여넣는데, 값(Valuse)만 붙여 넣어라.
* 셀.PasteSpecial xlPasteValues : 값만 붙여넣음
이런 의미입니다.
코드를 실행해 보면
이렇게 K열에 있던 수식이 사라지고, 값만 붙여넣어진 걸 확인할 수 있습니다.
예제3. 수식만 복사 - 붙여넣기
이번엔 반대로.
서식은 그대로 두고, 수식만 복사해 붙여넣고 싶을 때의 해결 방법인데요.
K열, 합계를 구하는 데 사용할 수식은 위 이미지처럼 K3 셀에 미리 작성을 해 놓고요.
L열, 평균을 구하는 데 사용할 수식은 위 이미지처럼 L3 셀에 미리 작성을 해 놨어요.
그럼 이제 각각 K3 셀과 L3셀의 수식을 복사 - 붙여넣기를 데이터가 있는 행의 개수만큼 반복하면 되겠죠?
그러기 위해선 데이터가 있는 행의 개수(데이가 입력된 마지막 행 번호)를 먼저 파악해야 합니다.
전에 배웠던 COUNTA 함수를 이용해 데이터가 입력된 행의 개수를 구해 봅니다. (위 이미지 B2 셀 수식 참고)
=COUNTA(F:F) + 5 'F열 전체에 텍스트가 입력돼 있는 셀의 개수를 구하고, 그 값에 5를 더해라.
* 5를 더하는 이유는 위에 셀 서식을 맞추기 위해(?) 비워두 1~5행이 있기 때문이에요.
기본 세팅을 해 두고 코드를 작성해 보면
Sub Test03()
Range("K3:L3").Copy
Range("k7:L" & Range("B2")).PasteSpecial xlPasteFormulas
End Sub
Range("K3:L3").Copy 'K3에서 L3까지의 범위를 복사해라.
Range("K7:L" & Range("B2")).PasteSpecial xlPasteFormulas
'복사한 것을 K7셀부터 L열까지 붙여넣는데 몇 행까지냐면 "B2"에 표기된 행까지 붙여넣는다. 붙여넣을 땐 수식(xlPasteFormulas)만 붙여넣어라.
* B2 셀엔 위에 보시면 아시겠지만, COUNTA 함수를 이용해 데이터가 입력된 셀의 개수를 카운트해 놓았습니다.
코드를 실행해 보겠습니다.
🤩👏
서식은 유지되면서 잘 붙여넣어진 거 같은데.
한번 확인해 보겠습니다.
K11 셀(랜덤으로 그냥 찍은 셀이에요.)을 확인해 보니, 위의 이미지처럼 수식이 아주 잘 복사됐습니다.
예제4. 값과 수식은 유지한 채로 서식만 복사 - 붙여넣기
이번엔 이미 입력돼 있는 데이터 값과 수식들은 그대로 유지하면서 서식만 복사해 붙여넣는 방법입니다.
저 같은 서식 디자인 집작 덕후에데 딱 맞는 팁이죠.
제멋대로 복사해다 붙여넣어서 테두리 사라지고 막 정렬 안 맞고 그런 거 너무 싫거든요.
이렇게 위의 이미지처럼 K열과 L열에는 합계와 평균을 구하기 위한 수식이 적용돼 있습니다.
이 수식을 유지하면서 F3 : L3 범위에 있는 서식만 복사해다가
F7 : L18 범위의 표에 적용하는 방법입니다.
마찬가지로 먼저, 데이터가 있는 행의 개수(데이가 입력된 마지막 행 번호)를 먼저 파악해야 합니다.
이젠 익숙해질 만도 한 COUNTA 함수를 이용해 데이터가 입력된 행의 개수를 구해 봅니다. (위 이미지 B2 셀 수식 참고)
=COUNTA(F:F) + 5 'F열 전체에 텍스트가 입력돼 있는 셀의 개수를 구하고, 그 값에 5를 더해라.
* 5를 더하는 이유는 위에 셀 서식을 맞추기 위해(?) 비워두 1~5행이 있기 때문이에요.
기본 세팅을 해 두고 코드를 작성해 보면
Sub Test04()
Range("F3:L3").Copy
Range("F7:L" & Range("B2")).PasteSpecial xlPasteFormats
End Sub
Range("F3:L3").Copy 'F3:L3 범위를 복사하라.
Range("F7:L" & Range("B2")).PasteSpecial xlPasteFormats
'복사한 걸 F7 셀부터 L열의(Range("F7:L") 행은 B2셀에 표기된 행만큼의 범위(& Range("B2"))에 붙여넣을 건데(PasteSpecial), 어떻게 붙여 넣느냐? 서식만 붙여넣어라.(xlPasteFormats)
이렇게 코드를 작성하고 실행해 봅니다.
일단 이렇게 서식은 잘 붙여넣기가 된 거 같습니다.
캡처는 깜빡 잊고 못했는데.... 기존에 수식이 입력돼 있던 L열의 아무 셀이나 찍어 보면 수식도 잘 살아 있습니다.
여기까지가 7강 학습한 내용이고요.
7강은 과제가 있었어요.
[과제]
E6:K18 범위의 표에서 성별이 남자이면 E2:I2 영역의 서식과 J2:K2 영역의 서식 + 수식을 적용하고,
성별이 여자이면 E3:I3 영역의 서식과 J3:K3 영역의 서식 + 수식을 적용하라. (1,000,000,000점😅)
어려워.........
전에 배웠던 For문과 If문을 활용하면 된다고 말씀해 주시더라고요.
감은 오지만 약간 막막했어요.
머리로만 생각해선 답이 안 나올 거 같으니 일단 시작해 봅니다.
우선 데이터가 입력된 표의 마지막 행 번호를 구해야겠네요.
앞에서 배운 대로 COUNTA 함수를 이용해 데이터가 입력된 마지막 행 번호를 구해 봅니다. (위 이미지 B2 셀)
=COUNTA(E:E)+5
여기까지 하고 한참의 시간이 흘렀습니다.🤣
시험이었다면 떨어졌을 거예요....ㅋㅋㅋ
Dim ..... 뭐뭐가 필요하지?
이렇게 삽질을 하다가 깨달음을 얻었습니다!
For문부터 작성....
중간중간 변수가 필요해지면 그때 Dim 변수 설정을 하기로 하고 작성하기 시작하니까 그래도 좀 감이 오더라고요.
최종 코드는 이렇습니다!
다른 분들 과제 해서 올리신 거 보니까 방법이 다양하더라고요.
어떤 분 코드는 제게 너무 고난도라 이해가 안 가기도 하는데... 우선 이렇게 초보적 마인드로 작성한 코드도 되긴 됩니다....^^;;;
Sub homework()
Dim i As Long
For i = 7 To Range("B2")
If Range("F" & i) = Range("D2") Then
Range("E2 : I2").Copy
Range("E" & i).PasteSpecial xlPasteFormats
Range("J2 : K2").Copy
Range("J" & i).PasteSpecial
Else
Range("E3 : I3").Copy
Range("E" & i).PasteSpecial xlPasteFormats
Range("J3 : K3").Copy
Range("J" & i).PasteSpecial
End If
Next i
Range("E6").Select
Application.CutCopyMode = False
End Sub
For i = 7 To Range("B2") 'i는 7(행)부터 B2 셀에 표기된 숫자(행)만큼 반복할 거예요.
If Range("F" & i) = Range("D2") Then 'F열의 i행이 D2 셀과 같다면(즉, 셀 데이터가 "남"이라면)
Range("E2 : I2").Copy 'E2:I2 범위를 복사해라.
Range("E" & i).PasteSpecial xlPasteFormats '복사한 값을 E열의 i행에 서식만 붙여넣어라.
Range("J2 : K2").Copy 'J2:K2 범위를 복사해라.
Range("J" & i).PasteSpecial '복사한 값을 J열의 i행에 서식, 수식을 모두 붙여넣어라.
Else '만약 F열 i행이 D셀과 다르다면(즉, 셀 데이터가 "남"이 아니라면)
Range("E3 : I3").Copy 'E3:i3 범위를 복사해라.
Range("E" & i).PasteSpecial xlPasteFormats '복사한 값을 E열의 i행에 서식만 붙여넣어라.
Range("J3 : K3").Copy 'J3:K3 범위를 복사해라.
Range("J" & i).PasteSpecial '복사한 값을 J열의 i행에 서식, 수식을 모두 붙여넣어라.
쓰긴 썼는데(사실 중간중간 막혀서 컨닝도 조금 했어요....ㅋㅋㅋㅋ)
될까 싶었습니다.
그렇게 자신 없게 일단 실행해 봅니다.
되네요. 아주 잘!
100% 제 힘으로 과제를 해내진 못해서 좀 찝찝한데...
나중에 다시 백지 위에 해 보려고요.🙂
파워 문과의 머리로 여기까지 이해한 것만 해도 전 제 스스로가 자랑스럽거든요 ㅋㅋㅋ
이제 8강 들어야겠네요.