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
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,824
Messages
6,181,186
Members
453,020
Latest member
Mohamed Magdi Tawfiq Emam

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top