+ Reply to Thread
Results 1 to 4 of 4

"If there is "NO" in Column C then copy that ROW from A-D and paste it into another Sheet.

  1. #1
    Registered User
    Join Date
    03-31-2004
    Location
    Australia
    Posts
    2

    "If there is "NO" in Column C then copy that ROW from A-D and paste it into another Sheet.

    Hi there all,
    I am needing to be able to see if there is a "NO" in Column C - On Worksheet "Audit"
    If there is then I need to COPY the ROW from A? to D? (whatever row number "No" is on)
    Then I need to Paste that info onto Worksheet "Non Compliant" Starting at A4 to D4 and then continue to the next "NO" and do the Same.

    EXAMPLE
    Worksheet "AUDIT"

    (Row No.) A B C D
    1 Clause No Requirement Compliant Notes
    2 2.3 This is a great place. Yes
    3 2.3.1 I am stuck here lol. No
    4 2.3.1 b) Now to type more. N/A

    So looking at COLUMN "C" I can see ROW "3" has a "NO" in the Compliant spot. So what I need to happen is for the system to Copy A3 to D3 and Paste it into the Worksheet "Non Compliant"

    Starting at A4

    I will need this to keep looking through the Data and if there are any other "NO" answers then the ROWs involved need to be copied to the Worksheet "Non Compliant"

    I do hope that this is as clear as mud and if you need any more info Please leave me a message as I will be checking it from time to time, But I am in Australia which is GMT +10 so that's the time difference.

    Thanks in Advance for any help will be really appreciated.

    Regards

    Gordon
    Take Care and God Bless
    ismegordo

  2. #2
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: "If there is "NO" in Column C then copy that ROW from A-D and paste it into another Sh

    Try this code
    Please Login or Register  to view this content.
    Copy the Excel VBA code
    Select the workbook in which you want to store the Excel VBA code
    Hold the Alt key, and press the F11 key, to open the Visual Basic Editor
    Choose Insert | Module
    Where the cursor is flashing, choose Edit | Paste

    To run the Excel VBA code:
    Choose View | Macros
    Select a macro in the list, and click the Run button
    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]

  3. #3
    Registered User
    Join Date
    03-31-2004
    Location
    Australia
    Posts
    2

    Re: "If there is "NO" in Column C then copy that ROW from A-D and paste it into another Sh

    Hi there Arlette,
    I put the code in and although I made the mistake of the Column having "N" and not NO I changed that and it worked.

    BUT when I went back to the original Excel worksheet that had 500 odd rows I re-did it and it came up with an error box saying "400"
    and it won't work anymore.
    Did I do something wrong or what's happening.
    Thanks for your help so far
    ALSO where you have "For i = 2 to lrow" - Is this because on the Audit worksheet page I said it starts at A2 or is it another reason, it actually starts in A5 and then the Rows with "N" in them need to be copied to "Non Compliant" Worksheet starting in A3
    Hope this helps
    Once again Thanks for your help

  4. #4
    Forum Contributor arlu1201's Avatar
    Join Date
    09-09-2011
    Location
    Bangalore, India
    MS-Off Ver
    Excel 2003 & 2007
    Posts
    19,166

    Re: "If there is "NO" in Column C then copy that ROW from A-D and paste it into another Sh

    If your audit worksheet page starts with row 5 (not headers), then you can change it to For i = 5 to lrow.

    Regarding the 400 error, have you installed any add-in in your system? Try removing it and reinstalling it.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Tags for this Thread

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