copy line of text to specific worksheet based upon an id

[sfzzh]

Hi I currently have an excel sheet that copy's a line of text from 1 worksheet to another I am wanting to try and separate these to individual sheets based on an asset id with a message box as shown below if the asset id is unknown put it to a separate miscellaneous sheet.

I am pretty sure it needs to be several case statements with a case else stamen

Would appreciate any feed back

The activecell is written so I can click onywhere on the line of the excel sheet

something like

Select Case asset id

Case Asset ID 1

Select Case MsgBox _
("You are about to archive this job." _
& vbCrLf & "Check everything before you proceed", _
vbYesNo Or vbExclamation, "Are you sure?")
Case vbYes
Case vbNo
Exit Sub
End Select



If ActiveCell.Entirerow.range("A1").offset(0, 2).Value =" asset id" Then

ActiveCell.EntireRow.Range("A1").Offset(0, 0).Range("A1:L1").Copy

Sheet5.Range("A6").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues

ActiveCell.EntireRow.Range("A1").Offset(0, 0).Range("A1:L1").ClearContents

Case anything else ( if the asset id is not within the code put it to a separate miscellaneous sheet)

 

[sfzzh]

Think I have cracked it am sure there is a better way but this does work

Please note in the sheet you are copying to you will need to fill the first line in manually
In my case range A7 or the macro will come up error

Sub Archiveit()


If ActiveCell.EntireRow.Range("A1").Offset(0, 11).Value = "" Then
MsgBox "You have not added the completed date"
Exit Sub
End If

Select Case MsgBox _
("You are about to archive this job." _
& vbCrLf & "Check everything before you proceed", _
vbYesNo Or vbExclamation, "Are you sure?")
Case vbYes
Case vbNo
Exit Sub
End Select

Select Case ActiveCell.EntireRow.Range("A1").Offset(0, 1).Value

Case "Press 1"

ActiveCell.EntireRow.Range("A1").Offset(0, 0).Range("A1:L1").Copy

Sheet10.Range("A6").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues

ActiveCell.EntireRow.Range("A1").Offset(0, 0).Range("A1:L1").ClearContents

Range("A13:L10000").Sort Key1:=Range("A13"), Order1:=xlAscending, Header:=xlGuess
Range("A13").Select


Case "Press 2"

ActiveCell.EntireRow.Range("A1").Offset(0, 0).Range("A1:L1").Copy

Sheet11.Range("A6").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues

ActiveCell.EntireRow.Range("A1").Offset(0, 0).Range("A1:L1").ClearContents

Range("A13:L10000").Sort Key1:=Range("A13"), Order1:=xlAscending, Header:=xlGuess

Range("A13").Select

Case Else

ActiveCell.EntireRow.Range("A1").Offset(0, 0).Range("A1:L1").Copy

Sheet5.Range("A6").End(xlDown).Offset(1, 0).PasteSpecial xlPasteValues

ActiveCell.EntireRow.Range("A1").Offset(0, 0).Range("A1:L1").ClearContents

Range("A13:L10000").Sort Key1:=Range("A13"), Order1:=xlAscending, Header:=xlGuess

Range("A13").Select

End Select

End Sub