Sub Comments()

Dim WBNEW As Workbook 'current week's workbook
Dim WBOLD As Workbook 'previous week's workbook
Dim WSNEW As Worksheet 'current week's worksheet
Dim WSOLD As Worksheet 'previous week's worksheet
Dim WSOLDR As Range


'Open all relevant workbooks also make them macro enabled?
Set WBNEW = Workbooks("HPD10.xlsm") '<--------change name of current week's workbook
Set WBOLD = Workbooks("HPD9.xlsm") '<--------change name of previous week's workbook
Set WSNEW = WBNEW.Worksheets("Open cases") 'change if relevant worksheet has different name
Set WSOLD = WBOLD.Worksheets("Open cases") 'change if relevant worksheet has different name
Set StartCell = WSOLD.Range("A1")

LastRow = WSOLD.Cells(WSOLD.Rows.Count, 1).End(xlUp).Row 'count last row
LastColumn = WSOLD.Cells(1, WSOLD.Columns.Count).End(xlToLeft).Column 'count last column
Set WSOLDR = WSOLD.Range(StartCell, WSOLD.Cells(LastRow, LastColumn))

Dim i As Long

For i = 1 To WSNEW.Rows.Count 'loops i from 1 to the last row in WSNEW which is counted
    If WSNEW.Cells(i, 1).Value <> "" Then 'if value of cells is NOT BLANK
    WSNEW.Cells(i, 37).Value = Application.VLookup(WSNEW.Cells(i, 1).Value, WSOLDR, 37, False) 'Assign each vlookup to a cell in WSNEW
    WSNEW.Cells(i, 38).Value = Application.VLookup(WSNEW.Cells(i, 1).Value, WSOLDR, 38, False)
    WSNEW.Cells(i, 39).Value = Application.VLookup(WSNEW.Cells(i, 1).Value, WSOLDR, 39, False)
    WSNEW.Cells(i, 40).Value = Application.VLookup(WSNEW.Cells(i, 1).Value, WSOLDR, 40, False)
    End If
Next i

MsgBox ("Transfer Complete! =)") 'popup textbox indicating code finished running

End Sub