Copy Range Data Macro

[Cheri]

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("A623").Select
Selection.ClearContents
Range("A12").Select
Sheets("Stats").Select
Range("A11").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

 

[Per Jessen]

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

 

[Dave Peterson]

Check your previous thread, too.

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("A623").Select
Selection.ClearContents
Range("A12").Select
Sheets("Stats").Select
Range("A11").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

 

[Cheri]

I'm sorry. Maybe I shouldn't have started another thread. I thought I was
supposed to since it was a different topic...copying data from the Stats
worksheet to all the newly named worksheets.

I have also asked two more questions on my other thread. I hope you won't
mind continuing to help me as you have been so helpful!!!

Cheri

 

[Cheri]

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("A522").Select
Selection.Copy
For Each sh In ThisWorkbook.Sheets
Range("A6").Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone,
SkipBlanks _
:=False, Transpose:=False
Range("A11").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Stats").Select
Range("A11").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!

 

[Dave Peterson]

My mistake. Sorry.

I saw the top of your post and thought you were asking a followup.

I should have continued reading the rest of your message.

 

[Dave Peterson]

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("A623").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("A623").ClearContents
End Select
Next wks
End Sub

 

[Per Jessen]

Hi Cheri
This coce will skip sheets named Stats And Team.

Sheets("Stats").Range("A522").Copy

For Each sh In ThisWorkbook.Sheets
If sh.Name <> "Stats" And sh.Name <> "Team" Then
Sheets(sh.Name).Select
Range("A6").PasteSpecial Paste:=xlPasteValues,
Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Range("A11").Select
ActiveWindow.ScrollWorkbookTabs Sheets:=-1
Sheets("Stats").Select
Range("A11").Select
Application.CutCopyMode = False
End If
Next

Regards,
Per

 

[Chersie]

I cannot thank you enough!!! You are amazing and I really appreciate your
help! I get amazed at how you guys know all of this stuff. Very impressive
and to you it is probably a simple request To me it was mind boggling!

Sincerely,
Cheri

 

[Chersie]

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