I think there are probably better ways to do what you're asking,
but I tossed together this sub which would do what you described, I
think. I'm assuming you're adding the lines from the weekly report
to the end of the MasterPPB report, so if that's NOT the case, this
would need to be adjusted. But, check this out and see if it comes
close--probably needs a bit of tweaking:

Option Explicit

Sub weeklyToMPPB()
Dim iTotalRows
Dim LastCol%
Dim Row1&
Dim Row2&
Dim wkExp As String 'variable for WeeklyExport.xls
Dim MsPPB As String 'variable for MasterPPB.xls'
Dim iRow As Integer

wkExp = "WeeklyExport.xls"
MsPPB = "MasterPPB.xls"
'<----assuming that the first row is column labels
iTotalRows = Range("A65535").End(xlUp).Row
'<---Finds last row used
LastCol% = Cells.Find(What:="*", _
SearchDirection:=xlPrevious, _
SearchOrder:=xlByColumns).Column
'<---Finds last column used

'so a given selection would be
'Range(Cells(Row1&, 1), Cells(Row2&, LastCol%)).Select
'so the loop would set the values for Row1& and Row2&

Windows(wkExp).Activate
'set the first 500 row selection
Row1& = 2
Row2& = Row1& + 499
Range(Cells(Row1&, 1), Cells(Row2&, LastCol%)).Select
Selection.Copy
Windows(MsPPB).Activate
iRow = Range("A65535").End(xlUp).Row + 1
Cells(iRow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False

Do While Cells(Row1&, 1).Value <> ""
Windows(wkExp).Activate
Row1& = Row2 + 1
If Row2& < iTotalRows Then
Row2& = Row1& + 499
Else
Row2& = iTotalRows
End If
Range(Cells(Row1&, 1), Cells(Row2&, LastCol%)).Select
If Cells(Row1&, 1).Value <> "" Then
Selection.Copy
Windows(MsPPB).Activate
iRow = Range("A65535").End(xlUp).Row + 1
Cells(iRow, 1).Select
ActiveSheet.Paste
Application.CutCopyMode = False
End If
Loop

End Sub