copy line of text to specific worksheet based upon an id

sfzzh

New Member
Joined
Mar 18, 2016
Messages
2
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)
 

Some videos you may like

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.

sfzzh

New Member
Joined
Mar 18, 2016
Messages
2
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
 

Watch MrExcel Video

Forum statistics

Threads
1,122,806
Messages
5,598,188
Members
414,218
Latest member
speedbit

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top