Excel Filename – Copy sheets to new file

In the previous post I posted a code to copy data and remove all external references and save as a new file. But for a simple case, I was trying to save a sheet – as it is – as a new file.

But, typical to a copy-paste code; a strange error occurred while I tried to get the filename of current sheet through an add-in. I wanted the filename of the excel file I was editing but I kept on receiving the name of add-in. It took me a while to realize that ‘ActiveWorkbook’ and ‘ThisWorkbook’ behave differently.

?ActiveWorkbook.FullName
C:\Documents and Settings\XXXX\Application Data\Microsoft\AddIns\test.xla

?ThisWorkbook.FullName
C:\Test\test.xlsx

I was copying a sheet and saving it as a new file. If you want to do the same, following subroutine can be handy :

Sub CopyMySheet()
Dim DstFile As String 'Destination File Name
Dim CurrentFileName As String
Dim wb As Workbook
CurrentFileName = ActiveWorkbook.FullName ' returns C:\Test\test.xlsx
CurrentFileName = Replace(CurrentFileName, ".xlsx", "")
DstFile = CurrentFileName & "_new" & ".xlsx" ' returns C:\Test\test_new.xlsx
Worksheets("st_orig").Copy 'Copying original sheet
Set wb = ActiveWorkbook
wb.SaveAs DstFile 'Saving the copy
wb.Close 'Close the file
End Sub

Leave a comment