PDA

View Full Version : VBA - Macro has to run twice for it to work.



Danroyd
09-14-2017, 05:58 AM
Goodafternoon Everyone.

Looking for a little help please.
I have a basic Macro that stops part way through on a "Paste" command. If I run the same marco a second time is runs through and completes the sequence.



Sub Completed()

'
' Archive completed record
'
'
'GoTo 999
Dim a, j, k, l, m
Dim LastRow As Long
Sheets("To do").Select
Columns("A:A").Select
'Find the Task, Select and cut


On Error GoTo Errhandler
a = Application.InputBox(prompt:="Enter Enquiry Number: ", Type:=1)

Selection.Find(What:=a, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlWhole, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True).Activate

ActiveCell.Rows("1:1").EntireRow.Select
Selection.Cut

Sheets("Completed").Select
LastRow = GetLastLine() + 1
Rows(LastRow).EntireRow.Select
'The Macro Stops here. The Row is selected but the Paste does not happen.

ActiveSheet.Paste


'Go back to ToDo Sheet
Sheets("To do").Select
Application.CutCopyMode = False
Sort_BySeqNo
Cond_format
Boarders
Range("A" & GetLastLine()).Select
Exit Sub

Errhandler:
Select Case Err
Case 91: 'Error 91 "object not defined"
Msg = "Enq Number not found !!!"
MsgBox Msg, vbCritical
Range("A3").Select
End Select

End Sub

Any suggestions please?


Regards

Dan

Bob Phillips
09-14-2017, 07:11 AM
Can you post the workbook, there is other stuff there you are not showing.