Copy and Rename Worksheet Based on Cell Reference - VBA troubleshooting question

[annieexcel]

Hi guys! I'm having some trouble with what I thought should be a pretty easy macro. I'm constantly working in a big workbook with many tabs. I want to be able to easily save a copy of the worksheet I am currently in and for the file to be named based off a cell reference. This code works when I'm in a workbook with only 1 tab but does not work in my workbook with many tabs. It does copy and create the file but the file just opens, and it doesn't save for me. Do you have any ideas? Any insight would be greatly appreciated.

Sub Make_And_Rename_Workbook()


Dim wksht As Worksheet
Set wksht = ActiveSheet


Dim path As String
path = "C:\Desktop"


wksht.Copy
ActiveWorkbook.SaveAs Filename:=path & wksht.Range("B3").Value & ".xlsx"
End Sub

 

[annieexcel]

I just had "filename" in B3 ... I changed B3 to "Johnson 7-23 Notes" (which is similar to what I'll actually be naming these files.) It saved a copy to my desktop of a file named "Excel BackupsJohnson 7-23 Notes"


Much closer to what I actually want! :D .. Just need to figure out how to get it to go exactly where I want and not include "Excel Backups" in the name.

 

[Norie]

Is 'Johnson 7-23 Notes' definitely all you have in the cell?

Is there any other code?

What happens if you create a folder in C:\ and save to there rather than the Desktop?

 

[annieexcel]

Yes there are no spaces or other characters in B3.

There is no other code.

I changed the code to:

Sub Make_And_Rename_Workbook()Dim wksht As Worksheet
Dim path As String


Set wksht = ActiveSheet


path = "C:\Excel Backups"


wksht.Copy


ActiveWorkbook.SaveAs Filename:=path & wksht.Range("B3").Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook


End Sub

I made a File named Excel Backups in C:\ and ran the code.

I got this error similar to earlier: https://imgur.com/a/Chts2Kr

 

[Norie]

The path needs to end in a '\', that's basically the only thing I changed in the original code.

Sub Make_And_Rename_Workbook()Dim wksht As Worksheet
Dim path As String


Set wksht = ActiveSheet


path = "C:\Excel Backups\"


wksht.Copy


ActiveWorkbook.SaveAs Filename:=path & wksht.Range("B3").Value & ".xlsx", FileFormat:=xlOpenXMLWorkbook


End Sub

 

[annieexcel]

Oh!!!!!!!!!! You're a miracle worker! That worked! Thank you so much!

https://imgur.com/a/NzpLfBV It's beautiful :D