ever changing worksheet tab

[Trevor3007]

good day ,

I have a workbook with several tabs . I created a macro that will 'blank' applicable cells & then prompt a message to insert a date. below is the code:-
Sub blanksheetsall()
'
' blanksheetsall Macro
'
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 6
Sheets("Overtime").Select
Range("B9:B15").Select
Selection.ClearContents
Range("C9:C15").Select
Selection.ClearContents
Sheets("Expenses").Select
Range("C6:D12").Select
Selection.ClearContents
Range("E6:E12").Select
Selection.ClearContents
Range("G6").Select
ActiveCell.FormulaR1C1 = "0"
Range("G7").Select
ActiveCell.FormulaR1C1 = "0"
Range("G8").Select
ActiveCell.FormulaR1C1 = "0"
Range("G9").Select
ActiveCell.FormulaR1C1 = "0"
Range("G10").Select
ActiveCell.FormulaR1C1 = "0"
Range("G11").Select
ActiveCell.FormulaR1C1 = "0"
Range("G12").Select
ActiveCell.FormulaR1C1 = "0"
Range("J6").Select
ActiveCell.FormulaR1C1 = "0"
Range("J7").Select
ActiveCell.FormulaR1C1 = "0"
Range("J8").Select
ActiveCell.FormulaR1C1 = "0"
Range("J9").Select
ActiveCell.FormulaR1C1 = "0"
Range("J10").Select
ActiveCell.FormulaR1C1 = "0"
Range("J11").Select
ActiveCell.FormulaR1C1 = ""
Range("J11").Select
ActiveCell.FormulaR1C1 = "0"
Range("J12").Select
ActiveCell.FormulaR1C1 = ""
Range("J12").Select
ActiveCell.FormulaR1C1 = "0"
Range("K6:K12").Select
Selection.ClearContents
Sheets("Quick Calc-For NASA").Select
Range("A6:G11").Select
Selection.ClearContents
Sheets("TMcL-TSheet DXC WC 11-12-2017").Select
ActiveWindow.ScrollRow = 5
ActiveWindow.ScrollRow = 4
ActiveWindow.ScrollRow = 3
ActiveWindow.ScrollRow = 2
ActiveWindow.ScrollRow = 1
Range("D3").Select
Selection.ClearContents
MsgBox "Enter Applicable WC Date"
End Sub

The above works great until the tab name is changed and the debug highlights the following:-
Sheets("TMcL-TSheet DXC WC 11-12-2017").Select

This is because the file name is changed based on as shown in the tab name "TMcL-TSheet DXC WC 11-12-2017".

How do I get this to run without smoothly?

Many thanks for your all help with my issue.

KR
Trevor3007

 

[MARK858]

Try using the sheets codename the name outside the brackets when you look at the sheet in the VBE properties window.

It will look something like Sheet1 (TMcL-TSheet DXC WC 11-12-2017) and you would use it as Sheet1.Select or as per the red lines in the slightly tidied code below (obviously amend Sheet1 to your sheets codename).

Sub blanksheetsall()

    Sheets("Overtime").Range("B9:B15").ClearContents
    Sheets("Overtime").Range("C9:C15").ClearContents
    
    With Sheets("Expenses")
        .Range("C6:D12").ClearContents
        .Range("E6:E12").ClearContents
        .Range("G6").Value = "0"
        .Range("G7").Value = "0"
        .Range("G8").Value = "0"
        .Range("G9").Value = "0"
        .Range("G10").Value = "0"
        .Range("G11").Value = "0"
        .Range("G12").Value = "0"
        .Range("J6").Value = "0"
        .Range("J7").Value = "0"
        .Range("J8").Value = "0"
        .Range("J9").Value = "0"
        .Range("J10").Value = "0"
        .Range("J11").Value = ""
        .Range("J11").Value = "0"
        .Range("J12").Value = ""
        .Range("J12").Value = "0"
        .Range("K6:K12").ClearContents
    End With
    Sheets("Quick Calc-For NASA").Range("A6:G11").ClearContents
   [COLOR="#FF0000"] Sheet1.Range("D3").ClearContents
    Application.Goto Sheet1.Range("D3")[/COLOR]
    MsgBox "Enter Applicable WC Date"
End Sub

 

[Trevor3007]

many thanks works a treat.

After I insert the applicable date into d3, I would to have the option of a yes/no to save? is this poss? The VB below works but at the moment is on a seperate macro button but on the same worksheet but would like to able to combine it into 'Sub blanksheetsall()'

'ActiveWorkbook.SaveAs Filename:="C:\Timesheets " & Application.PathSeparator & ActiveWorkbook.Sheets(1).Name

many thanks again & a very xmas too

 

[MARK858]

Maybe..

Sub blanksheetsall()
    Dim MSG1 As String

    Sheets("Overtime").Range("B9:B15").ClearContents
    Sheets("Overtime").Range("C9:C15").ClearContents

    With Sheets("Expenses")
        .Range("C6:D12").ClearContents
        .Range("E6:E12").ClearContents
        .Range("G6").Value = "0"
        .Range("G7").Value = "0"
        .Range("G8").Value = "0"
        .Range("G9").Value = "0"
        .Range("G10").Value = "0"
        .Range("G11").Value = "0"
        .Range("G12").Value = "0"
        .Range("J6").Value = "0"
        .Range("J7").Value = "0"
        .Range("J8").Value = "0"
        .Range("J9").Value = "0"
        .Range("J10").Value = "0"
        .Range("J11").Value = ""
        .Range("J11").Value = "0"
        .Range("J12").Value = ""
        .Range("J12").Value = "0"
        .Range("K6:K12").ClearContents
    End With
    Sheets("Quick Calc-For NASA").Range("A6:G11").ClearContents
    Sheet1.Range("D3").ClearContents
    MsgBox "Enter Applicable WC Date"

    MSG1 = MsgBox("Do you want to save?", vbYesNo, "Save?")

    If MSG1 = vbYes Then
        ActiveWorkbook.SaveAs Filename:="C:\Timesheets " & Application.PathSeparator & ActiveWorkbook.Sheets(1).Name
    Else
        Exit Sub
    End If

End Sub

 

[MARK858]

Actually I think
MSG1 should be dimmed as Long and not String but haven't tested as not by a computer.

 

[Trevor3007]

thank you very much. Could not get this to work? But this maybe because I have other code withn the the same workbook & there is some conflict? But should It be I fathom it out I will 'post you'.

NB
if I remove the y/n code it works great :}
Have a great xmas& all the very best..ho, ho,hoo <{}>