Saveas newwb, close new wb, delete old wb

[metricsboy]

The below code works beautiful; it will modify the old, saveas a new wb then continue to modify old wbs until it has opened all wbs in Excel. Can I add code to it to kill the old wb ?

Sub rev_final()
'
' rev_final Macro
'
' Keyboard Shortcut: Ctrl+h
'
Range("P1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[1]C[-13],""__"",R[1]C[-12],""__"",R[1]C[-14])"
Range("P1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
FormulaVersion:=xlReplaceFormula2
Cells.Replace What:=":", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
FormulaVersion:=xlReplaceFormula2
fName = Range("P1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
NewFile = fName
' Change directory to suit your PC, including USER NAME
ChDir _
"C:\Users\Paul.Mccarty\OneDrive - Kenco Group\Documents\Pre-Inspections\2023-10-OCT - copy"
ActiveWorkbook.SaveAs Filename:=NewFile
ActiveWorkbook.Close True
Application.Run "PERSONAL.XLSB!rev_final"
End Sub

 

[eiloken]

The below code works beautiful; it will modify the old, saveas a new wb then continue to modify old wbs until it has opened all wbs in Excel. Can I add code to it to kill the old wb ?

Sub rev_final()
'
' rev_final Macro
'
' Keyboard Shortcut: Ctrl+h
'
Range("P1").Select
Application.CutCopyMode = False
ActiveCell.FormulaR1C1 = _
"=CONCATENATE(R[1]C[-13],""__"",R[1]C[-12],""__"",R[1]C[-14])"
Range("P1").Select
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
FormulaVersion:=xlReplaceFormula2
Cells.Replace What:=":", Replacement:="", LookAt:=xlPart, SearchOrder:= _
xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
FormulaVersion:=xlReplaceFormula2
fName = Range("P1").Value
'Change the date format to whatever you'd like, but make sure it's in quotes
NewFile = fName
' Change directory to suit your PC, including USER NAME
ChDir _
"C:\Users\Paul.Mccarty\OneDrive - Kenco Group\Documents\Pre-Inspections\2023-10-OCT - copy"
ActiveWorkbook.SaveAs Filename:=NewFile
ActiveWorkbook.Close True
Application.Run "PERSONAL.XLSB!rev_final"
End Sub

really don't understand what do you want but base on your old macro, it will modified all active workbook, rename those and save as new version, but that really risks to working with multiple files and just process them as "ActiveWorkbook", maybe macro will modified wrong file, but if you ok with that, you can try:

Sub rev_final()
'
' rev_final Macro
'
' Keyboard Shortcut: Ctrl+h
'
    Dim oldpath As String
    oldpath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    Range("P1").FormulaR1C1 = "=CONCATENATE(R[1]C[-13],""__"",R[1]C[-12],""__"",R[1]C[-14])" 'im just change this, no need to select cell and paste its value
    Range("P1").Value = Range("P1").Value
    Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
    FormulaVersion:=xlReplaceFormula2
    Cells.Replace What:=":", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
    FormulaVersion:=xlReplaceFormula2
    fName = Range("P1").Value
    'Change the date format to whatever you'd like, but make sure it's in quotes
    NewFile = fName
    ' Change directory to suit your PC, including USER NAME
    ChDir "your folder"
    ActiveWorkbook.SaveAs Filename:=NewFile
    ActiveWorkbook.Close True
    Kill oldpath
    Application.Run "PERSONAL.XLSB!rev_final"
End Sub

 

[metricsboy]

I am attempting to open a wb and make changes to it. Once the macro has made the changes, I want to save it as a new wb, close the new wb, and then delete the old wb that the macro just made changes to. I did try your code, but the old wb is still there. I attached an image. the wb named, "asdf" is the original wb that was opened by the macro, and the other wb is the saveas or new wb.

if the old wb cannot be deleted, that's ok, I just have to manually delete them.

 

[eiloken]

I am attempting to open a wb and make changes to it. Once the macro has made the changes, I want to save it as a new wb, close the new wb, and then delete the old wb that the macro just made changes to. I did try your code, but the old wb is still there. I attached an image. the wb named, "asdf" is the original wb that was opened by the macro, and the other wb is the saveas or new wb.

if the old wb cannot be deleted, that's ok, I just have to manually delete them.

if the old workbook cannot be deleted then that mean the macro you used was modified itself and save to new workbook right?

 

[metricsboy]

if the old workbook cannot be deleted then that mean the macro you used was modified itself and save to new workbook right?

I believe so. I thought I could have 1 wb in a folder, open manually, save it as a new wb, then have the macro kill the old wb and still have 1 wb (the new one) in the folder.

 

[eiloken]

try:

Sub rev_final()
'
' rev_final Macro
'
' Keyboard Shortcut: Ctrl+h
'
    Dim oldpath As String
    oldpath = ActiveWorkbook.Path & "\" & ActiveWorkbook.Name
    Range("P1").FormulaR1C1 = "=CONCATENATE(R[1]C[-13],""__"",R[1]C[-12],""__"",R[1]C[-14])" 'im just change this, no need to select cell and paste its value
    Range("P1").Value = Range("P1").Value
    Cells.Replace What:="/", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
    FormulaVersion:=xlReplaceFormula2
    Cells.Replace What:=":", Replacement:="", LookAt:=xlPart, SearchOrder:= _
    xlByRows, MatchCase:=False, SearchFormat:=False, ReplaceFormat:=False, _
    FormulaVersion:=xlReplaceFormula2
    fName = Range("P1").Value
    'Change the date format to whatever you'd like, but make sure it's in quotes
    NewFile = fName
    ' Change directory to suit your PC, including USER NAME
    ChDir "your folder"
    ActiveWorkbook.SaveAs Filename:=NewFile
    Kill oldpath
    ActiveWorkbook.Close True
    Application.Run "PERSONAL.XLSB!rev_final"
End Sub

 

[metricsboy]

That did not work either. It's ok. I appreciate the effort. I will just manually kill the originals manually.

 

[metricsboy]

Hello. Instead of deleting the old wb, what if I made it go to a folder called "trash". Is there code for that?

 

[eiloken]

Hello. Instead of deleting the old wb, what if I made it go to a folder called "trash". Is there code for that?

the real problem is i don't know what is old wb because you set it as ActiveWorkbook, if we can't located it then we can't kill it or move it to another folder too, you should clear what are you trying to do and the workbook that include this macro is used to modify another workbook or itself