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

 

[Norie]

Are you sure the workbook isn't being saved?

Have a look in C:\ for any Excel files with names starting with 'Desktop'.

 

[annieexcel]

Thanks for your reply, Norie! Unfortunately no there aren't any files starting with Desktop in C:\

 

[annieexcel]

Norie - Actually now I see that two links to desktop did show up in C:\ that are dated this morning.. but they're literally links straight to the desktop. Not sure if this is applicable. https://imgur.com/a/ojekiII

 

[Norie]

Not sure what's going on there but try this.

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

    Set wksht = ActiveSheet

    path = "C:\Desktop\"

    wksht.Copy

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

End Sub

 

[annieexcel]

Hmm that gave me this syntax error: https://imgur.com/a/Y4F3i47 I really appreciate your help troubleshooting this! Will be sure to use code tags next time )

 

[Norie]

Try this.

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

    Set wksht = ActiveSheet

    path = "C:\Desktop\"

    wksht.Copy

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

End Sub

 

[annieexcel]

This time it copied and opened the file but then I got this: https://imgur.com/a/lxl9UWt

 

[annieexcel]

Well this is interesting... I tried changing the code to

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


Set wksht = ActiveSheet


path = "C:\Users\Steph\Desktop\Excel Backups"


wksht.Copy


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


End Sub

It now made and saved the file to my desktop under the name "Excel Backupfilename" (filename is what I have in Cell B3)

 

[Norie]

Do you have a valid filename in B3 on the sheet being copied?