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

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

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

"cottage6" wrote:

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

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"cottage6" wrote in message
...
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

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

--
jb


"cottage6" wrote:

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

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


"john" wrote:

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

--
jb


"cottage6" wrote:

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

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