+ Reply to Thread
Results 1 to 15 of 15

If cell has a value, copy entire row is appropriate sheet

  1. #1
    Registered User
    Join Date
    06-02-2014
    Posts
    17

    If cell has a value, copy entire row is appropriate sheet

    Hello.

    I'm new to this forum and I'm looking for help for establishing a working sheet. Hopefully I will be able to explain what am I looking for:

    The excell file is made of 1st sheet as a main one (to fill data in), 2nd as a base, and than followed by project number. In working sheet is only one project, called 374. It will be followed by other project (360,...)

    I want excel, when 1st sheet column D has a number 374 inside, that entire row is copied to sheet 374.

    How can I make this happen?

    I thank you for all the help in advance!

    Best Regards,

    Jure
    Attached Files Attached Files

  2. #2
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If cell has a value, copy entire row is appropriate sheet

    Maybe:

    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-02-2014
    Posts
    17

    Re: If cell has a value, copy entire row is appropriate sheet

    Thanks for help.

    I've pasted the code into Visual Basic. When I run it, it copies the 1st and 3rd row into appropriate sheet, but:

    When I re-run the macro it copies the 1st and 3rd line again (now is a double). I will refresh this excel daily and would like to be copied only once. So when I run the macro again it only updates sheet 374, 360,...

    Once again I than for the help!

  4. #4
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If cell has a value, copy entire row is appropriate sheet

    Maybe:

    Please Login or Register  to view this content.

  5. #5
    Registered User
    Join Date
    06-02-2014
    Posts
    17

    Re: If cell has a value, copy entire row is appropriate sheet

    When I run it it deletes the content?

    I mean, when I run macro, it deletes also content on 1st sheet and consequently nothing is copied on projects (374, 360,...).
    Last edited by koli99; 06-02-2014 at 08:31 AM.

  6. #6
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If cell has a value, copy entire row is appropriate sheet

    Quote Originally Posted by koli99 View Post
    Thanks for help.

    I've pasted the code into Visual Basic. When I run it, it copies the 1st and 3rd row into appropriate sheet, but:

    When I re-run the macro it copies the 1st and 3rd line again (now is a double). I will refresh this excel daily and would like to be copied only once. So when I run the macro again it only updates sheet 374, 360,...

    Once again I than for the help!
    When you refresh do you want the data in Sheets 374, and 360 cleared before pasting or check to see if the data contained has already been copied there?

  7. #7
    Registered User
    Join Date
    06-02-2014
    Posts
    17

    Re: If cell has a value, copy entire row is appropriate sheet

    It doesn't even matter if it cleared or checked (better if checked).

    I will input data on first page. Then I would like to run macro and 1st page will remain as it is, only working sheets 374, 360,... would be updated.

    Also another thing is what I noticed: If there is only working sheet 374, only 1st line is copied (even though the 3rd line is also under project 374). It seems that 2nd line (project 360) is interfering because there is no working sheet 360 created yet.
    Is it possible, that macro also skips lines if there is no working sheet 360 and continue writing project 374?

    Thanks!

  8. #8
    Registered User
    Join Date
    06-02-2014
    Posts
    17

    Re: If cell has a value, copy entire row is appropriate sheet

    Any additional help would be greatly appreciated...

  9. #9
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If cell has a value, copy entire row is appropriate sheet

    The macro shouldn't skip lines? When I tested it, it worked. It seems to be doing as you want? Is this the version you are using?

    Please Login or Register  to view this content.

  10. #10
    Registered User
    Join Date
    06-02-2014
    Posts
    17

    Re: If cell has a value, copy entire row is appropriate sheet

    Thanks for reply!

    Am I doing something wrong?

    I copy/paste your code into the .xls attached above:

    2.PNG

    and when I ran it:

    1.PNG

  11. #11
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If cell has a value, copy entire row is appropriate sheet

    Your screenshot doesn't have any values in Column D. Can you show a before? Again I tested it on your sample in Post 1 and it seems to be working?

  12. #12
    Registered User
    Join Date
    06-02-2014
    Posts
    17

    Re: If cell has a value, copy entire row is appropriate sheet

    That screenshot was taken after I ran the macro.

    I will do it again:
    Downloaded .xls from above, open Developer and pasted the code into Sheet1:
    1.PNG

    Pressed F5 and this is the result:
    2.PNG

    ?

  13. #13
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If cell has a value, copy entire row is appropriate sheet

    OK. Sorry. I see what you mean. I placed the macro in a Standard Module for you. Test the attached file to see if it does what you want.
    Attached Files Attached Files

  14. #14
    Registered User
    Join Date
    06-02-2014
    Posts
    17

    Re: If cell has a value, copy entire row is appropriate sheet

    Now it's working.

    This is absolutely what I was looking for.

    Much appreciated and hats off to you!

    Thanks again. Will marked it as solved and add reputation.

  15. #15
    Forum Expert
    Join Date
    06-12-2012
    Location
    Ridgefield Park, New Jersey
    MS-Off Ver
    Excel 2003,2007,2010
    Posts
    10,241

    Re: If cell has a value, copy entire row is appropriate sheet

    You're welcome. Glad to help out and thanks for the feedback. You had me fooled for a minute there.

+ 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. if cell is=z copy entire row to another sheet
    By tsioumiou in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 06-04-2014, 09:46 AM
  2. [SOLVED] Copy entire row in different sheet when cell has value
    By Morin in forum Excel Formulas & Functions
    Replies: 10
    Last Post: 04-11-2013, 07:37 AM
  3. Copy entire sheet, and name it according to a cell
    By gnoke in forum Excel Programming / VBA / Macros
    Replies: 10
    Last Post: 08-04-2010, 02:53 AM
  4. If Cell = X, Then Copy Entire Row Into A New Sheet-Help
    By AmandaBorthwick in forum Excel Formulas & Functions
    Replies: 3
    Last Post: 09-26-2009, 12:22 AM
  5. If Cell = X, Then Copy Entire Row Into A New Sheet
    By McNulty in forum Excel Programming / VBA / Macros
    Replies: 15
    Last Post: 05-04-2009, 11:50 PM

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