Save a Worksheet with a String Name

[Vlr516]

Hi,
I am trying to save a new worksheet using a value in my sourcesheet and the tab name of the sourcesheet. My code works well if I just use the value, but as soon as I add the sourcesheet name code, I get an error. If I just do the A1 value it worksCan you help?

Sub NameNewWorksheet

Dim Name as String
Dim sourcesheet As Variant

activesheet.copy
Name = sourcesheet.range("A1").Value & " " & sourcesheet.Name     
ActiveWorkbook.SaveAs Filename:=Name

End Sub
[\Code]

 

[Joe4]

I believe the issue is that "Name" is a reserved word, it is an existing Property for workbook and worksheet objects. You are actually using it in your setting the name variable!!!
So you are causing confusion for VBA. The rule of thumb is NEVER used reserved words (like names of existing Objects, Properties, Functions, etc) as variable names. It can cause ambiguity, errors, and unexpected results.

You also have created a variable for "sourcesheet", but have not set it to anything yet!

Try something like:

[COLOR=#333333]Sub NameNewWorksheet[/COLOR]

[COLOR=#333333]Dim NewName as String[/COLOR]
[COLOR=#333333]Dim sourcesheet As Worksheet
[/COLOR]
'Set sourcesheet variable here

[COLOR=#333333]activesheet.copy[/COLOR]
[COLOR=#333333]NewName = sourcesheet.Range("A1").Value & " " & sourcesheet.Name [/COLOR]
[COLOR=#333333]ActiveWorkbook.SaveAs Filename:=NewName[/COLOR]

[COLOR=#333333]End Sub[/COLOR]

 

[Vlr516]

Thank you so much. That worked.

 

[Joe4]

You are welcome.