Clear contents of cells

[trebor1956]

Hi,

I have a macro to clear cell contents. However, when I create a copy of the workbook and change the names of the worksheets the sheet names within the macro are not updated so it fails.Is there a way to have the same generic macro in all my related workbooks that will clear cell contents (identical cell range) even when the worksheet name has changed. In this case the worksheet name has the month year (mmm yyyy) within its name.
To try and explain further, I have a workbook for Jan 2018 with sheets called 'nn Jan 2018' etc. where nn is a persons initial. At the end of the month I will then copy the workbook, rename the sheets (via a macro) to 'nn Feb 2018'. There will be entries within cell range D7:E41 that will need clearing so I was hoping to have a generic macro that would do this even when the sheet names change.

many thanks,
trebor1956

 

[My Aswer Is This]

If you want a generic code that will clear the same range on all your sheets no matter their name.
Try this:

Sub Clear_Range_All_Sheeets()
'Modified 4-2-18 5:40 PM EDT
Dim i As Long
Application.ScreenUpdating = False
    For i = 1 To Sheets.Count
        Sheets(i).Range("D7:E41").ClearContents
    Next
Application.ScreenUpdating = True
End Sub

 

[trebor1956]

Thanks,
Unfortunately its not all of the sheets.

 

[My Aswer Is This]

Thanks,
Unfortunately its not all of the sheets.

Well tell me what sheets

Can you say sheets 10 to 25 or 4 to 54

 

[My Aswer Is This]

Work with me here. If you want a generic script then I need a generic range of sheets.

 

[My Aswer Is This]

And I can write you a script to rename all your sheets each month. Without you having to always enter all your old sheet names and how you want all your new sheets name to look.

Show me a example of how you want your sheets named for April 2018

Do they look like this:

nnn-Apr-01-2018 and then nnn-Apr-02-2018

 

[trebor1956]

Hi,
I can ensure that the sheets that need the cells clearing are sheets 1, 2, 3, 4 and 5

The sheet names for April will look like:

Summary Apr 2018

Dates Apr 2018

RF Apr 2018

DW Apr 2018

AE Apr 2018

SM Apr 2018

JC Apr 2018

RF summ Apr 2018

DW summ Apr 2018

AE (wrkng) Apr 2018

AE summ Apr 2018

SM (wrkng) Apr 2018

SM summ Apr 2018

JC (wrkng) Apr 2018

JC summ Apr 2018 Many thanks for your help

<tbody>

</tbody>

 

[trebor1956]

Hi,

Was the info. I replied with OK for you?

 

[My Aswer Is This]

Try this:

Sub Clear_Range_All_Sheeets()
'Modified 4-4-18 11:40 AM EDT
Dim i As Long
Application.ScreenUpdating = False
    For i = 1 To 5
        Sheets(i).Range("D7:E41").ClearContents
    Next
Application.ScreenUpdating = True
End Sub

 

[trebor1956]

Try this:

Sub Clear_Range_All_Sheeets()
'Modified 4-4-18 11:40 AM EDT
Dim i As Long
Application.ScreenUpdating = False
    For i = 1 To 5
        Sheets(i).Range("D7:E41").ClearContents
    Next
Application.ScreenUpdating = True
End Sub

That's great thanks. Any luck with the macro to

rename all your sheets each month. Without you having to always enter all your old sheet names and how you want all your new sheets name to look?
​

 

[My Aswer Is This]

In this example of yours:

Summary Apr 2018

Dates Apr 2018

RF Apr 2018

<tbody>
</tbody>

Is the word "Summary" some place on the sheet?

For example do you have Summary in Range(A1) of the sheet named Summary Apr 2018

And

RF in Range(A1) of sheet named

RF Apr 2018​

​

<strike></strike>