I wrote all that? lol Now I'm confused! I do have a macro that clears all
cells, so I must have copy and pasted the wrong code...sorry!

I cannot thank you enough for sticking with me through this whole mess. I
am so thankful and extremely impressed with your desire to help someone out
who obviously is such an amateur!

I love this stuff. I just don't get to play with it often enough to really
learn it.

Thanks again Dave,
Cheri

"Dave Peterson" wrote:

I'm confused. The code you posted at the bottom didn't copy|paste. It just
cleared cells.

Option Explicit
Sub ClearSheets2()
Dim wks As Worksheet
For Each wks In ActiveWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("team"), LCase("stats")
'skip it
Case Else
wks.Range("A6:P23").ClearContents
End Select
Next wks
End Sub

If you wanted to paste a range (from Stats????) to every other worksheet except
for Stats and Team, then...


Option Explicit
Sub CopySheets()
Dim wks As Worksheet
Dim RngToCopy as range

with activeworkbook.worksheets("Stats")
set rngtocopy = .range("a1:d1")
end with

For Each wks In ActiveWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("team"), LCase("stats")
'skip it
Case Else
rngtocopy.copy _
destination:=wks.Range("A1")
End Select
Next wks
End Sub

Combining the two...

Option Explicit
Sub CopyAndClearSheets()
Dim wks As Worksheet
Dim RngToCopy as range

with activeworkbook.worksheets("Stats")
set rngtocopy = .range("a1:d1")
end with

For Each wks In ActiveWorkbook.Worksheets
Select Case LCase(wks.Name)
Case Is = LCase("team"), LCase("stats")
'skip it
Case Else
rngtocopy.copy _
destination:=wks.Range("A1")
wks.Range("A6:P23").ClearContents
End Select
Next wks
End Sub

Cheri wrote:

Hi Per,

This was very helpful and I can see how it should work. The problem is that
I do not want any data copied to worksheet1 (named Team) nor worksheet2
(named Stats). It seems that the code I have wants to copy the data from the
STATS worksheet (which is what I want) but it also wants to paste the data
back into the STATS worksheet, which I of course do not want.

The code I input is:

Range("A5:P22").Select
Selection.Copy
For Each sh In ThisWorkbook.Sheets
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A1:D1").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Stats").Select
Range("A1:D1").Select
Application.CutCopyMode = False

I apparently need to somehow skip the STATS worksheet but I don't know
exactly what is missing in this code.

Can you help?

Thanks!

"Per Jessen" wrote:

Hi Cherie

This macro will loop through all worksheets in the active workbook.
I'll leave it to you to fill in the missing code :-)

For Each sh In ThisWorkbook.Sheets
'Here goes your code
Next

Best regards,
Per


On 11 Apr., 23:45, Cheri wrote:
I had requested information on how to name the tabs in a worksheet from a
list. I got a great response:

Sub namesheets()
For i = 1 To 18
Sheets("sheet" & i).Name = Cells(i, "a")
Next i
End Sub

Now what I need to know how to do is change my macro that copies from one
sheet to the other to adjust to whatever the new tab name is.

My macro currently is:

Sub ClearSheets()
'
' ClearSheets Macro
' Macro recorded 6/2/2007 by n0082840
'

'
Sheets(Array("Cutter", "Emrick", "Goble", "Heiser", "Keigley",
"Leonard", "Newcomb", _
"Nobles", "Patton", "Riley", "Steinike", "Watkins", "Woodhall",
"Template")).Select
Sheets("Cutter").Activate
Range("A6:P23").Select
Selection.ClearContents
Range("A1:D2").Select
Sheets("Stats").Select
Range("A1:D1").Select
End Sub

Needless to say, this won't work with new names for each worksheet. What
VBA code do I need to have it merely select the next worksheet regardless of
the name?

Thanks ahead of time!!
Cheri



--

Dave Peterson