+ Reply to Thread
Results 1 to 17 of 17

Copying data from a closed workbook into an open workbook ignoring excel filter?

  1. #1
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi All,

    I found a macro while googling and have tweaked it to my preference. It does the following actions

    1) Open file picker
    2) I select the source file I want to get data from
    3) data from 'sheet1' is copied into worksheet 'delivery' in my opened workbook
    4) macro ends

    What I have found is that, if someone has applied a filter to the source file, my macro will only copy the data that is visible (after filtering). How do I get the macro to copy ALL data?

    All help appreciated. Thanks!

    Reach


    My macro below:

    Please Login or Register  to view this content.
    Last edited by arlu1201; 09-10-2013 at 01:49 AM.

  2. #2
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi,

    Before
    Please Login or Register  to view this content.
    insert the line
    Please Login or Register  to view this content.
    Hope this helps

  3. #3
    Registered User
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,164

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Welcome to the forum.

    I have added code tags to your post. As per forum rule 3, you need to use them whenever you put any code in your post. Please add them in future. In order to put code tags, either type [CODE]Please [url=https://www.excelforum.com/login.php]Login or Register [/url] to view this content.[/CODE] at the end of it, OR you can highlight your code and click the # icon at the top of your post window.
    If I have helped, Don't forget to add to my reputation (click on the star below the post)
    Don't forget to mark threads as "Solved" (Thread Tools->Mark thread as Solved)
    Use code tags when posting your VBA code: [code] Your code here [/code]

  4. #4
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi ajryan88, thanks that worked.

    I now tried to automate another 2 worksheets from the source file where I wanted to merge the 2 worksheets into the open file

    Please Login or Register  to view this content.
    What I'm finding is that the header row of the 2nd worksheet is being copied as well (rightly so) and appended along with the data from the first worksheet. How do I delete the header row or not have the header row at all?

    ps: Thanks arlu1201, I'll add them from now on.

    Cheers,

    Reach

  5. #5
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    No problem!

    The simplest option here would be to delete the header row after the paste operation. So after
    Please Login or Register  to view this content.
    place this line of code:
    Please Login or Register  to view this content.
    Hope this helps

  6. #6
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Thanks ajryan88. however that bit of code deleted the header row of the first table. Here's my illustrated example of what I'm trying to achieve.

    macro_table_problem.jpg

  7. #7
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Aha whoops, I get it now

    Try changing
    Please Login or Register  to view this content.
    to
    Please Login or Register  to view this content.

  8. #8
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi ajryan88, that works a treat! But I found that for the 'NCD data' code block, the 'AutoFilterMode = False' line does not kick in. It seems to just copy the data that is filtered. That goes for sheets 2,3,4 in the code

    Please Login or Register  to view this content.
    You've been helping so much. I feel bad for asking more. Good thing is I'm learning but still have not gotten the hang of the syntax and semantics of this language yet...:D

  9. #9
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi again,

    That seems a bit strange that the AutoFilter isn't turned off for sheets 2, 3, and 4 but it works fine for sheet 1.

    Would it be at all possible for you to upload your workbooks (both the report workbook and the workbook that you are pasting the data into) so that I can take a look at why this is occurring?

    And there's no need to feel bad about it, that's what the forum is here for!

    Thanks

  10. #10
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi ajryan88, thanks for that. I've uploaded the files into google docs.

    macro file: https://docs.google.com/file/d/0BxMi...it?usp=sharing
    sample table data: https://docs.google.com/file/d/0BxMi...it?usp=sharing

  11. #11
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi,

    I have commented out some of your code and made a few slight changes, but the major change is tagged (with "AJRYAN88") at the beginning of the macro. I have done 3 things:
    1. The filters were table filters, not worksheet filters, so slightly different code was required to disable them
    2. Rather than opening and closing the workbook to copy each sheet, the workbook is opened at the beginning, all filters are removed, and then workbook is left open until the completion of the macro
    3. I have changed the copy mode of sheet 3 from copying the entire sheet to copying only the used range. This is because an error occurs if trying to copy the contents of an entire worksheet onto a cell that isn't A1.

    Hope this is working for you now
    Attached Files Attached Files

  12. #12
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi ajryan88, it's working!

    ta mate. Much appreciated.

  13. #13
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    No problems! Glad I could help

    Please don't forget to mark this thread as solved and please click on the * next to my post(s) to say thanks

  14. #14
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Reputation added, thanks :D

  15. #15
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi All,

    I think I've gone and killed the code again.

    This is for the 'NCD Data' part of the code, what it has gone and done is that it is now not only deleting the header row (which I want deleted) but also the first row of data of the second table I want to append to the first.

    Please Login or Register  to view this content.
    Also just for my understanding:

    Please Login or Register  to view this content.
    The code above copies only the 'used' rows and columns of a sheet starting from cell A2 right? Does this mean that for sheets(3) in the original code at the top, it does not copy the header row during the copy process thus eliminating the range for additional coding?

    AND

    Please Login or Register  to view this content.
    The code above, for the current activesheet defines the lastrow and adds 1 row to it (row below the last used row of the first table), then pastes the second table on that row. It then deletes that row (essentially removing the header).

    If my logic serves me right, this double elimination has deleted more rows than needed or is my understanding flawed?

    I tried changing

    Please Login or Register  to view this content.
    With

    Please Login or Register  to view this content.
    But it gave me a range class error.

  16. #16
    Valued Forum Contributor
    Join Date
    02-08-2012
    Location
    Newcastle, Australia
    MS-Off Ver
    Excel 2007 and Excel 2010
    Posts
    1,429

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Hi,

    Could you please re-upload your workbook with the newest changes in it.

    Thanks

  17. #17
    Registered User
    Join Date
    09-09-2013
    Location
    Australia
    MS-Off Ver
    Excel 2010
    Posts
    35

    Re: Copying data from a closed workbook into an open workbook ignoring excel filter?

    Managed to solve it by deleting the last 2 rows of code within the last With...End With block.

    Have found another bug. Will raise another query. :D

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. [SOLVED] Copying specific rows from an open workbook into the next row of a closed workbook.
    By Deimola in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 09-05-2012, 12:59 PM
  2. Copy data from closed workbook to open workbook
    By amandeepsharma89 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-27-2012, 11:20 AM
  3. VBA to copy data from closed workbook to open workbook
    By Anil2007 in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 07-27-2011, 12:30 PM
  4. VBA Copying data on closed sheet to open Workbook.
    By Mase123y in forum Excel Programming / VBA / Macros
    Replies: 13
    Last Post: 07-20-2009, 12:21 PM
  5. transfer data from open workbook to closed workbook
    By stevesunfold in forum Excel Programming / VBA / Macros
    Replies: 25
    Last Post: 12-23-2008, 08:30 AM

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.08639 seconds
  • Memory Usage 9,350KB
  • Queries Executed 16 (?)
More Information
Template Usage (35):
  • (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
  • (14)bbcode_code
  • (1)footer
  • (1)forumrules
  • (1)gobutton
  • (1)header
  • (1)headinclude
  • (1)headinclude_bottom
  • (17)memberaction_dropdown
  • (1)navbar
  • (4)navbar_link
  • (4)navbar_noticebit
  • (6)option
  • (1)postbit_attachment
  • (17)postbit_legacy
  • (17)postbit_onlinestatus
  • (17)postbit_wrapper
  • (4)showthread_bookmarksite
  • (5)showthread_similarthreadbit
  • (1)showthread_similarthreads
  • (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 (49):
  • 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
  • showthread_similarthread_query
  • showthread_similarthreadbit
  • forumrules
  • showthread_bookmarkbit
  • navbits
  • navbits_complete
  • showthread_complete


Search Engine Friendly URLs by vBSEO 3.6.0 RC 1