Save worksheet as new workbook

Hi all,

I want to make a macro that allows me to easily save worksheets as new workbooks in the same path as the original workbook.

This is what I got so far:

Sub SaveWorksheet()
  Dim wb As Workbook
  Dim Start As String
  Dim Finish As String
  Dim Name As String
 
  Start = Sheets("Example").Range("F5")
  Finish = Sheets("Example").Range("M5")
  
  Sheets("Example").Copy
  Set wb = ActiveWorkbook
  Name = "Text" & " " & Start & "" & " - " & "" & Finish
  With wb
    .SaveAs ThisWorkbook.Path & "\Name.xlsx"
    wb.Close
  End With
End Sub

In this code 'Start' and 'Finish' are dates in cells.

The only problem is that the worksheet isn't saved with the name I gave it behind 'Name' but actualy with the name Name.xlsx..

Is there any easy way that can fix my problem?

Hope you have some tips

- Daan

It's being saved as name.xlsx because you are passing a literal string.
Anytime you have "anything with quotes" that is what you are passing.

If you have a string variable named "name", then you pass it without quotes, since it's a string you pass it into another string with & name &

SaveAs ThisWorkbook.Path & "\" & Name & ".xlsx"

Thanks for the rep. points.

Another very useful tool when you are trying to build a string is the immediate window. Control + G is the shortcut.

You can build your name string and then in the line after it is built, you enter Debug.Print Name and it will printout in the immediate window and you can check it for errors.

Please click the Thread Tools drop down box above your first post and choose solved.