• 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...

  • 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

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.
 
Hi and welcome to the forum :)

Something like this perhaps?
Code:
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
 

Attachments

  • Sample.xlsm
    21.8 KB · Views: 5
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
 
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.
 
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 ...
 

Attachments

  • Goods In - KPI.xlsb
    148.9 KB · Views: 2
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
 
Maybe after dinner ...
all matters as empty stomach.
Protection is not challenge if ...
but without few steps - no work,
if cannot paste data!
 
No I got a sub script 9 error... unlocked the sheet but still the same error
 

Attachments

  • Goods In - KPI.xlsb
    152.4 KB · Views: 1
Last edited:
Kurt Smart
Your S-Column DropDown ...
There have to be 'Issues' and 'Completed'
(and there is option for 'In Progress')
just as Sheet-names!
 

Attachments

  • Goods In - KPI (1).xlsb
    149 KB · Views: 2
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?
 
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:
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
 
So far ... SORTING have to do manually!
I have to test with better time how to use those table names.
 
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
 
Kurt Smart
This version would work 'better' after ... drop down are as sheet names.
Couldn't test much because it needs data!
 

Attachments

  • Goods In - KPI (1).xlsb
    161 KB · Views: 2
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
 

Attachments

  • Goods In - KPI (1).xlsb
    161.8 KB · Views: 1
Last edited:
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 <<
 
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.
 

Attachments

  • Goods In - KPI - Near Final 2000.xlsb
    140.2 KB · Views: 3
Back
Top