Sub test()
currentwb = ActiveWorkbook.Name ' save the curent workbook name so that we can get back here
Dim wb As Workbook
Dim ws As Worksheet
' loop through all the workbooks to find one ith a name starting with "Remaining Balance Report"
wbnotfound = True ' set a flag to check if the workbook is found
For Each wb In Application.Workbooks
If Left(wb.Name, 24) = "Remaining Balance Report" Then
wb.Activate ' select the workbook if found
wbnotfound = False ' set the flag to show we foudn the workbook
Exit For
End If
Next wb
If wbnotfound Then GoTo notfound ' if workbook not found exit sub with message
yrno = Year(Now()) ' save this year
mn = Month(Now()) ' save this month
If Len(mn) = 1 Then ' check if month number is single digit
zz = "0" ' if so add a zero on the front
Else
zz = "" ' else leave as blank
End If
wsname = "fdx_ENCORE_REM_BAL_CERTS_" & yrno & zz & mn ' create string for worksheet name for current date using this month an year
On Error GoTo notfound ' set error capture to local exit
Worksheets(wsname).Select ' select the workseet if this creates an error it will goto notfound
On Error GoTo 0 ' reset error handling to outside this procedure again
qq = MsgBox("is this the correct worksheet", vbYesNo) ' ask user whether we have gtoto the correct sheet
If qq <> vbYes Then ' if user asnwer no
MsgBox ("exit") ' raise a message
Exit Sub ' aad exit the sub having done nothing
End If
' we are in the correct sheet so not load all the data into a variant array
' find lastrow in column B
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
inarr = Range(Cells(1, 1), Cells(lastrow, 13)) ' pick up all the data
' swap back to original workbook
Workbooks(currentwb).Activate
With Worksheets("Sheet1") 'select the workhseet with the list of contract in column A
lasta = .Cells(Rows.Count, "A").End(xlUp).Row ' find last row
POID = .Range(.Cells(1, 1), .Cells(lasta, 1)) ' load all the data in column A into a variant array
End With
Sheets.Add ' add a workhseet to put the results
outarr = Range(Cells(1, 1), Cells(lasta * 10, 13)) ' define an output variant array
indi = 2 ' set out put start row
For i = 2 To lasta ' loop through all the contracts
For j = 2 To lastrow ' loop through all the data in the remaining balance report
If POID(i, 1) = inarr(j, 2) Then ' check if this line has dat afor the contract we are looking at
For k = 1 To 13 ' if so copy 13 columns to output
outarr(indi, k) = inarr(j, k)
Next k
indi = indi + 1 ' increment the output row
End If
Next j
Next i
Range(Cells(1, 1), Cells(lasta * 10, 13)) = outarr ' output the array to the worksheet
Exit Sub
' error handler for workbook or worksheet not found
notfound:
MsgBox "Worksheet or workbook not found"
End Sub