Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
On Mon, 29 Jun 2009 18:40:03 -0700, aussiegirlone
wrote: 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 ----------------------------------------------------------------------------- Less Spam Better enjoyable experience Visit : news://spacesst.com 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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
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 -- Regards, OssieMac |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
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 --------------- Jacob Skaria "aussiegirlone" wrote: 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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
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" wrote: 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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
OssieMac, Jacob Skaria, FSt1
I have tested all three suggestions on a new workbook, Jacob Skarias code has an error 9 come up, but OssieMac and FSt1 both work well, and its 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" wrote: 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 --------------- Jacob Skaria "aussiegirlone" wrote: 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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
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 --------------- Jacob Skaria "aussiegirlone" wrote: OssieMac, Jacob Skaria, FSt1 I have tested all three suggestions on a new workbook, Jacob Skarias code has an error 9 come up, but OssieMac and FSt1 both work well, and its 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" wrote: 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 --------------- Jacob Skaria "aussiegirlone" wrote: 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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
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 "aussiegirlone" wrote: OssieMac, Jacob Skaria, FSt1 I have tested all three suggestions on a new workbook, Jacob Skarias code has an error 9 come up, but OssieMac and FSt1 both work well, and its 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" wrote: 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 --------------- Jacob Skaria "aussiegirlone" wrote: 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 |
#9
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
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. "Jacob Skaria" wrote: 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 --------------- Jacob Skaria "aussiegirlone" wrote: OssieMac, Jacob Skaria, FSt1 I have tested all three suggestions on a new workbook, Jacob Skarias code has an error 9 come up, but OssieMac and FSt1 both work well, and its 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" wrote: 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 --------------- Jacob Skaria "aussiegirlone" wrote: 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 |
#10
Posted to microsoft.public.excel.misc
|
|||
|
|||
Combining macros
Shane
It may be true that FSt1s 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 "Shane Devenshire" wrote: 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 "aussiegirlone" wrote: OssieMac, Jacob Skaria, FSt1 I have tested all three suggestions on a new workbook, Jacob Skarias code has an error 9 come up, but OssieMac and FSt1 both work well, and its 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" wrote: 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 --------------- Jacob Skaria "aussiegirlone" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 macros - how to merge 5 macros together into one | Excel Discussion (Misc queries) | |||
Macros warning always shows up, even if all macros removed | Excel Discussion (Misc queries) | |||
Macros - copying macros from one computer to another | Excel Discussion (Misc queries) | |||
Training: More on how to use macros in Excel: Recording Macros | Excel Worksheet Functions | |||
Combining macros | Excel Discussion (Misc queries) |