+ Reply to Thread
Results 1 to 7 of 7

Macro to combine multiple cells from multiple worksheets into one worksheet

  1. #1
    Registered User
    Join Date
    03-19-2013
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    4

    Macro to combine multiple cells from multiple worksheets into one worksheet

    Can someone help with a macro that will pull data from multiple similarly-formatted tabs and combine into one tab in a specified format? Note, the macro would be used within the file itself (on a new worksheet), but would be used for mulitple similar files. Each tab in each file would have a different 3-number name. The goal is to create a single list containing all of the rows containing data from all of the tabs within the file.

    Each of the source tabs/worksheets in the first file are titled "401", "410", "420", "430" "440", and "441". The macro should pull cell $C$5 into Column A (repeated for each relevant row from the worksheet); pull Cells D8:J8 into the destination sheet Column B thru G. This should be repeated for each row on the source sheet that has an account number (9-digit number) in column D. Note within possible rows on the source sheet, many of the rows of data (E-J) would only contain zeros. these rows would not need to be copied/returned to the destination sheet. That would be repeated for each tab within the file; and pasted below the pasted amounts copied from the tab before it. This would give me a single list containing all of the rows containing data from all of the tabs within the file.

    Can anyone help?

  2. #2
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to combine multiple cells from multiple worksheets into one worksheet

    Click GO ADVANCED and use the paperclip icon to post up sample workbooks. Show two source files and then the Master consolidated file that is created from those two sample file.

    From that, we can create a macro to get you from A to B, processing all the files in a specific folder into your master.

    ====================
    'WORKBOOKS TO 1 SHEET STACKED
    Here's a macro for collecting data from all files in a specific folder.The parts of the code that need to be edited are colored to draw your attention. This base macro gets you over half the way. Just need to customize it to apply an autofilter to your column A to show only rows with 9-digit number, then copy the visible rows to your master, repeating for every sheet in the opened workbooks.
    Last edited by JBeaucaire; 12-27-2019 at 10:11 PM.
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    ?None of us is as good as all of us? - Ray Kroc
    ?Actually, I *am* a rocket scientist.? - JB (little ones count!)

  3. #3
    Registered User
    Join Date
    03-19-2013
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Macro to combine multiple cells from multiple worksheets into one worksheet

    Thank you JBeaucaire. Attached is a sample workbook, with an example of the upload file I am trying to create from the numerous other tabs with example data. Note There are multiple files that I would hope to be able to use the macro for, but each file will have a different name, and, the worksheets within the files will be named differently.

    Thank you kindly,
    Attached Files Attached Files

  4. #4
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to combine multiple cells from multiple worksheets into one worksheet

    Give this a run:
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    03-19-2013
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Macro to combine multiple cells from multiple worksheets into one worksheet

    Thank you, I think this is very close. I noticed two issues: 1) it is not picking up accounts with all negative values, 2) it is not picking up accounts which total zero (for all six months) but may have an offsetting positive and negative values in one/more months.

    Also, can it be adjusted so the pasted dollar values are rounded to no more than two decimal places (i.e. 100.25 instead of 100.246777)

    Will I need to adjust the macro to use in similar formatted files, but with more/less tabs and tabs named differently?

  6. #6
    Forum Expert JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: Macro to combine multiple cells from multiple worksheets into one worksheet

    Maybe this is a better "formula" to test the ranges:
    Please Login or Register  to view this content.
    Add this at the top for the formatting:
    Please Login or Register  to view this content.
    The sheet names are not specifically used other than to make sure they aren't the same name as the new Output sheet. This should work on any file with any number of sheets formatted this way with data starting in row9.


    If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

  7. #7
    Registered User
    Join Date
    03-19-2013
    Location
    NJ
    MS-Off Ver
    Excel 2007
    Posts
    4

    Re: Macro to combine multiple cells from multiple worksheets into one worksheet

    I adjusted the two formatting lines and it works perfectly in the example file. THANK YOU!

    I tried a second time in another file and did not work, see second file attached. I'm not sure why, I believe the formatting is the same (i.e. data start on Row 7, etc...)?
    Attached Files Attached Files

+ 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