Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
Hi all,
i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
Hi,
Right click the sheet tab with the data in, view code and paste this in and run it Sub stance() Dim MyRange Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & Lastrow) For Each C In MyRange If UCase(C.Value) = "R" Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Sheets("Resigned").Range("A1").PasteSpecial End If End Sub Mike "Vijay DSK" wrote: Hi all, i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
I forgot,
You never said which column the code "R" was in so I assumed column D. Change to suit. Mike "Mike H" wrote: Hi, Right click the sheet tab with the data in, view code and paste this in and run it Sub stance() Dim MyRange Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & Lastrow) For Each C In MyRange If UCase(C.Value) = "R" Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Sheets("Resigned").Range("A1").PasteSpecial End If End Sub Mike "Vijay DSK" wrote: Hi all, i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
Mike
Tested your code, seems working well. Need some time to observe perfectly and another thing what i observed is i need to press Alt+F8, in order to run the macro, but what i am looking is the moment i changed the status from w to r immediately without pressing alt+F8, the data should be copied into the second sheet. Please adjust the code as required. Thanks in advance "Mike H" wrote: I forgot, You never said which column the code "R" was in so I assumed column D. Change to suit. Mike "Mike H" wrote: Hi, Right click the sheet tab with the data in, view code and paste this in and run it Sub stance() Dim MyRange Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & Lastrow) For Each C In MyRange If UCase(C.Value) = "R" Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Sheets("Resigned").Range("A1").PasteSpecial End If End Sub Mike "Vijay DSK" wrote: Hi all, i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
Mike can you just look into this and help me out.
Thanks in advance "Vijay DSK" wrote: Mike Tested your code, seems working well. Need some time to observe perfectly and another thing what i observed is i need to press Alt+F8, in order to run the macro, but what i am looking is the moment i changed the status from w to r immediately without pressing alt+F8, the data should be copied into the second sheet. Please adjust the code as required. Thanks in advance "Mike H" wrote: I forgot, You never said which column the code "R" was in so I assumed column D. Change to suit. Mike "Mike H" wrote: Hi, Right click the sheet tab with the data in, view code and paste this in and run it Sub stance() Dim MyRange Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & Lastrow) For Each C In MyRange If UCase(C.Value) = "R" Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Sheets("Resigned").Range("A1").PasteSpecial End If End Sub Mike "Vijay DSK" wrote: Hi all, i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
Try this event code.
Private Sub Worksheet_Change(ByVal Target As Range) Const MyRange As String = "D:D" Dim rng2 As Range Set rng2 = Worksheets("Resigned").Cells(Rows.Count, 1).End(xlUp) _ ..Offset(1, 0) If Not Intersect(Target, Me.Range(MyRange)) Is Nothing Then If UCase(Target.Value) = "R" Then Target.EntireRow.Copy Destination:=rng2 End If End If End Sub This is sheet event code. Right-click on the sheet tab and paste the code into that module. Edit MyRange to suit........I used column D as Mike did. Alt + q to return to the Excel window. Start pounding r's into the column to have the entire row copied to next available row in Resigned worksheet. Note..................will not copy existing r's rows to the new sheet. Run Mike's code once to achieve that first. Gord Dibben MS Excel MVP On Mon, 20 Oct 2008 04:59:02 -0700, Vijay DSK wrote: Mike can you just look into this and help me out. Thanks in advance "Vijay DSK" wrote: Mike Tested your code, seems working well. Need some time to observe perfectly and another thing what i observed is i need to press Alt+F8, in order to run the macro, but what i am looking is the moment i changed the status from w to r immediately without pressing alt+F8, the data should be copied into the second sheet. Please adjust the code as required. Thanks in advance "Mike H" wrote: I forgot, You never said which column the code "R" was in so I assumed column D. Change to suit. Mike "Mike H" wrote: Hi, Right click the sheet tab with the data in, view code and paste this in and run it Sub stance() Dim MyRange Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & Lastrow) For Each C In MyRange If UCase(C.Value) = "R" Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Sheets("Resigned").Range("A1").PasteSpecial End If End Sub Mike "Vijay DSK" wrote: Hi all, i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
Gord Dibben,
Thanks for your advise, sorry for the late reply. The code is working well, but it was copying the data to "resigned" data and pasting with a row gap from the existing row. Pls advise. I have two more issues on this but to avoid confusion i will present them once this issue is sorted. Thanks in advance "Gord Dibben" wrote: Try this event code. Private Sub Worksheet_Change(ByVal Target As Range) Const MyRange As String = "D:D" Dim rng2 As Range Set rng2 = Worksheets("Resigned").Cells(Rows.Count, 1).End(xlUp) _ ..Offset(1, 0) If Not Intersect(Target, Me.Range(MyRange)) Is Nothing Then If UCase(Target.Value) = "R" Then Target.EntireRow.Copy Destination:=rng2 End If End If End Sub This is sheet event code. Right-click on the sheet tab and paste the code into that module. Edit MyRange to suit........I used column D as Mike did. Alt + q to return to the Excel window. Start pounding r's into the column to have the entire row copied to next available row in Resigned worksheet. Note..................will not copy existing r's rows to the new sheet. Run Mike's code once to achieve that first. Gord Dibben MS Excel MVP On Mon, 20 Oct 2008 04:59:02 -0700, Vijay DSK wrote: Mike can you just look into this and help me out. Thanks in advance "Vijay DSK" wrote: Mike Tested your code, seems working well. Need some time to observe perfectly and another thing what i observed is i need to press Alt+F8, in order to run the macro, but what i am looking is the moment i changed the status from w to r immediately without pressing alt+F8, the data should be copied into the second sheet. Please adjust the code as required. Thanks in advance "Mike H" wrote: I forgot, You never said which column the code "R" was in so I assumed column D. Change to suit. Mike "Mike H" wrote: Hi, Right click the sheet tab with the data in, view code and paste this in and run it Sub stance() Dim MyRange Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & Lastrow) For Each C In MyRange If UCase(C.Value) = "R" Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Sheets("Resigned").Range("A1").PasteSpecial End If End Sub Mike "Vijay DSK" wrote: Hi all, i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
How would I modify the code to copy rows to different sheets based on the
contents of the cell. To keep on the same page as previous: Say there was another sheet named "absconding" and whenever the "AB" was in the cell that row is copied to the sheet "absconding". And there is a third sheet named "working" and whenever the "W" was in the cell that row is copied to the sheet "working". I have 4-5 possibilities for the contents of the cell and I want to have 4-5 sheets. Each sheet for each posibility. "Mike H" wrote: I forgot, You never said which column the code "R" was in so I assumed column D. Change to suit. Mike "Mike H" wrote: Hi, Right click the sheet tab with the data in, view code and paste this in and run it Sub stance() Dim MyRange Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & Lastrow) For Each C In MyRange If UCase(C.Value) = "R" Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Sheets("Resigned").Range("A1").PasteSpecial End If End Sub Mike "Vijay DSK" wrote: Hi all, i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Moving to other sheet basis on condition
Homer,
Sorry for the late reply. Actually I am looking at different categaries (Working, Resigned and absconding) to be copied on to different sheets (Sheet names ex: working, resigned and absconding) Once again sorry for the late reply. "Homer" wrote: How would I modify the code to copy rows to different sheets based on the contents of the cell. To keep on the same page as previous: Say there was another sheet named "absconding" and whenever the "AB" was in the cell that row is copied to the sheet "absconding". And there is a third sheet named "working" and whenever the "W" was in the cell that row is copied to the sheet "working". I have 4-5 possibilities for the contents of the cell and I want to have 4-5 sheets. Each sheet for each posibility. "Mike H" wrote: I forgot, You never said which column the code "R" was in so I assumed column D. Change to suit. Mike "Mike H" wrote: Hi, Right click the sheet tab with the data in, view code and paste this in and run it Sub stance() Dim MyRange Dim copyrange As Range Lastrow = Cells(Cells.Rows.Count, "D").End(xlUp).Row Set MyRange = Range("D1:D" & Lastrow) For Each C In MyRange If UCase(C.Value) = "R" Then If copyrange Is Nothing Then Set copyrange = C.EntireRow Else Set copyrange = Union(copyrange, C.EntireRow) End If End If Next If Not copyrange Is Nothing Then copyrange.Copy Sheets("Resigned").Range("A1").PasteSpecial End If End Sub Mike "Vijay DSK" wrote: Hi all, i am a novice in Excel. Need your advise in solving a problem. The issue is I have a sheet with all my employee names with their working status as "W" for Working "R"for Resigned and "AB" absconding. I conditionally formatted the cell, as it was filled with Red color where R is the condition. Now i am looking a solution, i want to copy that entire row to a sheet by name "Resigned" where working status is "R". I am looking for vba code as it could not be viewed by lay men. Thanks in advance for resolving the issue. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
formula for moving information from one sheet to another sheet | Excel Discussion (Misc queries) | |||
Moving text from a line to a new line based on a condition | Excel Programming | |||
Automatically create list in different sheet on basis of other lis | Excel Worksheet Functions | |||
Copy from one Sheet and paste on another sheet based on condition | Excel Discussion (Misc queries) | |||
Breaking text on the basis of condition | Excel Programming |