The happy ending of the first book when his mother is healed. The fairies think it was just a calculated ploy but it's clear it means a lot more than that to him.
From The Arctic Incident: "Well, young man? Have you found anyone worthy of your respect?" Artemis: "Yes. Yes, I believe I have."
Juliet and Butler's reunion in The Eternity Code, after Juliet skipped out on her training with Madame Ko after finding out Butler had died for a little bit.
The exchange that occured when Juliet found out:
Juliet: You mean, Butler needs me?
Madame Ko: No. I am reading the message as it's written. Domovoi needs you.
Cue Juliet running like hell for the airfield, completely abandoning her training to be with her brother.
Speaking of Butler's temporary death: "Artemis... call me... Domovoi"
A small one, but when Artemis hacks into Spiro's accounts at the end of The Eternity Code, he first intends to take the money for himself, but he remembers his father asking him if, when he gets the chance, he'll try to be a hero and make a difference, and ends up sending 90% of Spiro's billions to Amnesty International. And he makes the transaction non-reversible.
When Artemis finally reunites with his father, Artemis Senior.
"Arty," he cried, stretching his arms toward me. "You're a man now. A young man."
I ran into his embrace, and while he held me close all plots and schemes were forgotten. I had a father again.
While it's somewhat subverted since he does pull a Memory Gambit, the third Artemis Fowl has one of these at the end, when his memory of fairies and all related experiences are to be wiped. He reflects on how he's become a better person and is afraid that absent these memories, he will regress to being ruthless again.
Artemis and Holly becoming friends again in The Opal Deception. Even though Artemis hasn't got his memories back yet, in their own words, they're "bonded by trauma".
In "The Lost Colony", there's a fleeting moment where the narrator tells us that imps never know their parents, but Number 1 used to see a female with markings like his who was looking for her son. She'd smile at him, and he'd smile back, so they could pretend to have found each other. Also something of a Tear Jerker, because it's impossible to know if she really was his mother.
In the fifth book, when Holly dies, and Artemis revives her and simultaneously defeats Abbott by shooting a laser blast into the past. However, their reactions afterward are enough to make anyone go "Aaaaaaww".
"Hey," said a familiar voice. "Don't you have a job to do, Artemis?" It was Holly. She was hog-tying Abbot with his own bootlaces. Artemis could only stare at her and smile. He still felt the pain of her death, but that would heal quickly now that she was alive again. Holly caught him smiling. "Artemis, could you get that box on to the plateau? It's a simple plan." Artemis smiled some more, then shook himself. "Yes. Of course. Put the box on the plateau."
Holly finding out what happened later, via the group's temporary mental connection as they're departing Limbo.
Artemis turned and stared at his friend with blue eyes. Holly was staring back, and she was smiling. "I remember," she said aloud. "You saved me." Artemis smiled back. "It never happened," he said.
In The Time Paradox, Artemis struggles to think of a way to make up for having tricked Holly into thinking she'd poisoned his mother with an incurable disease. He finally finds the answer when he tells Holly to contact her commanding officer while they were in the past so she could tell him everything she didn't get to say when he died.
In the same book: whether or not you're an A/H shipper, the scene with Holly crying and smiling after healing Artemis is very touching.
"Of course I saved you. I couldn't do without you."
In The Last Guardian Artemis and Holly attempting to out-gambit each other to stop the other performing a heroic sacrifice. Artemis, of course, wins. He thanks her for fixing him when he was broken, kisses her and goes to his death to protect her and his family.
Artemis: I want you to know, my dear friend, that without you, I would not be the person I am today. I was a broken boy, and you fixed me. Thank you.
In the code down the bottom of the last book Colfer wrote Artemis's will. Half of it is, of course, jokes, until you get to:
To my friend, Captain Holly Short of the Lower Elements Police, I leave the thirty seven solid gold bars that were the price of her release all those years ago. I know that I can never make up for that crime but hopefully you can think of me as a friend when you do think of me.