VBA 유튜브 학습기 7 - 자동 복사, 붙여넣기의 모든 것(feat. 파워 문과 기획자)

youtu.be/Ir4SmzhGgdA

 

엑사남 님 강의 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. 값만 복사 - 붙여넣기

 

실무를 하다 보면 통째로 복사 - 붙여넣기보다는 값만 복사해 붙여넣는 걸 더 자주 쓰는 거 같아요.

저만 그런가요?

 

 

이 표는 K, L열에 전체적으로 수식이 적용돼 있었어요.

 

 

이렇게 엑셀 표 안에 수식이 적용될 때가 있잖아요.

수식은 됐고, 값만 가져다 쓰고 싶을 때.

그런 경우도 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 : 값만 붙여넣음

 

이런 의미입니다.

 

코드를 실행해 보면

 

K7 셀을 찍어 보니 수식은 사라지고 값만 남았습니다.

 

이렇게 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강 들어야겠네요.