Thank You, Thank You, Thank You; you are indeed a prince. Works great!

"Per Jessen" wrote:

Hi

When you use "With wks" you have to put a dot before every statement which
should refer to "wks". I would rather skip the sheet than exit the macro if
the sheet name is Summary or Lead.

Sub GetSheets()
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets
'With wks
wks.Select
If wks.Name < "Summary" And wks.Name < "Lead" Then
myName = ActiveSheet.Name
MsgBox myName
Range("A1").Select
For Counter = 1 To 15
If ActiveCell = "Application" Then
myRange1 = ActiveCell.Offset(0, 2)
myRange2 = ActiveCell.Offset(1, 2)
myRange3 = ActiveCell.Offset(1, 4)
myRange4 = ActiveCell.Offset(1, 6) 'activity
myRange5 = ActiveCell.Offset(2, 2)
myRange6 = ActiveCell.Offset(3, 2)
myRange7 = ActiveCell.Offset(4, 2)
Sheets("Sheet1").Select

Set SRng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2)
SRng.Select
ActiveCell = myName 'Sheet Name
ActiveCell.Offset(0, 1) = myRange1 'Application
ActiveCell.Offset(0, 2) = myRange2 'Business Process
ActiveCell.Offset(0, 3) = myRange3 'Sub Process
ActiveCell.Offset(0, 4) = myRange4 'Activity
ActiveCell.Offset(0, 5) = myRange5 'Sub System
ActiveCell.Offset(0, 6) = myRange6 'Test Number
ActiveCell.Offset(0, 7) = myRange7 ' Objective

Else
ActiveCell.Offset(1, 0).Select
End If
Next Counter
End If
'End With
Next wks
Application.ScreenUpdating = True
End Sub

Regards,
Per

"cottage6" skrev i meddelelsen
...
Hello,
I have many files with varying sheet names. I need to get each sheet in
the
file and copy certain data to a Summary Sheet, including the sheet name.
I
have this working kind of, but I need to ignore the Summary Sheet in the
"For
each wks" statement, and my code is not getting the next sheet after the
first one. Also, a sheet I've renamed still gives me the sheet name
"Sheet2"
for example. Tom Hutchins was kind enough to reply and from that I got the
"Exit Sub" idea. Anyway, my code is posted below if anyone can help.
This
is just really messed up. Please help; I'm near suicide.
Sub GetSheets()
For Each wks In ActiveWorkbook.Worksheets
With wks
If wks.Name = "Summary" Or wks.Name = "Lead" Then
Exit Sub
End If

myName = ActiveSheet.Name
MsgBox myName
Range("A1").Select
For Counter = 1 To 15
If ActiveCell = "Application" Then
myRange1 = ActiveCell.Offset(0, 2)
myRange2 = ActiveCell.Offset(1, 2)

myRange3 = ActiveCell.Offset(1, 4)
myRange4 = ActiveCell.Offset(1, 6) 'activity
myRange5 = ActiveCell.Offset(2, 2)
myRange6 = ActiveCell.Offset(3, 2)
myRange7 = ActiveCell.Offset(4, 2)
Sheets("Sheet1").Select
Set SRng = ActiveSheet.Cells(Rows.Count, 1).End(xlUp)(2)
SRng.Select
ActiveCell = myName 'Sheet Name
ActiveCell.Offset(0, 1) = myRange1 'Application
ActiveCell.Offset(0, 2) = myRange2 'Business Process
ActiveCell.Offset(0, 3) = myRange3 'Sub Process
ActiveCell.Offset(0, 4) = myRange4 'Activity
ActiveCell.Offset(0, 5) = myRange5 'Sub System
ActiveCell.Offset(0, 6) = myRange6 'Test Number
ActiveCell.Offset(0, 7) = myRange7 ' Objective

Else
ActiveCell.Offset(1, 0).Select
End If
Next Counter
End With
Next wks
End Sub