vba SaveAsXLSM

[Pinaceous]

Hi all,

I have a code that allows me to create a new excel document from an existing one, where it closes the existing document and opens up the new created document.

Here it is and works wonderful in this way:

Dim strName As String
        
    strName = ThisWorkbook.Path & "\" & Format(Date, "DDMMMYYYY ") & Sheet1.Name & " " & ".xlsm"
    
    ActiveSheet.Copy
    ThisWorkbook.SaveAs FileName:=strName, FileFormat:=xlOpenXMLWorkbookMacroEnabled
    ActiveWorkbook.Close savechanges:=False

My question is how do I modify this code to create an existing document, which does not close the existing document but rather creates a new document unopened?

I'm not sure how to modify this code.

Please let me know if you know how to do this.

Thank you,
pinaceous

 

[Fluff]

How about

Dim strName As String
        
    strName = ThisWorkbook.Path & "\" & Format(Date, "DDMMMYYYY ") & Sheet1.Name & " " & ".xlsm"
    ThisWorkbook.SaveCopyAs strName

 

[Pinaceous]

Hi Fluff,

I appreciate your post & your code!

I see says the blind man.

Thank you!
pinaceous

 

[Fluff]

You're welcome & thanks for the feedback.

 

[Pinaceous]

Fluff,

If I have the following:

Dim strName As String
    
    strName = ThisWorkbook.Path & "\" & "FILES NAME FOR 2020 " & ".xlsm"

    ThisWorkbook.SaveCopyAs strName

Is there any way I can have the code reference the "TITLE" from inside the workbook?

For example, if I have on Worksheets(1) Cell "A1" the text of

"FILES NAME FOR 2020 "

Could I reference that in the code?

Something like this:

 strName = ThisWorkbook.Path & "\" & "(Worksheet(1) Cell "A1")" & ".xlsm"

Please let me know.

Thank you!
pinaceous

 

[Fluff]

How about

strName = ThisWorkbook.Path & "\" & Worksheets(1).Range("A1").value  & ".xlsm"

 

[Pinaceous]

Nice Fluff! Cannot wait to test it! Thanks again!

 

[Fluff]

My pleasure.

 

[Pinaceous]

Good Day,

In using this code above from post #6, is there anyway that I can write an error message for this code?

For example, when I use this code depending upon the speed of the computer; Excel sometimes accompanies this code with an extra macro-free workbook called:

Book1.xlsx

Is there anyway that I can prevent this from happening through FSO?

This is what I have up until now that I'm writing as a separately run code, but not sure:

Sub VBA_Delete_Workbook_Excel_Using_FSO()
'Delete Workbook in Excel VBA using DeleteFile method of FSO
'?Remove MsgBox'sx2

    'Variable declaration
    Dim FSO
    Dim sWorkbookName As String
   
    'File Name to delete:
    sWorkbookName = "Book1.xlsx"
           
    'Set Object for file system object
    Set FSO = CreateObject("Scripting.FileSystemObject")
   
    'Before deleting check file exists or not
    If FSO.FileExists(sWorkbookName) Then
   
        'If file exists, It will delete the file from source location
        FSO.DeleteFile sWorkbookName, True
        MsgBox "Workbook has deleted successfully.", vbInformation
    Else
   
        'If file does not exists, It will display following message
        MsgBox "Specified Workbook was not found", vbInformation, "Not Found!"
    End If
      
End Sub

Please let me know & thank you so much!

pinaceous

 

[Fluff]

As you already have a thread running for this problem, you need to keep to it. Thanks