+ Reply to Thread
Results 1 to 13 of 13

Consolidate Multiple Sheets into Master

  1. #1
    Registered User
    Join Date
    11-17-2012
    Location
    Alberta
    MS-Off Ver
    Excel 2007
    Posts
    19

    Consolidate Multiple Sheets into Master

    Ok, I have a code that is working great, except for two things. I'd like to have the information on the Summary sheet start on Row 6, and have the names of the worksheets the info came from in Column A.

    Code is as follows

    Please Login or Register  to view this content.
    Also would like to have the Summary automatically update if I enter new data into the individual sheets, and to know, if I format the Summary sheet previously to running the macro, will it keep the formatting? (ex. hide columns etc)

    I hope this is enough information, and any help will be greatly appreciated.

    Thanks in advance!!

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Consolidate Multiple Sheets into Master

    This line is copying
    Please Login or Register  to view this content.
    in to first empty column "B" of summary sheet, which is probably "B1". If you wish the copy to start at Rows 6, the easiset option would be to fill in data(Suspect heading) in "B1-B5", so that the next empyt would become B6.

  3. #3
    Registered User
    Join Date
    11-17-2012
    Location
    Alberta
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Consolidate Multiple Sheets into Master

    Thank you, AB33!
    That's perfect

    Now I just need to figure out how to get the sheet names in column A, and I can probably clear all the data from "Summary" before re-running the macro to update, instead of having it go automatically.

    Thanks again!

  4. #4
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Consolidate Multiple Sheets into Master

    Yes, I am afraid. I had a discussion on this forum regarding automatic update. It is not shared by the most experienced users of VBA

    Please Login or Register  to view this content.

  5. #5
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Consolidate Multiple Sheets into Master

    Slightly improved code

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    11-17-2012
    Location
    Alberta
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Consolidate Multiple Sheets into Master

    Oh, you're a genius!

    Unfortunately it seems to be over-copying the sheet names, and is copying row 5 from some sheets. Would it be easier if I uploaded a copy of my WB for you to see what I'm trying to do?

  7. #7
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Consolidate Multiple Sheets into Master

    Yes, post the whole book

  8. #8
    Registered User
    Join Date
    11-17-2012
    Location
    Alberta
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Consolidate Multiple Sheets into Master

    Ok, here goes. What I'm looking for is: the corresponding client name only beside their information, only ranges A6:K(end) <- could be only one line on some clients, or up to 50 (or more) on others, not the column headers, and not the "Template" sheet. I hope this makes sense. I'm really sorry it's been so complicated, but I really do appreciate all your help!
    Attached Files Attached Files
    Last edited by megmer; 11-17-2012 at 03:37 PM.

  9. #9
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Consolidate Multiple Sheets into Master

    Try this one
    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AB33; 11-17-2012 at 04:24 PM.

  10. #10
    Registered User
    Join Date
    11-17-2012
    Location
    Alberta
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Consolidate Multiple Sheets into Master

    SO close! Now all I need is for it to not copy the 'empty' rows. I can't say enough how much I appreciate all your help. This would've taken me weeks to get this far :D

  11. #11
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Consolidate Multiple Sheets into Master

    Okay, There was a code which deletes a row if column "B" is empty, I removed it because I was not sure its purpose, I have now put it back. If you wish to delete a row if columns G, or F are zeros, you can change this line of code from "B" to either G, or F.
    If Cells(i, "B") = "" Then"

    Please Login or Register  to view this content.
    Attached Files Attached Files
    Last edited by AB33; 11-18-2012 at 07:38 AM.

  12. #12
    Registered User
    Join Date
    11-17-2012
    Location
    Alberta
    MS-Off Ver
    Excel 2007
    Posts
    19

    Re: Consolidate Multiple Sheets into Master

    Thank you SO So much, that is perfect!

  13. #13
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,445

    Re: Consolidate Multiple Sheets into Master

    You are welcome!
    Could you please close this thread as closed?

+ 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

X vBulletin 4.1.8 Debug Information

  • Page Generation 0.98333 seconds
  • Memory Usage 9,195KB
  • Queries Executed 15 (?)
More Information
Template Usage (33):
  • (1)SHOWTHREAD
  • (1)ad_footer_end
  • (1)ad_footer_start
  • (1)ad_global_above_footer
  • (1)ad_global_below_navbar
  • (1)ad_global_header1
  • (1)ad_global_header2
  • (1)ad_navbar_below
  • (1)ad_showthread_firstpost_sig
  • (1)ad_showthread_firstpost_start
  • (1)ad_thread_first_post_content
  • (1)ad_thread_last_post_content
  • (6)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (13)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (3)postbit_attachment
  • (13)postbit_legacy
  • (13)postbit_onlinestatus
  • (13)postbit_wrapper
  • (4)showthread_bookmarksite
  • (1)spacer_close
  • (1)spacer_open
  • (1)tagbit_wrapper
  • (1)vbseo_linkbackmenu_entry 

Phrase Groups Available (6):
  • global
  • inlinemod
  • postbit
  • posting
  • reputationlevel
  • showthread
Included Files (39):
  • ./vbseo.php
  • ./env.php
  • ./vbseo/includes/functions_vbseo.php
  • ./vbseo/includes/functions_vbseo_pre.php
  • ./vbseo/includes/functions_vbseo_url.php
  • ./vbseo/includes/functions_vbseo_createurl.php
  • ./vbseo/includes/functions_vbseo_db.php
  • ./vbseo/includes/functions_vbseo_vb.php
  • ./vbseo/includes/functions_vbseo_seo.php
  • ./vbseo/includes/functions_vbseo_misc.php
  • ./vbseo/includes/functions_vbseo_crr.php
  • ./vbseo/includes/functions_vbseo_cache.php
  • ./vbseo/includes/functions_vbseo_hook.php
  • ./vbseo/includes/functions_vbseo_startup.php
  • ./includes/config.php
  • ./showthread.php
  • ./global.php
  • ./includes/class_bootstrap.php
  • ./includes/init.php
  • ./includes/class_core.php
  • ./includes/functions.php
  • ./includes/class_friendly_url.php
  • ./includes/class_hook.php
  • ./includes/functions_cforum.php
  • ./includes/functions_facebook.php
  • ./includes/functions_bigthree.php
  • ./includes/class_postbit.php
  • ./includes/class_bbcode.php
  • ./includes/functions_reputation.php
  • ./includes/class_bootstrap_framework.php
  • ./vb/vb.php
  • ./vb/phrase.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php
  • ./includes/functions_notice.php 

Hooks Called (47):
  • init_startup
  • friendlyurl_resolve_class
  • database_pre_fetch_array
  • database_post_fetch_array
  • global_bootstrap_init_start
  • global_bootstrap_init_complete
  • cache_permissions
  • fetch_threadinfo_query
  • fetch_threadinfo
  • fetch_foruminfo
  • global_state_check
  • global_bootstrap_complete
  • global_start
  • style_fetch
  • global_setup_complete
  • showthread_start
  • showthread_getinfo
  • friendlyurl_redirect_canonical
  • showthread_post_start
  • showthread_query_postids
  • showthread_query
  • bbcode_fetch_tags
  • bbcode_create
  • showthread_postbit_create
  • postbit_factory
  • postbit_display_start
  • fetch_musername
  • cache_templates
  • template_register_var
  • parse_templates
  • notices_check_start
  • notices_noticebit
  • process_templates_complete
  • reputation_image
  • postbit_imicons
  • bbcode_parse_start
  • bbcode_parse_complete_precache
  • bbcode_parse_complete
  • postbit_display_complete
  • memberaction_dropdown
  • postbit_attachment
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1