How do I get it to select a single row based on the value

0 votes

In order to make an excel worksheet easier to understand, I want the data in the rows to be transferred based on the value. I want it to be transferred to Carc if the value is "done." I want it transferred to Ccon if the value is "On-going" (I haven't typed this out yet). Although this has been written out in VBA, I'm willing to explore other things if they will make the process simpler.

The main point is that I'm looking for a way to make the already written code simpler and more useful. The only thing I haven't found out is how to make it select just one row rather than all of them.

Sub MoveBasedOnValue2()

Dim TakeCell As Range
Dim DestCell As Range

Dim Status As Range
Dim Cjob As Worksheet
Dim CArc As Worksheet

Dim Contact As Range, Subject As Range, JobNo As Range, QuoteNo As Range
Dim Dateofcommision As Range, Ddate As Range

Set Cjob = Sheet4
Set CArc = Sheet1


If Cjob.Range("G2") = "Done" Then

Set Contact = Cjob.Range("A2")
Set Subject = Cjob.Range("B2")
Set QuoteNo = Cjob.Range("C2")
Set JobNo = Cjob.Range("D2")
Set Dateofcommision = Cjob.Range("E2")
Set Ddate = Cjob.Range("F2")

Status.Select
Contact.Select
Subject.Select
QuoteNo.Select
JobNo.Select
Dateofcommision.Select
Ddate.Select

If CArc.Range("A2") = "" Then
    Set DestCell = CArc.Range("A2")
Else
    Set DestCell = CArc.Range("A1").End(xlDown).Offset(1, 0)
End If

Contact.Copy DestCell
Subject.Copy DestCell.Offset(0, 1)
QuoteNo.Copy DestCell.Offset(0, 2)
JobNo.Copy DestCell.Offset(0, 3)
Dateofcommision.Copy DestCell.Offset(0, 4)
Ddate.Copy DestCell.Offset(0, 5)

Status.ClearContents
Contact.ClearContents
Subject.ClearContents
QuoteNo.ClearContents
JobNo.ClearContents
Dateofcommision.ClearContents
Ddate.ClearContents
End If
Jan 20 in Others by Kithuzzz
• 27,740 points
30 views

1 answer to this question.

0 votes

Try this:

Sub MoveBasedOnValue2()

    Dim cStatus As Range, wsDest As Worksheet
    
    Set cStatus = Sheet4.Range("G2") 'first cell to check status
    
    Do While Len(cStatus.Value) > 0
        Select Case LCase(cStatus.Value)
            Case "done": Set wsDest = Sheet1
            Case "on-going": Set wsDest = Sheet2 'for example
            Case Else: Set wsDest = Nothing      'no move to make
        End Select
        
        If Not wsDest Is Nothing Then 'got a destination sheet?
            'here Range("A1:F2") is *relative* to the whole row...
            cStatus.EntireRow.Range("A1:F2").Cut _
               Destination:=wsDest.Cells(Rows.Count, "A").End(xlUp).Offset(1)
        End If
        
        Set cStatus = cStatus.Offset(1, 0) 'next source row
    Loop
End Sub
answered Jan 20 by narikkadan
• 51,240 points

Related Questions In Others

0 votes
1 answer
0 votes
1 answer

How do I align a UserForm next to the active cell?

Answer to Q1 - Yes, it's correct. In ...READ MORE

answered Oct 27, 2022 in Others by narikkadan
• 51,240 points
165 views
0 votes
1 answer

How do I merge multiple excel files to a single excel file

You copy a worksheet from before each ...READ MORE

answered Dec 24, 2022 in Others by narikkadan
• 51,240 points
59 views
0 votes
1 answer

Excel-How can I get the address of a cell instead of a value?

There are various difficulties in this. Which ...READ MORE

answered Dec 29, 2022 in Others by narikkadan
• 51,240 points
51 views
0 votes
1 answer

Excel-VBA - How to identify Target range (more than 1 cell) is deleted in a Worksheet_Change function?

You misunderstand the purpose of the function ...READ MORE

answered Sep 23, 2022 in Others by narikkadan
• 51,240 points
1,071 views
0 votes
1 answer

Retrieve epay.info Balance with VBA and Excel

This code should log you in, provided ...READ MORE

answered Sep 5, 2018 in Blockchain by digger
• 26,740 points
522 views
0 votes
1 answer

How to load file to Excel Power query from SFTP site

Currently, I don't think there is a ...READ MORE

answered Dec 3, 2018 in Power BI by Upasana
• 8,620 points
2,382 views
0 votes
1 answer

Using VBA Excel to create a gramatically correct list

The Excel AND function is a logical ...READ MORE

answered Feb 9, 2022 in Others by gaurav
• 22,940 points
110 views
0 votes
1 answer

In excel how do I reference the current row but a specific column?

Put a $ symbol in front of ...READ MORE

answered Oct 15, 2022 in Others by narikkadan
• 51,240 points
193 views
0 votes
1 answer

How can I use a command button in excel to set the value of multiple cells in one click?

Try this: Private Scan As Integer Private Sub CommandButton1_Click() ...READ MORE

answered Oct 24, 2022 in Others by narikkadan
• 51,240 points
138 views
webinar REGISTER FOR FREE WEBINAR X
REGISTER NOW
webinar_success Thank you for registering Join Edureka Meetup community for 100+ Free Webinars each month JOIN MEETUP GROUP