VBA Code for Saving Workbook not working

[WxShady13]

I am creating an Archive macro that when pressed will send a worksheet from one workbook to another. Currently the code will open the workbook and send the worksheet and then delete it from the original workbook. The problem I am having is saving the destination workbook after it has been opened. It is named CMD-Archive and sits in a Doc Library on a SharePoint site where the source file sits. What am I doing wrong?

Sub Archive()
'Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Workbooks.Open "FILENAME", UpdateLinks = False

ThisWorkbook.ActiveSheet.Copy After:=Workbooks("CMD-Archive.xlsm").Sheets(1)
Workbooks("CMD-Archive.xlsm").Activate
Application.DisplayAlerts = False
ActiveWorkbook.Save
ActiveWorkbook.Close
Application.DisplayAlerts = True

Application.DisplayAlerts = False
ActiveSheet.Delete
Application.DisplayAlerts = True

End Sub

 

[KennyGreens]

Hi, when you run this code, is the CMD-Archive workbook saving at all or does it just close? Does it give an error?

 

[WxShady13]

When the DisplayAlerts = False is turned off I get the popup asking me to save it, however when it is True I do not see anything, nor does it save. It does delete the worksheet from the source file though.

 

[KennyGreens]

I was not able to recreate the issue you are having. On a different note, you do not need to continue setting DisplayAlerts to true or false after it is done once as it applies to the application, not a specific workbook. To clean it up a bit, it could look like this:

Sub Archive()
'Application.AskToUpdateLinks = False
Application.DisplayAlerts = False
Workbooks.Open "FILENAME", UpdateLinks = False


ThisWorkbook.ActiveSheet.Copy After:=Workbooks("CMD-Archive.xlsm").Sheets(1)
Workbooks("CMD-Archive.xlsm").Activate
ActiveWorkbook.Save
ActiveWorkbook.Close


ActiveSheet.Delete


Application.DisplayAlerts = True


End Sub

This shouldn't effect what your code does, but it definitely cleans it up a bit.

 

[WxShady13]

THANK YOU!!! I wonder if it has anything to do with writing it back to a SharePoint site versus a shared folder?

 

[KennyGreens]

THANK YOU!!! I wonder if it has anything to do with writing it back to a SharePoint site versus a shared folder?

I'm not incredibly familiar with how a SharePoint site works so I am unsure. From what I understand though (and this could be completely wrong), isn't a SharePoint site treated very similar to a shared folder by Excel when trying to save a file to the folder? For example, you could just save as anything to the SharePoint site as if it were a folder?

 

[WxShady13]

In some cases....Thank you for the code, I think the disconnect now is between Excel and SP. I will continue working to see why they are not auto saving. I truly appreciate the help

 

[KennyGreens]

In some cases....Thank you for the code, I think the disconnect now is between Excel and SP. I will continue working to see why they are not auto saving. I truly appreciate the help

No problem, good luck!