Pastas de trabalho de empresas costumam ter uma planilha por região, loja ou mês, além de uma planilha Summary que alguém refaz à mão. Uma macro pode percorrer todas as planilhas por você:
For Each ws In Worksheets ... Next ws
Dentro do loop, ws é a planilha que está sendo visitada. ws.Name é o nome da guia dela, então If ws.Name <> "Summary" Then pula o próprio resumo.
Qualifique cada referência com a planilha a que ela pertence. ws.Range("C2:C9") lê a planilha do loop, enquanto um Range("C2:C9") sem qualificação lê a planilha que estiver ativa, que é o erro mais comum nessas macros. O mesmo vale para a última linha: ws.Cells(ws.Rows.Count, 1).End(xlUp).Row dá a cada planilha o próprio tamanho.
Uma macro de resumo é executada de novo no mês seguinte, então deixe essa reexecução limpa. Limpe primeiro a saída antiga com Range(...).ClearContents e depois escreva as linhas novas. Sem isso, uma região que fechou continua no resumo.
Cada volta lê a planilha do loop, com a última linha própria dessa planilha.