vba code in 365 gives error 13 in Excel 2007

[andymalan]

My Dear Learned Friends.
Can any help be forthcoming with 2 problems I have. The first is that when the workbook is opened, no worksheet Tabs are visible. I have checked in the advanced section of the application, and tabs is selected.
problem 2 follows
The following code runs fine on "365" but on a users computer it stops at " fName = Sheets("Home").Range("Q2").Value" will not save the file as the code intended.

Sub aSaveAs()
Dim MyFolder As String
Dim fName As String, ext As String
Dim TempFolder As String

 Application.DisplayAlerts = False
 
    MyFolder = Environ$("USERPROFILE") & "\Users\User\Documents\Excalibur Capital Kitchens\03_FactoryJobsAndCostings"
'C:\Users\User\Documents\Excalibur Capital Kitchens\03_FactoryJobsAndCostings
    If MyFolder = "" Then Exit Sub

    fName = Sheets("Home").Range("Q2").Value
Application.DisplayAlerts = True
    ActiveWorkbook.SaveAs Filename:=MyFolder & "\" & fName, _
         CreateBackup:=False

End Sub

I will of course be ever so grateful
kind regards
A

 

[davesexcel]

Is there an error msg?

 

[andymalan]

Is there an error msg?

Hi Dave
sorry i did not mention it, but yes , its an error 13 Type mismatch

 

[MARK858]

What is in the cell?

Sheets("Home").Range("Q2")

 

[andymalan]

What is in the cell?

Sheets("Home").Range("Q2")

Hi Mark
in Q2 is the name of the file I want to save to

 

[MARK858]

in Q2 is the name of the file I want to save to

Yes I know that but what is it exactly

 

[andymalan]

Yes I know that but what is it exactly

it is a filename derived from a quote or job number coupled with a Customers name and area description. So it will change all the time, but basically will read as Quote123_Jordan_Kitchen.xlsx

 

[MARK858]

So is "Quote123_Jordan_Kitchen.xlsx" exactly (without the quotes) what is in the cell when it is erring out?
How are you getting the filename in the cell (formula?)?
What happens if you change the line to...

Sheets("Home").Range("Q2").Text

 

[Siddharth Rout]

it is a filename derived from a quote or job number coupled with a Customers name and area description. So it will change all the time, but basically will read as Quote123_Jordan_Kitchen.xlsx

I think @MARK858 is on the right track. If the cell has a formula error then it is possible that you may get 13-Runtime error. To test this, type "=1/0" in say cell A1 and then in VBA run the code

Msgbox Range("A1").Value

I would recommend putting a breakpoint or introducing proper error handling to catch that error...

 

[andymalan]

So is "Quote123_Jordan_Kitchen.xlsx" exactly (without the quotes) what is in the cell when it is erring out?
How are you getting the filename in the cell (formula?)?
What happens if you change the line to...

Sheets("Home").Range("Q2").Text

Thank you Mark and also Sid, Q2 is reflecting #NAME?, so there is a flaw in the way the name is being constructed. You guys have put me on the right track.....