+ Reply to Thread
Results 1 to 6 of 6

Copy and paste non consecutive cells and paste consecutively in another workbook

  1. #1
    Registered User
    Join Date
    09-05-2011
    Location
    Wakefield, England
    MS-Off Ver
    Excel 2010
    Posts
    63

    Copy and paste non consecutive cells and paste consecutively in another workbook

    Hi guys

    I have 2 excel databases. Database 1 is a data collection workbook and Database 2 is to keep consecutive records of data collected in Database 1.

    Here's what happens. I have created an input sheet in Database 1 where people can enter specific data. Due to design the data is entered into cells that are not consecustive i.e. first cell is F6, followed by F8, F10, F12, F14, F16, F19, G19, H19, F22, H22, F25, H25 and F27. Most of these cells are merged cells as well.

    Once data is entered they click a submit button. The macro I have recorded does the following actions:

    opens up Database 2 and selects sheet 1
    copies each piece of data one at a time from Database 1 and copies it in a row starting at row 2 on Database 2
    Once each cell is copied and pasted a new blank row is inserted on Database 2
    closes and saves database 2
    Returns to the input sheet on database 1.

    However, this macro is really slow. Can anyone think of a quicker way to acheive this? All help greatly appreciated.

    Regards

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

    Re: Copy and paste non consecutive cells and paste consecutively in another workbook

    Post up your macro. Don't forget to put it in between code tags as shown in my signature.

  3. #3
    Registered User
    Join Date
    09-05-2011
    Location
    Wakefield, England
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Copy and paste non consecutive cells and paste consecutively in another workbook

    Hi JBeaucaire

    As requested. I'm new to all this so hope the below is entered correctly. Thanks for a super quick response :-)

    Please Login or Register  to view this content.
    Regards

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

    Re: Copy and paste non consecutive cells and paste consecutively in another workbook

    Probably would've helped to see these workbooks, too. Anyway, in this macro, edit the line that refers to "thisworkbook" to list the correct sheet name where the Submit information is coming from.

    In your database, remove all the "merged" columns, they're unnecessary. The values will be placed into individual cells as shown in the macro. I also removed the formatting code. Go ahead and apply the formatting you want in the database to the columns A:K so they already have the formatting you want. The macro will add the new values to the bottom of the existing data each time.
    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    09-05-2011
    Location
    Wakefield, England
    MS-Off Ver
    Excel 2010
    Posts
    63

    Re: Copy and paste non consecutive cells and paste consecutively in another workbook

    Hi there

    Wow you are a genius. It worked a treat. Amazing. Thank you for taking the time to look at this for me.

    Regards

    MacQuhele

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

    Re: Copy and paste non consecutive cells and paste consecutively in another workbook

    If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.

+ 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.06501 seconds
  • Memory Usage 8,978KB
  • Queries Executed 14 (?)
More Information
Template Usage (32):
  • (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
  • (2)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (6)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (6)postbit_legacy
  • (6)postbit_onlinestatus
  • (6)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
  • ./includes/functions_notice.php
  • ./packages/vbattach/attach.php
  • ./vb/types.php
  • ./vb/cache.php
  • ./vb/cache/db.php
  • ./vb/cache/observer/db.php
  • ./vb/cache/observer.php 

Hooks Called (46):
  • 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
  • tag_fetchbit_complete
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1