End of Macro

[JTL9161]

I am using a macro from a co-worker that is no longer with the company.

Its a macro I attach to our main frame emulator. When I start it with a spreadsheet open it takes a code number from cell B2 on the spreadsheet, pastes it in the emulator, hits enter and then screen scrapes 6 pieces of data back to columns c through I on the spreadsheet. Then moves down 1 line to cell B3 and continues until there is a blank cell.

While I pretty much can figure out what each line of code in the macro does, I am trying to find the spot in the macro to add some additional steps after it reaches the blank line and stops with the screen scrapes and its back on spreadsheet. I want to add an option to format a column with the proper date format and then copy to another spreadsheet I already have open.

I keep trying to find the spot where the macro is done scraping but even when I put my additional code prior to the END SUB it reads it to early. Can someone tell me what spot within the below macro I can add my additional steps without making any (major) changes to the existing macro. I did not include each session command in the macro below but it needed I can add them at someone's request.


Thanks,
James

Sub NORX()
Dim lRow As Long, lLastRow As Long
Dim oMyWorkBook As Object
Dim oMyWorkSheet As Object

On Error Resume Next
Set oMyWorkBook = GetObject("C:\Users\jleonar3\Documents\DisCompare\MMDDYY-SGCS.XLSM")
Set oMyWorkSheet = oMyWorkBook.Sheets("sheet1")
On Error GoTo 0


If oMyWorkSheet Is Nothing Then
MsgBox "Source workbook not found."
Exit Sub
End If

With oMyWorkSheet
.Activate

'--find last row of data in col B. xlUp = -4162
lLastRow = .Cells(.Rows.Count, "B").End(-4162).Row

'--step through each row
For lRow = 2 To lLastRow
'--paste policy number onto clipboard
.Cells(lRow, "B").Copy

'--call procedure that will lookup corresponding network number
' and paste it to clipboard
Call DoBPLTerminalSession

'--paste network number into columm C
.Cells(lRow, "C").Select
.Paste

Call DoSTARTTerminalSession
.Cells(lRow, "D").Select
.Paste

Call DoENDTerminalSession
.Cells(lRow, "E").Select
.Paste

Call DoPLANTerminalSession
.Cells(lRow, "F").Select
.Paste

Call DoTRANSDATETerminalSession
.Cells(lRow, "G").Select
.Paste

Call DoSTATETerminalSession
.Cells(lRow, "I").Select
.Paste

Next lRow


End With
End Sub

 

[Fluff]

Put what you need after the End With and before the End Sub

 

[JTL9161]

Thanks. I will give it a try.