1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

If cell = X copy to. relevant sheet

Discussion in 'VBA Macros' started by Kurt Smart, Jun 14, 2017.

  1. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    Hi all,

    I'm a newbie to Excel i have 3 sheets.... "in progress", "Issues" and "Complete".

    In Column 'S' I want to look for 'Complete' and 'Issue'

    Then if found to copy columns 'A to 'S' to the relevant sheet on the next empty row.

    Thank you for any help.
  2. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Kurt Smart
    Maybe You would upload a file
    with sample data,
    example of You case
    and
    of course (even manually solved) sample results?
  3. PCosta87

    PCosta87 Well-Known Member

    Messages:
    870
    Hi and welcome to the forum :)

    Something like this perhaps?
    Code (vb):
    Sub test()

        Dim c As Range, lrow As Integer
       
        lrow = Cells(Rows.Count, 1).End(xlUp).Row
       
        For Each c In Range("A1:A" & lrow)
            Select Case c.Offset(, 18)
                Case Is = "Complete"
                    Range(c, c.Offset(, 18)).Copy Sheets("Complete").Cells(Rows.Count, 1).End(xlUp).Offset(1)
                Case Is = "Issue"
                    Range(c, c.Offset(, 18)).Copy Sheets("Issues").Cells(Rows.Count, 1).End(xlUp).Offset(1)
            End Select
        Next c
       
    End Sub
    Attached

    Hope this helps

    Attached Files:

  4. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    Hi guys, sorry more information would be useful to you.

    Screen shot below of main working sheet called 'In Progress'

    In column 'S' if the dropdown selection is 'Complete' then I would like to copy the entire row but only from 'A' to 'S' to another sheet called 'Completed' and then delete what was copied from the 'In Progress' sheet.

    If 'S' equals 'Issue' then I would like the entire row copied to the sheet called 'Issues' and then delete the copied row from 'In Progress'.

    Hope this is clear information I have no knowledge of VBA but i'm trying to learn.

    Thanks for any help so far.


    1.jpg
  5. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Kurt Smart - screen shot - yes!
    Okay - maybe You missed few points
    but You asked something like
    'act with S-columns dropdown moves data from sheet-to-sheet'
    > Ideas - Questions? <

    Attached Files:

    Kurt Smart and Thomas Kuriakose like this.
  6. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    This works great on your sheet, how can I integrate into mine.

    And thank you very much
  7. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Kurt Smart
    As I wrote in #2:
    Maybe You would upload a file with sample data ...
    Or just save that one Macro from ThisWorkbook (Code) sheet
    to Your file (same place).
    Qs: That moves also 'Comments' to next sheet and
    tries to move that range to end of target sheet.
  8. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    This is a copy of the file i'm working on with sample data

    Attached Files:

  9. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Kurt Smart
    1) You wrote: i have 3 sheets.... "in progress", "Issues" and "Complete".
    There are five sheets! ...
    2) You didn't answer my previous questions (#7) ...
    T-column won't copy ... and hopeful that 'Complete'-sheet can do paste.
    >> I copied that Macro as I wrote ... with none T-column move! <<
    >>> I didn't test at all ...

    Attached Files:

  10. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    Ok I will try it after my dinner. Yeah I didn't mention the others as the 3 are the only ones that information is needed to move around on I didn't realise it would matter. I also just remember that complete sheet is protected! Not a good day for me sorry
  11. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Maybe after dinner ...
    all matters as empty stomach.
    Protection is not challenge if ...
    but without few steps - no work,
    if cannot paste data!
  12. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    No I got a sub script 9 error... unlocked the sheet but still the same error

    Attached Files:

    Last edited: Jun 17, 2017
  13. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Kurt Smart
    Your S-Column DropDown ...
    There have to be 'Issues' and 'Completed'
    (and there is option for 'In Progress')
    just as Sheet-names!

    Attached Files:

  14. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    Ok thank you something is happening now but the lines are vanishing from in progress and not appearing anywhere

    Any ideas?
  15. vletm

    vletm Well-Known Member

    Messages:
    2,708
    And some data to every sheet and upload here.
  16. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    Ah Ok I see it is working but because I have formulas in column B It is going right down to the end where the formulas finish how do I get around this?
  17. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Yes .. I tested and just noticed same ...
    Your sheets could 'Sort' automatic after move...
    Which column is the best for sorting?
    Note: What is that 'Yes' in 'DropDown' ... that makes challenge without modify code!
    Last edited: Jun 17, 2017
  18. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    By 'Date Issued' I think would be best. Also noticed the 'Category' column isnt staying with the supplier.
    Example Acco B when it gets copied over its no Acco N/A
  19. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    The 'Yes' is for columns 'Started' and 'Posted' .... Issue is used in both 'Posted' and 'Completed'
  20. vletm

    vletm Well-Known Member

    Messages:
    2,708
    So far ... SORTING have to do manually!
    I have to test with better time how to use those table names.
  21. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    Would it be possible to get the sheet to switch back to 'in progress' once it's copied over a row? So the process is invisible to the admin user to them it just disappears.

    I really appreciate what you have done so far a great help to a novice thank you
  22. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Kurt Smart
    This version would work 'better' after ... drop down are as sheet names.
    Couldn't test much because it needs data!

    Attached Files:

  23. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    In 'Completed' option once it copies into the 'Completed' sheet the formulas get messed up after 'S' column. and the 'Category column seems to get messed up to and the comments do not get copied if its an Issue.

    I dont really want the condition formatting copying over to the 'Completed' sheet jus the data if thats possable.

    Ive attached new copy with Data changed so you can test your end too

    Attached Files:

    Last edited: Jun 18, 2017
  24. vletm

    vletm Well-Known Member

    Messages:
    2,708
    Kurt Smart
    1) As I already wrote #13:
    Your S-Column DropDown ...
    There have to be 'Issues' and 'Completed' Completed is okay
    (and there is option for 'In Progress')
    just as Sheet-names!
    2) Formulas: What kind of mess?
    Maybe some mess was a part of 'act move' which I changed.
    Anyway, I won't use that kind of formulas at all.
    3) I dont really need the colours ... 'as You wrote as You get':
    Then if found to copy columns 'A to 'S' to the relevant sheet on the next empty row.
    But maybe that 'better' after that change.
    4)
    Ok I will try it after my dinner. Yeah I didn't mention the others as the 3 are the only ones that information is needed to move around on I didn't realise it would matter. I also just remember that complete sheet is protected! Not a good day for me sorry
    >> Think twice - Write once <<
  25. Kurt Smart

    Kurt Smart New Member

    Messages:
    13
    Hi vletm,
    Everything is working apart from it is copying the entire column 'Category' to either 'Issues' or 'Completed' instead of just the row data.

    Seams like its trying to also move the formula instead of just pasting the data in the cell.

    Attached Files:

Share This Page