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

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.

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
 

Forum statistics

Threads
1,089,587
Messages
5,409,153
Members
403,254
Latest member
Saiyan_8

This Week's Hot Topics

  • help please
    SORRY NOT ANY GOOD AT EXCEL SO HELP WOULD BE MUCH APPRECIATED this formula is in a sheet called ignore...
  • two formulas needed
    Hello, I'll try my best to explain this: First formula needed in Sheet1 cell A2: If Sheet1 cell B2 = Sheet2 cell B2 then return a 1. If not then...
  • Dynamic Counts
    Good afternoon, we are tidying up some data & the data seems to be growing quicker than we are tidying it up! What we confirm (by reviewing it...
  • Help Excel formula eliminate duplicate values and keep only 2 identical rows.
    as picture below column A has a duplicate value. but the values are not the same as the rule. sometimes 4 rows, sometimes 10 rows or 7 or 9...
  • Macro Compile Error Sub or Function not defined
    Hello, I am trying to run macros from a validation list, all macros have been created and run perfectly on there own but I'm getting a compile...
  • Last row combined with Current Region VBA
    I'm generally happy finding the last row of data through something like Lastrow = Cells(Rows.Count, "D").End(xlUp) but I don't always receive data...
Top