کپی کردن محدوده مجموعه از چندین کاربرگ به کاربرگ جدید در اکسل

Question

Om صدها کاربرگ با نام های متفاوت دارد که همه در یک کتاب کار یکسان هستند. ساختار هر کاربرگ اساساً یکسان است. او می خواهد یک محدوده ثابت (A146:O146) را از هر کاربرگ در یک کاربرگ جدید، یکی پس از دیگری کپی کند.

چند راه برای انجام این کار وجود دارد. برای پول من، یک ماکرو ساده بهترین گزینه است. با این حال، قبل از ورود به رویکرد مبتنی بر کلان، راهی وجود دارد که می توانید آن را با استفاده از فرمول ها انجام دهید. یک راه نسبتا منحصر به فرد بر تعریف یک فرمول در Name Manager بستگی دارد. برای شروع مراحل زیر را دنبال کنید:

شکل 1. کادر محاوره ای New Name.

=REPLACE(GET.WORKBOOK(1),1,FIND("]",GET.WORKBOOK(1),1),"")

1. تب Formulas روبان را نمایش دهید.
2. در گروه نام‌های تعریف شده، روی ابزار مدیریت نام کلیک کنید. اکسل کادر محاوره ای Name Manager را نمایش می دهد.
3. روی دکمه New کلیک کنید. اکسل کادر محاوره ای New Name را نمایش می دهد. (شکل 1 را ببینید.)
4. در قسمت Name، نام ListSheets را وارد کنید (توجه داشته باشید که این یک کلمه است، بدون فاصله).
5. در قسمت Refers To فرمول زیر را وارد کنید:
6. روی دکمه OK کلیک کنید تا ایجاد محدوده نامگذاری شده به پایان برسد. محدوده جدید باید در کادر محاوره ای Name Manager ظاهر شود.
7. روی دکمه Close کلیک کنید تا کادر گفتگوی Name Manager حذف شود.

با فرمولی که به این شکل تعریف شده است، می توانید به صفحه کاری بروید که می خواهید همه آن محدوده ها را در آن ادغام کنید. (بگذارید بگوییم که نام این کاربرگ "خلاصه" است.) من قویاً پیشنهاد می کنم مطمئن شوید که این کاربرگ آخرین مورد در کتاب کار شما باشد. در کاربرگ Summary، فرمول زیر را در ستون A هر ردیف قرار دهید:

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

هر چند ردیف برای نمایش همه کاربرگ ها لازم است فرمول را به پایین کپی کنید. به عبارت دیگر، اگر 25 کاربرگ دارید (بدون احتساب کاربرگ خلاصه)، فرمول را 24 ردیف پایین کپی کنید. با شمارش نسخه اصلی، اکنون باید فرمول را در مجموع 25 ردیف نشان دهید.

یک نکته جانبی در اینجا: فرمول ListSheets - فرمولی که در Name Manager تعریف کردید - آرایه ای از نام کاربرگ ها را برمی گرداند. تابع ROWS برای تعیین اینکه کدام عنصر از آن آرایه از طریق تابع INDEX برگردانده می شود استفاده می شود. اگر کاربرگ Summary آخرین مورد در کتاب کار شما نباشد، می‌توان آن را به راحتی توسط ListSheets برگرداند و در نهایت مقادیر را از آن استخراج خواهید کرد. این بدون شک کاری است که شما نمی خواهید انجام دهید، به همین دلیل است که من پیشنهاد کردم مطمئن شوید که Summary آخرین کاربرگ در کتاب کار است.

اکنون به سادگی فرمول ها را از ستون 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 dont 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 شروع می شود، کپی می شود.

رویکرد کلان سریع و آسان است. بعلاوه، اگر زمانی نیاز به انجام مجدد برگه خلاصه خود داشتید، کافی است برگه قبلی را حذف کنید و ماکرو را دوباره اجرا کنید.