+ Reply to Thread
Results 1 to 4 of 4

How to copy and paste formulas in large worksheets Excel 2007.

  1. #1
    Registered User
    Join Date
    03-30-2010
    Location
    Nairobi,Kenya
    MS-Off Ver
    Excel 2007
    Posts
    16

    How to copy and paste formulas in large worksheets Excel 2007.

    Hallo! I am using large volumes of data where I have to sum up related products in several worksheets.

    Please see the excel sheet called Áutosum’to understand my query.

    In worksheet called data_1, I have summed up all related products. I would like to copy and paste these sum formulas in worksheet data_2 such that in one single copy and paste action, all the related rows in data_2 will sum up like in data_1.

    How can I achieve this?

    Thanks.
    Attached Files Attached Files

  2. #2
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to copy and paste formulas in large worksheets Excel 2007.

    Your workbook has 5 worksheets, but none of them are called data_1 or data_2.
    Jeff
    | | |·| |·| |·| |·| | |:| | |·| |·|
    Read the rules
    Use code tags to [code]enclose your code![/code]

  3. #3
    Registered User
    Join Date
    03-30-2010
    Location
    Nairobi,Kenya
    MS-Off Ver
    Excel 2007
    Posts
    16

    Re: How to copy and paste formulas in large worksheets Excel 2007.

    Oh!Sorry.I uploaded the wrong file.Please find the attached file Autosum 2.

    The worksheets are called data1 and data2 respectively.

    Thanks.
    Attached Files Attached Files

  4. #4
    Administrator 6StringJazzer's Avatar
    Join Date
    01-27-2010
    Location
    Tysons Corner, VA, USA
    MS-Off Ver
    MS365 Family 64-bit
    Posts
    24,532

    Re: How to copy and paste formulas in large worksheets Excel 2007.

    OK, now I see what you want to do. Let me elaborate for the sake of the rest of our audience.

    This sheet has several columns of numbers, and the rows are grouped so that there are intermittent totals taken using the SUM function. Our user wants to copy just the SUM formulas and paste them into the corresponding places in the other sheet without touching the data.

    Excel does not have a built-in way to copy a noncontiguous range and paste it so that it overlays existing cells. If you highlight all the SUM formulas individually (by holding CTRL and clicking), you can copy them all. But when you paste, they will all be pasted contiguously, instead of in the positions that correspond to their positions on Data1.

    I have written a macro that can do this for you.

    Please Login or Register  to view this content.
    If you are not familiar with macros, do the following:
    1. In Excel, press ALT-F11. This will bring up the VBA development window; see the attached image.
    2. On the left you will see a folder list. Look for your Excel file.
    3. Underneath it you will see a list of the worksheets. Double-click on data2.
    4. Then on the right side of the screen, paste the code above.
    5. This is probably a one-time operation, so rather than put buttons on the worksheet, you can just position your cursor somewhere in the code, then press F5 to run the macro.
    Attached Images Attached Images

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1