Greetings all. I am working on a macro to take an active row, cut it, then
insert it into a new location, sort the target, then return to the starting
sheet and delete the empty row. The target sheet is a user prompt, where the
user types in the name of the target sheet. I would like for the input box
to open with a dropdown list of all the sheets in the workbook. Also, I
found that in some places I could perform action on the target worksheet
without activating it, like Sheets(Target).Range.some action. But then for
others I have to activate it, so I just activated it from the start. Is
there a way to perform the actions on the target without activating it?
Below is the macro with some comments. I'm still learning, so there are
probably things I am doing that are not the best way to do it, but I try
everything until something works, then I stop. Thank you.

Greg

Sub MoveJobs()

'Get the name of the active sheet so I can come back to it
Dim ActSheet As String
Dim ActCell As String
ActCell = ActiveCell.Address
ActSheet = ActiveSheet.Name

'Get the name of the target sheet
'I would like the imput box to have a drop down menu of all the sheets
'so the user does not have to enter it
Dim Target As String
Target = InputBox("Move where?")

'Move the selection to the first data row in the target sheet
Dim SheetName As Variant
ActiveCell.EntireRow.Select
Selection.Cut
Sheets(Target).Rows("2:2").Insert Shift:=xlDown
lastrow = Sheets(Target).[B65000].End(xlUp).Row

'Delete any rows where column C is blank
'I tried this method to delete the cut row once back on
'the starting sheet, but sometimes it leaves rows.
'If I run it again, it will delete the row from last run, but
'not the new one. It works here though
Sheets(Target).Activate
LstRow = [C65000].End(xlUp).Row
Set MyRng = Range("C2:C" & LstRow)
For Each MyCell In MyRng
If MyCell.Value = "" Then
MyCell.EntireRow.Delete
End If
Next MyCell

'Reset the last row
LstRow = [C65000].End(xlUp).Row

'Resort the selection
Rows("2:" & LstRow).Sort Key1:=Range("B2"), Order1:=xlAscending,
Key2:=Range("C2") _
, Order2:=xlAscending, Header:=xlGuess, OrderCustom:=1, MatchCase:= _
False, Orientation:=xlTopToBottom, DataOption1:=xlSortNormal,
DataOption2 _
:=xlSortNormal

'Go back to the starting sheet
Sheets(ActSheet).Activate

'Reset the last row
LstRow = [C65000].End(xlUp).Row

'Delete the blanks
'I tried this method on the target sheet, but it kept giving errors
'I can not figure out why it works in one place but not the other
'Basically I want one method that works everywhere, every time.
Range("C2:C" &
LstRow).Cells.SpecialCells(xlCellTypeBlanks).Entir eRow.Delete

'Return to the starting point
Range(ActCell).Select

End Sub