The FOR EACH loop that I show below works terrific for going thru ALL tabs
and performing an operation on each one. I would like to keep the same FOR
EACH statement and be able to exclude say Sheet75 and Sheet85 from that loop.
Are there any suggestions, your help is much appreciated. Thx - Dean

Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Dim wks As Worksheet
For Each wks In Worksheets
wks.Select
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
wks.Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
Sheets("Complete list").Select
Range("A1").Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByRows, xlPrevious).Row + 2
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByColumns, xlPrevious).Column
Cells(lRealLastRow, 1).Select
ActiveSheet.Paste
Next wks

As long as those two sheets have not been renamed try this:

For Each wks In Worksheets
If wks.Name < "Sheet75" And wks.Name < "Sheet85" Then
....
End If
Next wks


"Dean" wrote:

The FOR EACH loop that I show below works terrific for going thru ALL tabs
and performing an operation on each one. I would like to keep the same FOR
EACH statement and be able to exclude say Sheet75 and Sheet85 from that loop.
Are there any suggestions, your help is much appreciated. Thx - Dean

Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Dim wks As Worksheet
For Each wks In Worksheets
wks.Select
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
wks.Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
Sheets("Complete list").Select
Range("A1").Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByRows, xlPrevious).Row + 2
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByColumns, xlPrevious).Column
Cells(lRealLastRow, 1).Select
ActiveSheet.Paste
Next wks

Charlie,
Great response. One issue I'm thinking of, the names of the Sheets are not
Sheet75 and Sheet85, that is their code names. Would the < statement
change then, that is to use the Code Names instead of Sheet names? Thx
again. Dean

"Charlie" wrote:

As long as those two sheets have not been renamed try this:

For Each wks In Worksheets
If wks.Name < "Sheet75" And wks.Name < "Sheet85" Then
...
End If
Next wks


"Dean" wrote:

The FOR EACH loop that I show below works terrific for going thru ALL tabs
and performing an operation on each one. I would like to keep the same FOR
EACH statement and be able to exclude say Sheet75 and Sheet85 from that loop.
Are there any suggestions, your help is much appreciated. Thx - Dean

Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Dim wks As Worksheet
For Each wks In Worksheets
wks.Select
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
wks.Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
Sheets("Complete list").Select
Range("A1").Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByRows, xlPrevious).Row + 2
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByColumns, xlPrevious).Column
Cells(lRealLastRow, 1).Select
ActiveSheet.Paste
Next wks

Yes, in fact I never reference the sheet objects (Sheet1, Sheet2, etc.)
directly. I prefer to reference them as Sheets("Sheet1") or Sheets("Whatever")

In your case put in the real sheet names in the quotes:

If wks.Name < "MySheet75" ... Then


"Dean" wrote:

Charlie,
Great response. One issue I'm thinking of, the names of the Sheets are not
Sheet75 and Sheet85, that is their code names. Would the < statement
change then, that is to use the Code Names instead of Sheet names? Thx
again. Dean

"Charlie" wrote:

As long as those two sheets have not been renamed try this:

For Each wks In Worksheets
If wks.Name < "Sheet75" And wks.Name < "Sheet85" Then
...
End If
Next wks


"Dean" wrote:

The FOR EACH loop that I show below works terrific for going thru ALL tabs
and performing an operation on each one. I would like to keep the same FOR
EACH statement and be able to exclude say Sheet75 and Sheet85 from that loop.
Are there any suggestions, your help is much appreciated. Thx - Dean

Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Dim wks As Worksheet
For Each wks In Worksheets
wks.Select
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
wks.Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
Sheets("Complete list").Select
Range("A1").Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByRows, xlPrevious).Row + 2
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByColumns, xlPrevious).Column
Cells(lRealLastRow, 1).Select
ActiveSheet.Paste
Next wks

Charlie,
I tried the statement Sheet75.name, it worked perfectly for Code Name use.
Thx so much for your help. This is great ! Dean

"Dean" wrote:

Charlie,
Great response. One issue I'm thinking of, the names of the Sheets are not
Sheet75 and Sheet85, that is their code names. Would the < statement
change then, that is to use the Code Names instead of Sheet names? Thx
again. Dean

"Charlie" wrote:

As long as those two sheets have not been renamed try this:

For Each wks In Worksheets
If wks.Name < "Sheet75" And wks.Name < "Sheet85" Then
...
End If
Next wks


"Dean" wrote:

The FOR EACH loop that I show below works terrific for going thru ALL tabs
and performing an operation on each one. I would like to keep the same FOR
EACH statement and be able to exclude say Sheet75 and Sheet85 from that loop.
Are there any suggestions, your help is much appreciated. Thx - Dean

Dim lRealLastRow As Long
Dim lRealLastColumn As Long
Dim wks As Worksheet
For Each wks In Worksheets
wks.Select
LastRow = ActiveSheet.UsedRange.Row - 1 +
ActiveSheet.UsedRange.Rows.Count
wks.Select
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Copy
Sheets("Complete list").Select
Range("A1").Select
On Error Resume Next
lRealLastRow = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByRows, xlPrevious).Row + 2
lRealLastColumn = Cells.Find("*", Range("A1"), xlFormulas, ,
xlByColumns, xlPrevious).Column
Cells(lRealLastRow, 1).Select
ActiveSheet.Paste
Next wks