+ Reply to Thread
Results 1 to 18 of 18

If value found, copy range

  1. #1
    Registered User
    Join Date
    06-17-2010
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    13

    If value found, copy range

    Hello

    Can anyone help with this as I new to Excel VBA .

    What I need is Macro that will look though my ‘sheet1’, check column ‘M’ starting on row ’A7’ for the word “unresolved”, if true copy the row range A:P to ‘sheet2’, find the next available empty row starting on range ‘A7’. Then to go back to ‘Sheet1’ and delete the data that was copied.

    Thank you to anyone that can help me with this….

    Regards

    Perbags

  2. #2
    Forum Contributor pierre08's Avatar
    Join Date
    02-16-2010
    Location
    Paris
    MS-Off Ver
    Excel 2003
    Posts
    166

    Re: If value found, copy range macro help!

    Hi,
    that should do it:
    Please Login or Register  to view this content.

  3. #3
    Registered User
    Join Date
    06-17-2010
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If value found, copy range macro help!

    I have just ran the Marco and get the following error msg..
    'Cannot jump to 'default' because it is hidden'
    The data it self is entered using drop list, would this effect the macro?

    Thank you for all your help...

  4. #4
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: If value found, copy range macro help!

    Try this,

    It needs a bit of tidying, but we can do this if it works for you.
    Please Login or Register  to view this content.

    Hope this helps

  5. #5
    Registered User
    Join Date
    06-17-2010
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If value found, copy range macro help!

    Hi Marcol,

    I have just ran the Marco but it is not doing anything.. not even getting a error msg this time!

    any ideas to this....

    Thank you

  6. #6
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: If value found, copy range macro help!

    Can you post a sample workbook?

    It should not contain any sensitive data.

    The code will not give you any message because of the On Error method used, the same problem must still exist.

    Need to know what is hidden and how.

    Are you working on an old workbook that may have been created in Excel 97 ?
    Last edited by Marcol; 06-17-2010 at 06:41 AM.

  7. #7
    Registered User
    Join Date
    06-17-2010
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If value found, copy range macro help!

    Here is a copy of the workbook to have a look at...

    I am using Excel 2003..
    Attached Files Attached Files
    Last edited by perbags; 06-17-2010 at 07:45 AM.

  8. #8
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: If value found, copy range macro help!

    You didn't change the sheet names to suit your workbook.
    Also the search string "unresolved" you asked for should have been "UNRESOLVED"
    I didn't allow for ucase, this is easily fixed.

    I couldn't replicate your error, however as I am working with 2007 at the moment, try this workbook and see if the error still exists.

    I have a better code in preparation for you and will post it when you confirm if the error is cleared or not.
    Attached Files Attached Files

  9. #9
    Registered User
    Join Date
    06-17-2010
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If value found, copy range macro help!

    The joys of a being anewbie!!

    That works alot better, with no error forming,
    Just one but issue accord, how do I just get to copy and deleting the range in row (A to P) rather than the whole row as it currently deleting my drop down list.

    Thanks again! Again...

  10. #10
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: If value found, copy range macro help!

    Fixing that and other small issues in new code, got to break off for a while, look back in an hour or so.

    What is to happen with the data in "Today's Data" columns R to AD when A to P is deleted?

    Tip for further Posts
    Problems are always easier solved if a sample workbook is provided from the outset, then all can clearly see the problems.
    Last edited by Marcol; 06-17-2010 at 08:24 AM.

  11. #11
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: If value found, copy range macro help!

    I have found the main source of your problem, it's going to take a little longer to fix than I thought.

    Please bear with me, I need another hour or so. It should be well worth it long term.

  12. #12
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: If value found, copy range

    Okay,

    Your biggest single problem was that you had your Data validation on the same sheet(s) as your active data.

    By adding a sheet for these lookups there is no longer a problem when deleting rows on these sheets.

    I have added dynamic named ranges to the lookup columns on the sheet "Lookups".
    Check out the data validation for the drop-downs to see how this is applied.
    Check out the names manager to see how dynamic ranges are created.

    On the sheet "Lookups", change a value in the yellow cells to see how it changes the resultant list, and hence your dropdowns.

    You might find I have missed some data from these lists, just add or delete as required and see the change in the drop-downs.

    There should be no need to format or validate the sheet "unresolved" this should happen automatically as the data is transferred.

    Finally I have altered the code to make it sheet specific, you can now call it at any time no matter what the active sheet is.
    Please Login or Register  to view this content.

    This can be further generalised, but enough for the moment. Let's see if it works for you first.
    There are bound to be a few things I have got wrong or missed.


    Let me know how you get on with the attached workbook.

    Have fun ...... ...


    If you need any further information, please feel free to ask further

    However

    If this takes care of your needs, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED. It helps everybody!
    Also
    If you are satisfied by any members response to your problem please consider using the scales icon top right of thier post to show your appreciation.
    Attached Files Attached Files

  13. #13
    Registered User
    Join Date
    06-17-2010
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If value found, copy range

    Hi Marcol

    Sorry for the delay in getting back to you but I have just got back from Rotterdam... I taken far too long than I should. I don’t know if you got the message I sent the evening but please ignore as opened the wrong sheet.

    I just opened the updated excel spreadsheet and that is excellent!

    I have a play and make sure it covers everything I need.

    This is just the basic sheet I am working on and need to may to lock down certain parts and added page for ‘RESOVLED’ etc but thank for all your help so far…

  14. #14
    Registered User
    Join Date
    06-17-2010
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If value found, copy range

    That does work just how I need it so far....

    Now my next stage to add to the macro is to it to to create another 2 sheets, one for 'RESOLVED' and one for 'No Issues',

    The Resolved is to do I Identical the identical 'UNRESOLVED' and just copy and delete the copy text.

    The 'No issues' is just to take the take the date and 'No' Field (A&B colums) data then delete the copy text.

    Next is to protect the sheets to ensure the data cant be be changed...

    Resolved , lookup and No issue sheets need to be fully locked down, unresolved needs to all locked down except for columns M,O,P...

    Then I be looking to change the macro to work on a submit button so when a person has entered the data on the 'daily issues' page and the one marco does everything...

    It going to be a long day! ......If you get any ideas on how get this working please let me now... but a BIG thanks to all your help so far..

    At the moment I having to do this manually on daily bases.... :-(

  15. #15
    Registered User
    Join Date
    06-17-2010
    Location
    Bristol, England
    MS-Off Ver
    Excel 2003
    Posts
    13

    Re: If value found, copy range

    Hi Marcol

    I have had a go at VBA and come up with this so far....

    I just need to create the button..

    Can you just have a look at the coding and just tell me if it looks okay etc.. I think it a bit basic and needs a good tiding up... but im getting there .... lol

    thank you...

    Please Login or Register  to view this content.
    Last edited by perbags; 06-22-2010 at 01:59 PM.

  16. #16
    Forum Guru (RIP) Marcol's Avatar
    Join Date
    12-23-2009
    Location
    Fife, Scotland
    MS-Off Ver
    Excel '97 & 2003/7
    Posts
    7,216

    Re: If value found, copy range

    Hi perbags

    Please wrap your code in code tags, before the moderators get you...

    Forum rules
    3. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # button at the top of the post window. If you are editing an existing post, press Go Advanced to see the # button.

    I'm working on the original file now, but will look at your code as well.

    Cheers

  17. #17
    Forum Expert Simon Lloyd's Avatar
    Join Date
    03-02-2004
    Location
    locked in the cage
    MS-Off Ver
    All the ones my homepage shows
    Posts
    3,161

    Re: If value found, copy range

    Not all forums are the same - seek and you shall find

  18. #18
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: If value found, copy range

    Your post does not comply with Rule 8 of our Forum RULES. Cross-posting is when you post the same question in other forums on the web. You'll find people are disinclined to respond to cross-posts because they may be wasting their time solving a problem that has been solved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser)to the cross-post. Expect cross-posts without a link to be closed a message will be posted by the moderator explaining why. We are here to help so help us help you!

    Read this to understand why we ask you to do this


    Cross posted here
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

+ 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