Changing filename

[Guest]

I have a macro that will take data from an existing spreadsheet and copy
certain sheets into a new workbook. It then pastes these sheets as values.

What I want to do is save this new workbook and base the file name on the
original workbook and then add the text "(Values)" after it. So something in
C:\My Documents\Review For Partner.xls would then become C:\My
Documents\Review For Partner (Values).xls

A sample of the code is shown below:

Sub Macro1()
NewBookName = ThisWorkbook.FullName & " (Values)"
End Sub

The problem is that the file is called C:\My Documents\Review For
Partner.xls (Values).xls

I can see this is because when I use ThisWorkbook.FullName it produces the
answer "C:\My Documents\Review For Partner.xls"

What can I do to remove this .xls?

Thanks in advance.

 

[Guest]

NewBookName = Replace(ThisWorkbook.FullName, ".xls") & " (Values)"

 

[Guest]

Typo!

NewBookName = Replace(ThisWorkbook.FullName, ".xls", " (Values)")

 

[Guest]

That's brilliant. Cheers Charlie.

NewBookName = Replace(ThisWorkbook.FullName, ".xls") & " (Values)"

 

[Guest]

Thanks for that. Was just about to post to say I was getting a compiler error.

 

[Guest]

Thanks (even if it was incorrect!) I presume you fixed it before I reposted.