Having used macros a lot in my days as a Common Lisp programmer, I’m a fan. They are certainly a good way to allow you to author an internal DSL and then get the compiler to transform it down into the base language. Keeping everything in the same language means that you can use your usual debugging tools both to debug the transformation and to debug the code that is produced. In a Common Lisp system the compiler can often track how code in the original DSL is pushed into the final implementation code, and therefore can link them up when you are source code debugging.
The big advantage of languages like Common Lisp and its derivatives like Clojure, are that the syntax tree is presented to the macro function in a form that uses the base datatypes of the language and more specially in the actual datatypes that you, the user, use to express your program – atoms, lists and vectors. You don’t get this in other languages, where parsing is a lot more complicated, and here you need to use accessors to navigate around the syntax tree which feels a lot less natural.
Macros started in early versions of Lisp, but have been making their way into other languages too. Scala, for example, has had them for some time with a design that fits in well with an emphasis on domain specific languages (though that example doesn’t actually use macros).