Old macro won't run after upgrading to 2013

kendo679

New Member
Joined
Aug 26, 2016
Messages
25
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:
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
 

Some videos you may like

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)

kendo679

New Member
Joined
Aug 26, 2016
Messages
25
Noticed also that the individual sheets aren't being saved correctly when the macro runs.
Instead of saving sheet 4,6,7,9... the 4 files created all contain data from sheet 1
Again, works correctly when F8 stepping
 

kendo679

New Member
Joined
Aug 26, 2016
Messages
25
I fixed the macro by changing the SaveAs lines, but I don't know why I had to do this.
It functions just like it used to now, but I had to add screenupdating = false because all the new workbooks popping up
Code:
Sheets("O0120").Copy
    ActiveWorkbook.SaveAs path & "\5PA COMPS\O0120.NC", FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close
    ThisWorkbook.Activate
    
  Sheets("O0220").Copy
    ActiveWorkbook.SaveAs path & "\5PA COMPS\O0220.NC", FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close
    ThisWorkbook.Activate
  
  Sheets("O0320").Copy
    ActiveWorkbook.SaveAs path & "\5PA COMPS\O0320.NC", FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close
    ThisWorkbook.Activate
    
  Sheets("O0420").Copy
    ActiveWorkbook.SaveAs path & "\5PA COMPS\O0420.NC", FileFormat:=xlText, CreateBackup:=False
    ActiveWorkbook.Saved = True
    ActiveWorkbook.Close
    ThisWorkbook.Activate
  
  '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
 

Watch MrExcel Video

Forum statistics

Threads
1,095,385
Messages
5,444,150
Members
405,269
Latest member
Kjtakke

This Week's Hot Topics

Top