Hello Excel Experts,
I have a macro with me that will copy the existing workbook to a user defined path and name which will be provided in cell D8. Good thing about it is that this process happens in the background. But I want to get rid of the macros which were copied along with the workbook. To do this I found the following code in the forum from Well-known Member: Norie
and the one that I am currently using is
D8 =
The second macro or the one I am using right now will copy the excel file smoothly, however, it will not be a macro free workbook.
1. It will not give any prompts at all.
2. It will overwrite if file already exist.
3. It will create a directory in windows explorer for the year only if it doesn't already exist. (example: 2020, 2021, 2022 etc)
My question is how do I perform these three points with the first macro given in this post, so that it will be saved as a macro-free workbook.
Many thanks and will appreciate.
I have a macro with me that will copy the existing workbook to a user defined path and name which will be provided in cell D8. Good thing about it is that this process happens in the background. But I want to get rid of the macros which were copied along with the workbook. To do this I found the following code in the forum from Well-known Member: Norie
VBA Code:
ThisWorkbook.Sheets.Copy
Set wbNew = ActiveWorkbook
wbNew.SaveAs "NewName", FileFormat:=xlOpenXMLWorkbook
and the one that I am currently using is
VBA Code:
Sub copy_workbook()
Dim lFso As Boolean, mySplit, FnPath As String
Set myFSO = CreateObject("Scripting.FileSystemObject")
FnPath = ThisWorkbook.Sheets("HSheet").Range("D8").Value
mySplit = Split(FnPath, "\", , vbTextCompare)
If myFSO.FolderExists(Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)) Then
Debug.Print "OK---> "; Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
Else
MkDir Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
Debug.Print "New--> "; Replace(FnPath, "\" & mySplit(UBound(mySplit)), "", , , vbTextCompare)
End If
Set myFSO = Nothing
ThisWorkbook.SaveCopyAs ThisWorkbook.Sheets("HSheet").Range("D8").Value ':Save Copy
End Sub
D8 =
D:\Users\Admin\Dropbox\Business Folder\STOCK REPORT\2021\03-2021.xlsb |
The second macro or the one I am using right now will copy the excel file smoothly, however, it will not be a macro free workbook.
1. It will not give any prompts at all.
2. It will overwrite if file already exist.
3. It will create a directory in windows explorer for the year only if it doesn't already exist. (example: 2020, 2021, 2022 etc)
My question is how do I perform these three points with the first macro given in this post, so that it will be saved as a macro-free workbook.
Many thanks and will appreciate.