I've been using this macro for a long time,
what it does:
-creates new folder
-saves sheets as new files
-saves workbook as new filename with time and date stamp
-chops off the file extensions
-saves copy of workbook into newly created folder
-adds date/time stamp to folder name
You'll notice that I'm taking some extra steps in there, that's because this is one of my first macros and I found work-arounds for things I (still) don't understand. Regardless, it worked fine until recently, and 2013 upgrade is the only obvious change. Now it only works when I step through with F8. If I run the macro it hangs up at the line following 'UNWANTED COPY CREATED. I've tried pausing for several seconds but no help.
I need to figure out why it won't run, But I'd be glad to hear ideas to help improve the code overall
As always, any help is greatly appreciated
code:
what it does:
-creates new folder
-saves sheets as new files
-saves workbook as new filename with time and date stamp
-chops off the file extensions
-saves copy of workbook into newly created folder
-adds date/time stamp to folder name
You'll notice that I'm taking some extra steps in there, that's because this is one of my first macros and I found work-arounds for things I (still) don't understand. Regardless, it worked fine until recently, and 2013 upgrade is the only obvious change. Now it only works when I step through with F8. If I run the macro it hangs up at the line following 'UNWANTED COPY CREATED. I've tried pausing for several seconds but no help.
I need to figure out why it won't run, But I'd be glad to hear ideas to help improve the code overall
As always, any help is greatly appreciated
code:
Code:
Sub OPsave()
Dim path As String
path = Application.ActiveWorkbook.path
Dim time As String
time = Format(Now, "yyyy-mm-dd hh.mm.ssam/pm")
MkDir path & "\5PA COMPS"
Sheet4.SaveAs path & "\5PA COMPS\O0120.NC", xlTextWindows
Sheet6.SaveAs path & "\5PA COMPS\O0220.NC", xlTextWindows
Sheet7.SaveAs path & "\5PA COMPS\O0320.NC", xlTextWindows
Sheet9.SaveAs path & "\5PA COMPS\O0420.NC", xlTextWindows
'UNWANTED COPY CREATED
ActiveWorkbook.SaveAs path & "\5PA OP DATA BKUP " & time & ".xlsm", FileFormat:=52
Dim old1 As String
Dim newname1 As String
old1 = path & "\5PA COMPS\O0120.NC"
newname1 = path & "\5PA COMPS\O0120"
Name old1 As newname1
old1 = path & "\5PA COMPS\O0220.NC"
newname1 = path & "\5PA COMPS\O0220"
Name old1 As newname1
old1 = path & "\5PA COMPS\O0320.NC"
newname1 = path & "\5PA COMPS\O0320"
Name old1 As newname1
old1 = path & "\5PA COMPS\O0420.NC"
newname1 = path & "\5PA COMPS\O0420"
Name old1 As newname1
ActiveWorkbook.SaveCopyAs Filename:=path & "\5PA COMPS\5PA OP DATA BKUP " & time & ".xlsm"
old1 = path & "\5PA COMPS"
newname1 = path & "\5PA COMPS " & time
Name old1 As newname1
ThisWorkbook.Saved = True
Dim xFullName As String
xFullName = Application.ActiveWorkbook.FullName
ActiveWorkbook.Saved = True
Application.ActiveWorkbook.ChangeFileAccess xlReadOnly
Kill xFullName
Application.ActiveWorkbook.Close False
Application.Quit
End Sub