Om은 동일한 통합 문서에 서로 다른 이름의 수백 개의 워크 시트가 있습니다. 각 워크 시트의 구조는 기본적으로 동일합니다. 그는 고정 된 범위 (A146 : O146)를 각 워크 시트에서 새 워크 시트로 한 행씩 복사하려고합니다.

이 작업을 수행 할 수있는 몇 가지 방법이 있습니다. 내 돈으로는 간단한 매크로가 최선의 방법입니다. 그러나 매크로 기반 접근 방식을 시작하기 전에 수식을 사용하여 수행 할 수있는 방법이 있습니다. 다소 독특한 방법은 이름 관리자에서 공식을 정의하는 것입니다. 시작하려면 다음 단계를 따르십시오.

  1. 리본의 공식 탭을 표시합니다.

  2. 정의 된 이름 그룹에서 이름 관리자 도구를 클릭합니다. Excel은 이름 관리자 대화 상자를 표시합니다.

  3. 새로 만들기 버튼을 클릭합니다. Excel에 새 이름 대화 상자가 표시됩니다. (그림 1 참조)

  4. 이름 필드에 ListSheets라는 이름을 입력합니다 (공백이없는 단일 단어 임).

  5. 참조 대상 필드에 다음 공식을 입력하십시오.

  6. 확인 버튼을 클릭하여 명명 된 범위 만들기를 마칩니다. 이름 관리자 대화 상자에 새 범위가 나타납니다.

  7. 닫기 버튼을 클릭하여 이름 관리자 대화 상자를 닫습니다.

이러한 방식으로 정의 된 수식을 사용하여 모든 범위를 합산하려는 워크 시트로 이동할 수 있습니다. (이 워크 시트의 이름이 “요약”이라고 가정 해 보겠습니다.)이 워크 시트가 워크 북의 가장 마지막 워크 시트인지 확인하는 것이 좋습니다. 요약 워크 시트에서 행의 A 열에 다음 수식을 입력합니다.

=INDIRECT(INDEX(ListSheets,ROWS($A$1:$A1))&"!"&CELL("address",A$146))

공식을 복사하지만 모든 워크 시트를 나타내려면 많은 행이 필요합니다. 즉, 25 개의 워크 시트가있는 경우 (요약 워크 시트는 제외) 수식을 24 행 아래로 복사합니다. 원본을 계산하면 이제 수식이 총 25 개 행에 나타납니다.

참고 : ListSheets 수식 (이름 관리자에서 정의한 수식)은 워크 시트 이름의 배열을 반환합니다. ROWS 함수는 INDEX 함수를 통해 반환되는 배열의 요소를 결정하는 데 사용됩니다. 요약 워크 시트가 통합 문서의 마지막 워크 시트가 아닌 경우 ListSheets에서 쉽게 반환 할 수 있으며 결과적으로 값을 가져 오게됩니다. 이것은 의심 할 여지없이 수행하고 싶지 않은 작업이므로 요약이 통합 문서의 마지막 워크 시트인지 확인하는 것이 좋습니다.

이제 A 열에서 오른쪽으로 수식을 복사하여 O 열까지 모두 갖도록합니다. 그 결과 수식이 포함 된 셀에 A146 : O146의 값이 표시됩니다.

앞서 매크로 기반 접근 방식이 최선의 방법이라고 생각했습니다.

이것이 왜 그런지 보여주는 짧은 매크로입니다.

Sub CopyRange()

Dim w As Worksheet     Dim sRange As String     Dim lRow As Long

sNewName = "Summary" 'Name for summary worksheet     sRange = "A146:O146" 'Range to copy from each worksheet

Worksheets(1).Select     Worksheets.Add     ActiveSheet.Name = sNewName

lRow = 2     For Each w In Worksheets         If w.Name <> sNewName Then             'Comment out the following line if you don't want to             'include worksheet names in the summary sheet             Cells(lRow, 1) = w.Name             'If you commented out the previous line, make a change             'in the following line: change (lRow, 2) to (lRow, 1)

w.Range(sRange).Copy Cells(lRow, 2)

lRow = lRow + 1         End If     Next w End Sub

매크로 시작 부분에 설정되는 두 개의 변수 (sNewName 및 sRange)가 있습니다. 매크로로 만든 새 요약 워크 시트에 사용할 이름과 각 워크 시트에서 복사 할 셀 범위를 나타냅니다.

그런 다음 매크로는 통합 문서의 첫 번째 워크 시트를 활성화하고 요약에 사용할 새 워크 시트를 추가합니다. 이 워크 시트에는 sNewName 변수에 지정한 이름이 지정됩니다. 그런 다음 매크로는 다른 워크 시트를 각각 확인하는 루프를 단계별로 수행합니다. 워크 시트가 요약 워크 시트가 아닌 경우 워크 시트의 이름은 요약 시트의 A 열에 배치되고 sRange 변수 (A146 : O146)에 지정된 범위는 B 열에서 시작하는 요약 워크 시트에 복사됩니다. .

매크로 접근 방식은 빠르고 쉽습니다. 또한 요약 시트를 다시 실행해야하는 경우 이전 시트를 삭제하고 매크로를 다시 실행하면됩니다.

_ 참고 : _

이 페이지 (또는 ExcelTips 사이트의 다른 페이지)에 설명 된 매크로를 사용하는 방법을 알고 싶다면 유용한 정보가 포함 된 특별 페이지를 준비했습니다.

link : / excelribbon-ExcelTipsMacros [새 브라우저 탭에서 특별 페이지를 열려면 여기를 클릭하세요].

_ExcelTips_는 비용 효율적인 Microsoft Excel 교육을위한 소스입니다.

이 팁 (9812)은 Office 365의 Microsoft Excel 2007, 2010, 2013, 2016, 2019 및 Excel에 적용됩니다.