In the following code:
Dim FirstSourceCell As Range
Set FirstSourceCell = Workbooks("Source.xlsx").Worksheets ("Settlements").Range("M9")
FirstSourceCell.Activate
why does the FirstSourceCell.Activate works only when the Source.xlsx workbook is activated?
You can only activate a cell in the currently active worksheet. So if the workbook isn't active, the worksheet can't be active, therefore you will get an error if you try to activate a cell on that inactive worksheet.
But there are very few cases where a cell needs to be activated. About the only real case is where you want to return control to the user with a specific cell selected. In that case, it is better to use Application.GoTo FirstSourceCell.