Combining macros

[aussiegirlone]

Below are two macros that work just beautifully. The problem is when I use
the macro to RenameSheetsTabs, the macro ClearContents does not work unless I
edit the worksheets array and change the names in that section.
Is there a way that the RenameSheetTabs macro will also Rename the names in
the ClearContents macro or is this impossible.

ClearContentsMacro

Public Sub ClearRangeIn12Sheets()
Const csCellRef As String = "A2:I7"
Dim ws As Worksheet
For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3",
"Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10",
"Sheet11", "Sheet12"))
ws.Range(csCellRef).ClearContents
Next ws
End Sub

RenameSheetTabsMacro

Option Explicit
Sub update_all_names3()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Cells(1, 1).Value
If Err.Number <> 0 Then
MsgBox sh.Name & " wasn't renamed!"
Err.Clear
End If
On Error GoTo 0
Next sh
End Sub

 

[Lars-Åke Aspelin]

Below are two macros that work just beautifully. The problem is when I use
the macro to RenameSheetsTabs, the macro ClearContents does not work unless I
edit the worksheets array and change the names in that section.
Is there a way that the RenameSheetTabs macro will also Rename the names in
the ClearContents macro or is this impossible.

ClearContentsMacro

Public Sub ClearRangeIn12Sheets()
Const csCellRef As String = "A2:I7"
Dim ws As Worksheet
For Each ws In Worksheets(Array("Sheet1", "Sheet2", "Sheet3",
"Sheet4", "Sheet5", "Sheet6", "Sheet7", "Sheet8", "Sheet9", "Sheet10",
"Sheet11", "Sheet12"))
ws.Range(csCellRef).ClearContents
Next ws
End Sub

RenameSheetTabsMacro

Option Explicit
Sub update_all_names3()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Cells(1, 1).Value
If Err.Number <> 0 Then
MsgBox sh.Name & " wasn't renamed!"
Err.Clear
End If
On Error GoTo 0
Next sh
End Sub

If there are only 12 sheets in the workbook, i.e. you want all sheets
to be cleared, try this:

Public Sub ClearRangeIn12Sheets()
Const csCellRef As String = "A2:I7"
Dim ws As Worksheet
For Each ws In Worksheets
ws.Range(csCellRef).ClearContents
Next ws
End Sub

If there are more than 12 sheets and you only want to clear the 12
first sheets, try this:

Public Sub ClearRangeIn12Sheets()
Const csCellRef As String = "A2:I7"
Dim ws As Worksheet
For i = 1 To 12
Set ws = Worksheets(i)
ws.Range(csCellRef).ClearContents
Next i
End Sub

Hope this helps / Lars-Åke

 

[OssieMac]

The CodeName for a worksheet does not change when you change the sheet name
on the sheet tab.
To see the code names, in the VBA editor on the Project Explorer, the code
name is first and the given sheet name is in parenthesis. Before renaming any
sheet they are the same but after renaming the Code name remains unchanged.

Try the following method. I have used Select Case because it is so easy to
alter the CodeNames. Check your actual codenames agains the given names to
ensure you have them correct.

NOTE: A soace and underscoire at the end of a line is a line break in an
otherwise single line of code.

Public Sub ClearRangeIn12Sheets()
Const csCellRef As String = "A2:I7"
Dim ws As Worksheet
For Each ws In Worksheets
Select Case ws.CodeName
Case "Sheet1", "Sheet2", "Sheet3", _
"Sheet4", "Sheet5", "Sheet6", _
"Sheet7", "Sheet8", "Sheet9", _
"Sheet10", "Sheet11", "Sheet12"
ws.Range(csCellRef).ClearContents
End Select
Next ws
End Sub

 

[Jacob Skaria]

Try the below macro..which works (clear contents and rename) from sheet1 to
sheet12. Hope Range("A1") will not have characters like "\" "/" etc which are
not accepted as sheet names

Sub Macro()
Dim intTemp As Integer
For intTemp = 1 To 12
Sheets("Sheet" & intTemp).Range("A2:I7").ClearContents
Sheets("Sheet" & intTemp).Name = _
Sheets("Sheet" & intTemp).Range("A1")
Next
End Sub

If this post helps click Yes

 

[FSt1]

hi
back again. you must be working on one big project. hope this helps.
you have two subs each looping through sheets. why not combine the function
of both in to 1 sub and 1 loop i.e. rename and delete in one pass.

Option Explicit
Sub update_all_names3()
Dim sh As Worksheet
For Each sh In ActiveWorkbook.Worksheets
On Error Resume Next
sh.Name = sh.Cells(1, 1).Value
If Err.Number <> 0 Then
MsgBox sh.Name & " wasn't renamed!"
Err.Clear

else
sh.Range("A2:I7").ClearContents

End If
On Error GoTo 0
Next sh
End Sub

this is untested so my logic may be flawed. also i may not be on the same
page as you as to what you are doing. you might need to test and place the
clear contents line where it works best for you.

Regards
FSt1

 

[aussiegirlone]

OssieMac, Jacob Skaria, FSt1
I have tested all three suggestions on a new workbook, Jacob Skaria’s code
has an error 9 come up, but OssieMac and FSt1 both work well, and it’s just a
matter of choice. Such a difficult decision to make….LOL Thankyou very much
for your help, but now, can I tempt you guys with my biggest project please?

The macro I used to clear the contents of 12 sheets, is not good enough to
clear 500 sheets. This project workbook also uses the code: Sub
update_all_names3(), to name all the sheets. Can you create a code that
would skip the first 14 sheets of the workbook and clear the contents of the
following 500 sheets, array A5:AF32 adding your suggested code
sh.Range("A2:I7").ClearContents code with it.
I would appreciate this very much if you could?

 

[Jacob Skaria]

Try the below..to clear the contents from sheet 15 to end

Sub Macro()
Dim intTemp As Integer
For intTemp = 15 To ActiveWorkbook.Sheets.Count
Sheets(intTemp).Range("A5:AF32").ClearContents
Next
End Sub

If this post helps click Yes

 

[Shane Devenshire]

Hi,

Here is a slight change in logic to the macro submitted by FSt1. It looks
to me like you want to clear the range on all sheets even if the renaming of
the sheet fails?

Assuming the macro is in the activeworkbook you don't need to explicitly
state that.

Option Explicit
Sub update_all_names3()
Dim sh As Worksheet
For Each sh In Worksheets
On Error Resume Next
sh.Name = sh.[A1]
If Err.Number <> 0 Then
MsgBox sh.Name & " wasn't renamed!"
Err.Clear
End If
sh.[A2:I7].ClearContents
Next sh
End Sub

I may be wrong but I don't think the On Error GoTo 0 line serves any
function here.

 

[aussiegirlone]

My Appologies I just removed the word "contents" from
Sheets(intTemp).Range("A5:AF32").ClearContents and it does what it supposed
to do thankyou very much for your help.

 

[aussiegirlone]

Shane
It may be true that FSt1’s logic may be flawed but I see it did not impair
his code writing as his code does work well and testing your code proves to
work just the same.
So thank you all very much for your help

Hi,

Here is a slight change in logic to the macro submitted by FSt1. It looks
to me like you want to clear the range on all sheets even if the renaming of
the sheet fails?

Assuming the macro is in the activeworkbook you don't need to explicitly
state that.

Option Explicit
Sub update_all_names3()
Dim sh As Worksheet
For Each sh In Worksheets
On Error Resume Next
sh.Name = sh.[A1]
If Err.Number <> 0 Then
MsgBox sh.Name & " wasn't renamed!"
Err.Clear
End If
sh.[A2:I7].ClearContents
Next sh
End Sub

I may be wrong but I don't think the On Error GoTo 0 line serves any
function here.

--
If this helps, please click the Yes button.

Cheers,
Shane Devenshire

OssieMac, Jacob Skaria, FSt1
I have tested all three suggestions on a new workbook, Jacob Skaria’s code
has an error 9 come up, but OssieMac and FSt1 both work well, and it’s just a
matter of choice. Such a difficult decision to make….LOL Thankyou very much
for your help, but now, can I tempt you guys with my biggest project please?

The macro I used to clear the contents of 12 sheets, is not good enough to
clear 500 sheets. This project workbook also uses the code: Sub
update_all_names3(), to name all the sheets. Can you create a code that
would skip the first 14 sheets of the workbook and clear the contents of the
following 500 sheets, array A5:AF32 adding your suggested code
sh.Range("A2:I7").ClearContents code with it.
I would appreciate this very much if you could?