I have developed a very nice Lisp dialect from the ground up, in which macros do all these things:

* completely implement the system of "syntactic places": the ability to mutate variables, mutable slots of various kinds on various objects, and user-defined such things. The only API underneath consist of ad hoc functions to update specific kinds of places.

* implement the syntax of the object system: structure defining via defstruct, instantiating objects with new and so on: over top of an API consisting of functions to manipulate the object system.

* various control structures.

* the op macro for currying / partial application.

* the FFI language for defining foreign types, and its operators like typedef, sizeof and offsetof.

* the awk macro: a fully-loaded, "Lispified" version of the Awk language.

* generator-like programming interface (featuring yield with bi-directional communication of values) implemented over top of delimited continuation primitives.

Many more. Macros provide a catalyst for language research. New syntax can be added to existing syntax without any conflict, and without having to muck around with a parser. The new syntax doesn't even have to be loaded into memory if it's not being used.

"Macros don't do much; I don't need need to write macros" ignores all the macros that have been already provided and the benefit they bring. You can't be using existing macros left and right while claiming that they are not all that necessary.

You may feel that way when using a language that has no macros. But the syntax of such a language is effectively a macro expander. A Yacc rule (or what have you) which matches some symbols on the left and produces a piece of AST in its action body is effectively a macro. Someone else wrote that syntax and you benefit from it, just like you benefit from existing macros in a Lisp. But that syntax hinders development of that language; it is ultimately detrimental to that language. Because new versions of the language struggle with the integration of new syntax, they create backward-compatibility wrinkles.

With macros, if I have some piece of code that uses syntax available only in the latest and greatest version of the language (not what I'm using in production), I may be able to just write the macro to simulate the feature well enough to get the code working. Code relying on hard-coded new syntax has to be rewritten not to exhibit a syntax error with the older language.

The ability to define macros can really help work around bugs in existing macros. If hard-coded syntax is broken, all you can do is code around it with different syntax. If a macro is broken, you can write a macro just like it (only with a different name) or perhaps even redefine it. The code doesn't have to change at all. Eventually when upstream fixes the bug, the workaround is removed and that is that.

Since macros are ordinary identifiers, they can be namespaced. Just because you introduce a new control construct called wonk doesn't mean that wonk is now a reserved token in a syntax, which breaks everyone who used wonk as a variable name. Syntax-in-a-package (rather than in a global parser) is awesome!

If Python had macros, Python 2 to 3 migration would be a non-issue. It wouldn't be a topic of discussion that non-Python-programmers know about.