Run Macro in All Worksheets Except One

[Ramiro]

I need help with a Macro that runs in all sheets except one called "Data", that in all the other sheets changes all the formulae to values.


Thanks in Advance.

 

[hopstr]

Link to the workbook https://www.dropbox.com/s/8ci4lkdmulhxmxd/Employee Hours 2018.xlsm?dl=0

My apologies, the code below.

Sub test()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "TEMPLATE" Then


    On Error Resume Next
    If Len(ws.Range("A2")) > 0 Then
        ws.Name = Replace(ws.Range("A2").Value, "/", "-")


  End If
Next ws
End Sub

 

[hiker95]

hopstr,

I have had problems in the past when attempting to download an Excel file with macros, with the xlsm file extension.

Please remove all macros, and, then rename the workbook using the xlsx file extension, and, then repost on dropbox.


Then post all of your macro code in your next reply.

When posting VBA code, please use Code Tags - like this:

[code=rich]

'Paste your code here.

[/code]

 

[hopstr]

My apologies for the late reply. Here's the link with the xlsx extension.

https://www.dropbox.com/s/cfd9xomnx37icpv/Employee Hours 2018.xlsx?dl=0

I'm trying to get the tabs on a spreadsheet to update to the last day on a pay period except the TEMPLATE tab. Could anyone instruct me on how to fix this?
I'm having issues with the following code:

Sub TabName()
Dim ws As Worksheet
For Each ws In Worksheets
    On Error Resume Next
    If Len(ws.Range("A2")) > 0 Then
        ws.Name = Replace(ws.Range("A2").Value, "/", "-")
    End If
    On Error GoTo 0
    If ws.Name <> Replace(ws.Range("A2").Value, "/", "-") Then
        MsgBox ws.Name & " Was Not renamed, the suggested name was invalid"
    End If
Next
End Sub




Option Explicit
Sub test()
' hiker95, 10/29/2013
' http://www.mrexcel.com/forum/excel-questions/735788-run-macro-all-worksheets-except-one.html
Dim ws As Worksheet
For Each ws In ThisWorkbook.Worksheets
  If ws.Name <> "TEMPLATE" Then


    On Error Resume Next
    If Len(ws.Range("A2")) > 0 Then
        ws.Name = Replace(ws.Range("A2").Value, "/", "-")


  End If
Next ws
End Sub

 

[hiker95]

hopstr,

Thanks for the latest workbook/worksheets.

On all of your raw data worksheets

worksheets have the following name formats 3-10-2018
cell A2 displays 03-10-18
cell A2 contains the following formula
=O2

1. Are you trying to change the format of cell A2 to 03/10/2018

2. Or, are you trying to change the worksheet name to 03/10/2018

You can not have a worksheet name that contains/displays the / character.

 

[hopstr]

I would like the change the worksheet name to 03-10-2018 by running a macro. I read on another forum that I can run a macro that will change the / to a - which I believe I accomplished in the Sub TabName() macro. But when I run this macro, it changes the name on the TEMPLATE worksheet to be the date in cell A2 (01-13-1900). I would like the TEMPLATE worksheet to remain titled TEMPLATE so when the client creates a copy of this worksheet for another pay period the new worksheet's title will change to the date in cell A2 with the first macro correcting the / to a -

The second macro, Sub test(), I was hoping would keep the worksheet TEMPLATE titled as TEMPLATE. But when I run the macro I receive the Next without For error. I don't know what this error means.

 

[hiker95]

hopstr,

I just downloaded your latest raw data workbook again. The data across worksheets is not consistent?????


The TEMPLATE sheet:
cell A2 contains 1/13/1900
cell O2 contains 1/13/1900


Sheet 3-10-2017 sheet:
cell A2 contains 03-10-18
cell O2 contains 03-10-18


Sheet 3-24-1018 sheet:
cell A2 contains 03-24-18
cell O2 contains 03/24/2018


The 4-7-2018 sheet:
cell A2 contains 04-07-18
cell O2 contains 04/07/2018


The 4-21-2018 sheet:
cell A2 contains 04-21-18
cell O2 contains 04/21/2018


The 5-5-2018 sheet:
cell A2 contains 5/5/2018
cell O2 contains 5/5/2018


The 5-19-2018 sheet:
cell A2 contains 5/19/2018
cell O2 contains 5/19/2018



Maybe someone else on MrExcel will be able to assist you.