Clear contents of cells

T

trebor1956

Board Regular
Joined
Jul 2, 2015
Messages
100
Office Version
  1. 2013
Platform
  1. Windows
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
 

Excel Facts

Format cells as date
Select range and press Ctrl+Shift+3 to format cells as date. (Shift 3 is the # sign which sort of looks like a small calendar).
Sort by date Sort by votes
trebor1956 said:
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
Click to expand...

Sorry, I don't know what you mean by 'code tags'.
 
Upvote 0
trebor1956 said:
Sorry, I don't know what you mean by 'code tags'.
Click to expand...
After you paste your code into the reply window for your response, select it (if it is not already selected) and click the button with the # sign on it (located in the reply window's ribbon)... that will place a [/code] tag at the end of your selection and a
Code: 
[/COLOR][/B] tag at the beginning of your selection (you could also simply type those in manually if you wanted)... doing that will post your code in a "code box" which will preserve all text formatting (indents, multiple spaces, etc.) making it easier for us to read.
 
Last edited:
Upvote 0
I'm really sorry but I don't seem to be able to paste the copied macro into the reply.
 
Upvote 0
Try using a different browser other than Edge.
 
Upvote 0
If your using MSEdge and are not able to paste code in this forum do this:
Copy code and paste into NotePad
Then copy code from Notepad and paste into this forum
MS is aware of this problem and are working on a solution.
 
Upvote 0
MARK858 said:
Try using a different browser other than Edge.
Click to expand...

I'm really sorry but I am completely at a loss. I have no idea how to let you see the macro I am currently using. If you are able to code a completely separate one that simply clears contents that that will do me.
 
Upvote 0
If you aren't using Edge then you literally copy and paste it in the thread then preferably select the code pasted and click the # icon.
 
Upvote 0
My Aswer Is This said:
If your using MSEdge and are not able to paste code in this forum do this:
Copy code and paste into NotePad
Then copy code from Notepad and paste into this forum
MS is aware of this problem and are working on a solution.
Click to expand...

Sub renames()
'
' renames Macro
'
'
''put available sheetname in Column A and Against it (Column B) put revised sheetname
Dim i As Long
Dim lstRow As Long

lstRow = Cells(Rows.Count, "A").End(xlUp).Row
For i = 1 To 15
Worksheets(Cells(i, "A").Value).Name = Cells(i, "B").Value
Next
Application.ScreenUpdating = True
End Sub
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question
Clear contents daily
Replies
1
Views
181
alansidman
A
VinceF
  • Solved
Clear Contents From 2 Sheets
Replies
8
Views
228
VinceF
VinceF
N
  • Question
Macro to Clearing Contents of Row affect Validation Lists
Replies
0
Views
208
Nathan Asius
N
PuntingJawa
  • Question
Clear contents in multiple sheets on same workbook macro
Replies
2
Views
89
PuntingJawa
PuntingJawa
G
  • Question
Macro Code Help for multiple worksheets.
Replies
1
Views
176
Georgiboy
Georgiboy

Forum statistics

Threads
1,214,979
Messages
6,122,557
Members
449,088
Latest member
davidcom

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top