+ Reply to Thread
Results 1 to 11 of 11

Copy speciffic rows from workbook with VBA or Formula?

  1. #1
    Registered User
    Join Date
    10-14-2010
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Copy speciffic rows from workbook with VBA or Formula?

    hi all!

    I'm having some troubles with an excel thing and I can't get it figured out.. I'm affraid I might have to resort to VBA instead
    Here's the situation:
    sheet 1 (officially another workbook (1) but for convenience sake i made a demo sheet in the same workbook) holds ALL the data
    sheet 2 (officially workbook 2) should only hold the data from sheet 1 where a cell in a certain column holds a speciffic value.

    In the end people should only be able to look at workbook 2 so they can not look at the other data.

    The problem is that there are rows in between that are not applicable and if I use a formula I will have rows reading "#N/A" or something along those lines.

    Is there a way (formula or VBA) that can look op rows in a different workbook that hold a specific value in a specific cell in a specific column and copy certain cells from that row to a new workbook?


    I have enclosed a dummy/demo workbook so anyone who thinks he/she can help can take a look.

    Kind regards and thanks in advanced!

    Target_0.xls
    Attached Files Attached Files
    Last edited by little_ghost; 09-18-2012 at 09:50 AM.

  2. #2
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Formula or VBA script; I can't get it figured out :(

    does it need to be linked? if not you can simply filter then copy and paste
    Josie

    if at first you don't succeed try doing it the way your wife told you to

  3. #3
    Registered User
    Join Date
    10-14-2010
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Formula or VBA script; I can't get it figured out :(

    Yes., I'm affraid it does.
    The source file will be edited by different people/project leaders.
    The target file will be viewed by the "end user".

    This file (target) will have to get it's "up to date" data when it is opend by the end user.

  4. #4
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Formula or VBA script; I can't get it figured out :(

    I'd use a query using Data-Import external data and use ms query to extract the data you want from the source workbook-you can set up criteria in the query window. once you have it set up you can set the result table to refresh when the workbook is opened so the user doesn't have to do anything

  5. #5
    Registered User
    Join Date
    10-14-2010
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Copy speciffic rows from workbook with VBA or Formula?

    @JosephP: I'm affraid you've lost me

  6. #6
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Copy speciffic rows from workbook with VBA or Formula?

    are you using excel 2003?

  7. #7
    Registered User
    Join Date
    10-14-2010
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Copy speciffic rows from workbook with VBA or Formula?

    Haha, I just updated that in my profile to 2010... so, no, i'm using 2010.

  8. #8
    Forum Guru JosephP's Avatar
    Join Date
    03-27-2012
    Location
    Ut
    MS-Off Ver
    2003/10
    Posts
    7,328

    Re: Copy speciffic rows from workbook with VBA or Formula?

    -data tab, 'from other sources', from microsoft query
    -choose 'Excel files*' and OK
    -select your source file
    -if you get an error about no visible tables, click Options and check the 'system tables' option
    -add the columns you want
    -add any filters you want
    -add any sorting you want
    -select 'return data to microsoft excel' and press finish
    -choose the location for your table and click the properties button then check the option to refresh data on opening the workbook
    -done ;-)

  9. #9
    Forum Moderator Fotis1991's Avatar
    Join Date
    10-11-2011
    Location
    Athens(The homeland of the Democracy!). Greece
    MS-Off Ver
    Excel 1997!&2003 & 2007&2010
    Posts
    13,744

    Re: Copy speciffic rows from workbook with VBA or Formula?

    mAYBE SOMETHING LIKE THIS?
    Attached Files Attached Files
    Regards

    Fotis.

    -This is my Greek whisper to Europe.

    --Remember, saying thanks only takes a second or two. Click the little star * below, to give some Rep if you think an answer deserves it.

    Advanced Excel Techniques: http://excelxor.com/

    --KISS(Keep it simple Stupid)

    --Bring them back.

    ---See about Acropolis of Athens.

    --Visit Greece.

  10. #10
    Registered User
    Join Date
    10-14-2010
    Location
    NL
    MS-Off Ver
    Excel 2010
    Posts
    13

    Re: Copy speciffic rows from workbook with VBA or Formula?

    Quote Originally Posted by JosephP View Post
    -data tab, 'from other sources', from microsoft query
    -choose 'Excel files*' and OK
    -select your source file
    -if you get an error about no visible tables, click Options and check the 'system tables' option
    -add the columns you want
    -add any filters you want
    -add any sorting you want
    -select 'return data to microsoft excel' and press finish
    -choose the location for your table and click the properties button then check the option to refresh data on opening the workbook
    -done ;-)
    This worked after some fiddling.
    It had some problems with the source file being on the network. Wouldn't accept absolute paths and relative paths wouldn't work since most people have the drive mapped to a different letter.
    By fiddling around in the definition (connection string and command text) i managed to get it to work. Now you do get some kind of error when you click the link to the file but the file works.

    I tried Fotis1991's option also, and that obviously work aswell.
    If there are any problems with option 1, I can always resort to option 2.
    If that doesn't work.... I'll let you geniuses know!

    Thanks a million!!!!

  11. #11
    Forum Expert Cutter's Avatar
    Join Date
    05-24-2004
    Location
    Ontario,Canada
    MS-Off Ver
    Excel 2010
    Posts
    6,451

    Re: Copy speciffic rows from workbook with VBA or Formula?

    @ little_ghost

    Based on your last post it seems that you are satisfied with the solution(s) you've received but you haven't marked your thread as SOLVED. I'll do that for you now but please keep in mind for your future threads that Rule #9 requires you to do that yourself. If your problem has not been solved you can use Thread Tools (located above your first post) and choose "Mark this thread as unsolved".
    Thanks.

    Also, as an infrequent user of the forum, you may have forgotten (or may not be aware) that you can thank those who have helped you by clicking the small star icon located in the lower left corner of the post in which the help was given. By doing so you can add to the reputation(s) of those who helped.

+ 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