Worksheet changing names to workbook name

[Dummy Excel]

Hi All,
I have created a macro to format some raw data from our system. When we receive the data from our system it always has a new file & worksheet name. So to get around this in the beginning i use the below code and then call the workbook/worksheet when required;

    j = ActiveWorkbook.Name
    K = ActiveSheet.Name

At the end of the report I rename my worksheet (k) to something more meaningful using the code below;

Sheets(K).Name = "Dogs"

This all works great until I go and save my formatted report. Please see code below to save the report, although just before it saves the report, it changes the worksheet name from Dogs to Dogs OOS 170611

    filedate = InputBox("Enter today's date as ddmmyy", , "010111")
    Filename = "Dogs OOS " & filedate
    ActiveWorkbook.SaveAs Filename:="C:\Pets OOS Reports - Dogs\" & Filename & ".xls"

Any thoughts/help would be greatly appreciated
regards
Sam

 

[tweedle]

Try this:

'filedate = Format(InputBox("Enter today's date as ddmmyy", , Date), "ddmmyy")
    filedate = Format(Date, "ddmmyy")
    myFilename = "Dogs OOS " & filedate
    ActiveWorkbook.SaveAs Filename:="C:\Pets OOS Reports - Dogs\" & myFilename & ".xls"

"Filename" is a reserved/keyword in VBA.

 

[Dummy Excel]

This still did the same thing

 

[tweedle]

So to restate the issue:
A:You are not getting any error messaging
B:On the SaveAs though, it changes the worksheet name to the workbook name.
The posted code would not cause B: to occur.

Is there anything in a Sub Workbook_BeforeSave event?
I might put a break (Select the code line and F9) on the SaveAs, and then F8 from there and see if it takes you through any "Sheets(K).Name = " code.