VBA: Copy sheet and rename book

[Eean]

Hello again!

I'm trying to create a VBA code to copy the active sheet to a new book but I want to rename the new book after the sheet name. Then I'm going to email it.
I have a lot of these to do.
I have this code, but can't work out how to rename the book.

Sub Copy()
'
' Copy Macro
'
'
    ActiveSheet.Select
    ActiveSheet.Copy
End Sub

 

[Joe4]

I'm trying to create a VBA code to copy the active sheet to a new book but I want to rename the new book after the sheet name.

I think this code should do that for you:

Sub MySheetCopy()


    Dim mySourceWB As Workbook
    Dim mySourceSheet As Worksheet
    Dim myDestWB As Workbook
    Dim myNewFileName As String
    
'   First capture current workbook and worksheet
    Set mySourceWB = ActiveWorkbook
    Set mySourceSheet = ActiveSheet


'   Build new file name based
    myNewFileName = mySourceWB.Path & "\" & mySourceSheet.Name & ".xlsx"


'   Add new workbook and save with name of sheet from other file
    Workbooks.Add
    ActiveWorkbook.SaveAs Filename:=myNewFileName
    Set myDestWB = ActiveWorkbook
    
'   Copy over sheet from previous file
    mySourceWB.Activate
    Cells.Copy
    myDestWB.Activate
    Range("A1").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
    
'   Resave new workbook
    ActiveWorkbook.Save


End Sub

 

[Eean]

That works brilliantly!
I think I can follow most of that code too.
Thanks so much.

 

[Joe4]

Your welcome!

Note some of the assumptions I made, namely:
- you wanted to save the new file to the same path as the original file
- you wanted to use a "xlsx" extension

Either of those things can be changed easily enough, if they don't suit your needs.

 

[Eean]

Your welcome!

Note some of the assumptions I made, namely:
- you wanted to save the new file to the same path as the original file
- you wanted to use a "xlsx" extension

Either of those things can be changed easily enough, if they don't suit your needs.

Is it neccesary to define the path when you're defining a filename?
I think I may have been going wrong with that.

 

[Joe4]

I believe that you usually need to include the path as part of the file name when saving the file with the SaveAs command (or else it doesn't know where to save it to).

 

[Eean]

I believe that you usually need to include the path as part of the file name when saving the file with the SaveAs command (or else it doesn't know where to save it to).

I don't think I factored in that it needed saving...
You live and learn!
Thanks for sharing your excellent knowledge!

 

[r2fro]

Does anyone know why I'm having a problem with this code when trying to save .xlsm format?

.xlsx works fine for me so I'm a bit miffed.


The only change to the code I made was from this:

myNewFileName = mySourceWB.Path & "\" & mySourceSheet.Name & ".xlsx"

To this:

myNewFileName = mySourceWB.Path & "\" & mySourceSheet.Name & ".xlsm"

it hags on the SaveAs line with:
"Run-time error '1004' This file ext cannot be used with the selected file type. Change the file ext et.

 

[Joe4]

The issue isn't with the line you highlighted, it is with the SaveAs line. Since it is not being saved to the "default" format, you cannot use the default settings and actually need to specify the File Format.
So change this:

    ActiveWorkbook.SaveAs Filename:=myNewFileName

to this:

    ActiveWorkbook.SaveAs Filename:=myNewFileName, _
        FileFormat:=xlOpenXMLWorkbookMacroEnabled, CreateBackup:=False

There is an easy way to figure this out. Simply turn on the Macro Recorder and record yourself saving a file in the "xlsm" format. Then stop the Recorder and view the code. This will show you all the arguments and settings you need.