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.
C:Documents and SettingsXXXXApplication DataMicrosoftAddInstest.xla
I was copying a sheet and saving it as a new file. If you want to do the same, following subroutine can be handy :
Dim DstFile As String 'Destination File Name
Dim CurrentFileName As String
Dim wb As Workbook
CurrentFileName = ActiveWorkbook.FullName ' returns C:Testtest.xlsx
CurrentFileName = Replace(CurrentFileName, ".xlsx", "")
DstFile = CurrentFileName & "_new" & ".xlsx" ' returns C:Testtest_new.xlsx
Worksheets("st_orig").Copy 'Copying original sheet
Set wb = ActiveWorkbook
wb.SaveAs DstFile 'Saving the copy
wb.Close 'Close the file