lundi 26 juin 2017

Cut and Paste into a sheet with Case-Select

I am new to writing macros and trying to write one for work. Below is a piece of code I have been fighting with. I want it to look at sheet "NG304" and find key words listed in column B. If the key words are there, move them to the second spreadsheet "Payroll Detail". Issues i'm having - the code is not going through the whole list and it doesn't seem to be pasting in the next available row on the payroll detail spreadsheet (it will simply paste on top of my header).

Can anyone assist? Code below.

Dim Findme As String, Findwhat As String, c As Range

With ActiveWorkbook.Worksheets("NG304")

    For Each c In .Range(.Cells(2, "B"), .Cells(Rows.Count, "B").End(xlUp))
        Findwhat = vbNullString
        Findme = StrConv(c.Value2, vbProperCase)
        Select Case True
            Case Findme Like "VCIP"
                Findwhat = "VCIP"
            Case Findme Like "Company Labor"
                Findwhat = UCase(Findme)
            Case Else
                'do nothing
        End Select

   If CBool(Len(Findwhat)) Then
            With .Parent.Worksheets("NG304")
                c.EntireRow.Cut Destination:=Worksheets("Payroll Detail").Range("A" & lastrow + 1)
        lastrow = lastrow + 1
            End With
        End If
    Next c


End With

Aucun commentaire:

Enregistrer un commentaire