The one bit that needs changing is an unqualified reference.
Change:
For Each ce In Worksheets("Sheet1").Range("A2:A" & [COUNTA(A:A)])
to:
For Each ce In Worksheets("Sheet1").Range("A2:A" & [COUNTA(Sheet1!A:A)])