Macro for making a copy of the open workbook.

[Luke1690]

Ive been looking online and have seen a few possible ways to do this, thought id see what you lot know before trying them.

1) I'm after a macro that will save a copy of the current open workbook in a different location.
2) I'm then going to add my wipe data feature in the same macro so i would like a warning message before the macro runs asking along the line of (are you sure)

is this possible guys any snippets of code would be great full.

Thanks

 

[dmt32]

Hi,
look at using the SaveCopyAs method

You can read more in VBA Helpfile: Workbook.SaveCopyAs method (Excel)

Dave

 

[swapnilk]

Try this to make a copy of current open workbook:

Sub SaveCopy()
    'Declare a variable to hold the current workbook
    Dim wb As Workbook
    'Set the variable equal to the current open workbook
    Set wb = ActiveWorkbook
    
    'Prompt the user for the location to save the copy of the workbook
    Dim savePath As String
    savePath = Application.GetSaveAsFilename( _
        InitialFileName:=wb.Name, _
        FileFilter:="Excel Files (*.xlsx), *.xlsx")
    
    'Save a copy of the workbook to the specified location
    wb.SaveCopyAs savePath
End Sub

Code to ask user if they are sure?

Dim response As Integer

response = MsgBox("Are you sure?", vbYesNo, "Confirm")

If response = vbYes Then
  ' Insert your code here that you want to run after the user confirms
Else
  ' Insert code here to handle the case where the user does not confirm
End If

 

[Luke1690]

Try this to make a copy of current open workbook:

Sub SaveCopy()
    'Declare a variable to hold the current workbook
    Dim wb As Workbook
    'Set the variable equal to the current open workbook
    Set wb = ActiveWorkbook
   
    'Prompt the user for the location to save the copy of the workbook
    Dim savePath As String
    savePath = Application.GetSaveAsFilename( _
        InitialFileName:=wb.Name, _
        FileFilter:="Excel Files (*.xlsx), *.xlsx")
   
    'Save a copy of the workbook to the specified location
    wb.SaveCopyAs savePath
End Sub

Code to ask user if they are sure?

Dim response As Integer

response = MsgBox("Are you sure?", vbYesNo, "Confirm")

If response = vbYes Then
  ' Insert your code here that you want to run after the user confirms
Else
  ' Insert code here to handle the case where the user does not confirm
End If

Amazing response, thankyou so much.

 

[Luke1690]

Hi,
look at using the SaveCopyAs method

You can read more in VBA Helpfile: Workbook.SaveCopyAs method (Excel)

Dave

Thankyou also, i will look into this to better my knowledge.

 

[dmt32]

Thankyou also, i will look into this to better my knowledge.

You are welcome but do be aware SaveCopyAs does not have the File Format option so you cannot save the file in another format using this method - it does what it says, Saves A Copy of the workbook

Dave