В макросе?    
For each sh in workbook.sheets  
if sh.name<>"название главного листа" then  
... 'копируете что надо  
end  
next

sheets("название главного листа").[D1] = sh.name
 
(это внутри цикла)

Option Explicit  
Sub tt()  
 
Dim sh As Object  
 
For Each sh In ThisWorkbook.Sheets  
If sh.Name <> "Сводная" Then  
sh.Range("A1:AK37").Copy Sheets("Сводная").Range("L28")  
End If  
Next  
End Sub

для обязательного объявления переменных. Иначе программа ругаться будет, что какая-либо переменная у вас не объявлена.    
 
Пример объявления переменных  
Dim Stroka As String  
Dim m As Long  
Dim выручка As Single  
 
и т.д.

{quote}{login=Паша}{date=28.05.2010 04:46}{thema=}{post}Sub one()  
 
Dim sh As Sheets  
 
For Each sh In Workbook.Sheets  
If sh.Name <> "Сводная" Then  
Range("A1:AK37").Select  
   Selection.Copy  
   Sheets("Ñâîäíàÿ").Select  
   Range("L28").Select  
   ActiveSheet.Paste  
End If  
Next  
End Sub  
 
 
 
сделал такой макрос который копирует всё в одно место что бы посмоотреть F8 как он вставляет попеременно всё, выдаёт ошибку Метод ор дата мембер нот фаунд и выделяет строку с IF. А если не прописывать Dim sh as shеet то выдаёт Object Required и выделяет первую строку For each.... И ещё к End приписал If так как выдаёт Next whithout For{/post}{/quote}  
 
просто вы обявили не тот тип    
 
 
sheets - это коллекция листов, а вам нужно тип элементов этой коллекции, но и тут может быть засада - объекта sheet не существует, а если использовать worksheet , то не все листы могут быть рабочими, встречаются меж ними и листы диаграмм, макросов и т.п. так что использование универсального обявления object в данном случае самое оно, но я к тому, что можно вообще не объявлять тип, тогда транслятор сам присвоит тип вариант, кот очень даже прокатит..

1. Опять: Dim sh As Object  
2. И где задано имя этого sh?  
 
 
Sub Макрос1()  
Columns("A").Insert  
Sheets("Сводный отчёт").[A1] = ActiveSheet.Name
End Sub