As I know...the difference between two could be understand by below example.

If you need to perform an operation on a specific sheet, you do
Worksheets("Sheet1").Select
Operation will take place perfectly does not matter which sheet  you had
active.

But in another case you perform the operation after activating the sheet and
you use.
Worksheets("Sheet1").Activate