Help getting next sheet

[cottage6]

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

 

[Per Jessen]

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

 

[Mike H]

Hi,

The reason you aren't getting the next worksheet is because your code is
terminating as soon as it encounters either of the 2 named worksheets so try
this instead

For Each wks In ActiveWorkbook.Worksheets
If wks.Name = "Summary" Or wks.Name = "Lead" Then
GoTo label
End If

'Your code


Label:
next ws

Looking at your code I'm a bit confused as to what you are trying to do.
The for - next counter loop is doingthe same thing 15 times and I suspect
you don't want that and the with - end with statement is doing nothing. Your
code could be built on but you may be better explaining in words exactly what
you are trying to do.


Mike

 

[Don Guillett]

Sub GetSheetsDon()
For Each wks In ActiveWorkbook.Worksheets
With wks
If .Name <> "Summary" And .Name <> "Lead" Then
MsgBox .Name
'I can't figure out what you want to do with each sheet. Please EXPLAIN


End If
End With
Next wks
End Sub

 

[john]

like most other respondants, not really too sure what you are trying to do
but gave it a stab with following:

not tested.

Sub GetSheets()
Application.ScreenUpdating = False
For Each wks In ActiveWorkbook.Worksheets

Select Case wks.Name

Case Is = "Summary", "Lead"
'do nothing

Case Else

With Worksheets(wks.Name)
myName = .Name
'MsgBox myName
'Range("A1").Select

For counter = 1 To 15
If .Range("A" & counter).Value = "Application" Then
With .Range("A" & counter)
myRange1 = .Offset(0, 2).Value
myRange2 = .Offset(1, 2).Value
myRange3 = .Offset(1, 4).Value
myRange4 = .Offset(1, 6).Value 'activity
myRange5 = .Offset(2, 2).Value
myRange6 = .Offset(3, 2).Value
myRange7 = .Offset(4, 2).Value
End With

With Worksheets("Sheet1")
Set SRng = .Cells(Rows.Count, 1).End(xlUp)(2)
With .Range(SRng)
.Value = myName 'Sheet Name
.Offset(0, 1).Value = myRange1 'Application
.Offset(0, 2).Value = myRange2 'Business
Process
.Offset(0, 3).Value = myRange3 'Sub Process
.Offset(0, 4).Value = myRange4 'Activity
.Offset(0, 5).Value = myRange5 'Sub System
.Offset(0, 6).Value = myRange6 'Test Number
.Offset(0, 7).Value = myRange7 ' Objective
End With
End With
End If
Next counter
End With
End Select
Next wks
Application.ScreenUpdating = True
End Sub

 

[cottage6]

Thanks for all the quick input; sorry if I wasn't very clear.
This is the
layout I've been given; I need any
data after the : such as "Accounts Payable" to be pasted into the Summary
Sheet for each sheet in the workbook, as well as the sheet name. The counter
loop got put in after I was informed the users probably had deleted rows in
some of the files.
A8 C8
Application: Accounts Payable
A9 C9 D9 E9
E9 Business Process: Purchase to Pay Business Sub-Process: All
F9 G9
Business Activity: Verify Batch
A10 C10
Lawson Sub-System: Accounts Payable
A11 C11
Test Number: AP520.1
A12 C12
Test Objective: Create a job for the Batch Release AP520.1

 

[cottage6]

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