+ Reply to Thread
Results 1 to 5 of 5

creating summary table from selected rows of multiple worksheets

  1. #1
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    creating summary table from selected rows of multiple worksheets

    I've attached a very trimmed-down example of a large workbook. In order to get the file size small enough to upload to the forum, I eliminated much data and many formulas, leaving enough values and tables to illustrate my question.

    In the "real" workbook there are 10+ worksheets, each with large tables, some having as many as 3,000 rows. Each row in these tables contains many formulas. The rows are sorted by date, so I cannot re-sort them, as it would corrupt some of the forumulas in the rows. (for example, some values in row 3 are dependent on values in rows 1 through 50)

    I want to reassemble the data from these worksheets into a summary table (in worksheet titled "summary." The specified rows I want to copy from each worksheet can be identified by looking at the values in column AK. If that value is greater than 0, I want to include data from that row in the summary worksheet.

    Once the new summary table is created I intend on performing additional analysis of the data, including perhaps inclusion of new formula columns. (I mention this because I haven't figured out how create new formulas after having created pivot tables or arrays).

    Thanks.
    Attached Files Attached Files
    Last edited by jrtaylor; 06-07-2009 at 01:24 PM. Reason: typo

  2. #2
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    764

    Re: creating summary table from selected rows of multiple worksheets

    Hi
    try the macro in the attached file
    Ravi
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: creating summary table from selected rows of multiple worksheets

    Ravishankar,

    Thank you, this works! I looked at the macro, trying to understand it. How would I make the first row of the report begin on row 70, rather than row 4?

    I've never written a macro before but it seems that you've set up this one to run based on a hyperlink from an object on the worksheet?

    Finally, what if I wanted to import only certain columns from the worksheets? Does that require a minor modification, or an extensive rewrite of the macro?

    thanks again

  4. #4
    Forum Contributor
    Join Date
    02-27-2008
    Posts
    764

    Re: creating summary table from selected rows of multiple worksheets

    Hi
    Press Alt F11 to open the macro window. change d= 4 to d= 70. If you tell me which are the columns to be imported, I can help by modifying the codes
    Ravi

  5. #5
    Forum Contributor
    Join Date
    12-03-2008
    Location
    Key West, FL
    MS-Off Ver
    365 Apps for Enterprise
    Posts
    665

    Re: creating summary table from selected rows of multiple worksheets

    Ravi, thank you so much. I will try to study your coding so that it works on my latest worksheets. If I can't figure it out I'll post a new example here.

    again, thank you for this.

    Walter

+ 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