Macro Does Not Close Excel Workbook

[legalhustler]

Hello,

The following macro copies a sheet (called "Data") from a Source workbook to a Destination workbook (both on SharePoint). When the procedure executes, the sheet is copied from the Source workbook to the Destination workbook and the Destination workbook saves & closes but then after a few second the Source workbook opens (in read only) and asks if I want to Save it after I click the X on the top right corner of the workbook. The Source workbook like the Destination workbook should NOT open after the macro finishes and I do not want any changes saved to it. I do not want the Excel application to close because I may have other workbooks open, just the two workbooks should close. Can anyone help modify the code below?

Additionally, if the macro is run more than once I would like the sheet that is copied (i.e. sheet name "Data 4.18.2024") to be replaced with the current version and renamed accordingly (i.e. sheet name "Data 4.18.2024"). I was hoping the part of the code where the comment is "Check if table with the same name exists, delete it if found" would do that but it does not. Can this also be corrected?

Sub PasteDataToLastWorksheet()
    Dim sourceWorkbookPath As String
    Dim destinationWorkbookPath As String
    Dim sourceWorkbook As Workbook
    Dim destinationWorkbook As Workbook
    Dim sourceSheet As Worksheet
    Dim destinationSheet As Worksheet
    Dim currentDate As String
    Dim sourceTable As ListObject
    Dim destinationTable As ListObject
    Dim sourceHeaderRow As Range
    Dim destinationHeaderRow As Range
    Dim destinationStartCell As Range
    Dim sourceDataRange As Range
    Dim sourceTableRange As Range
 
    ' Turn off screen updating to speed up the macro and prevent flickering
    Application.ScreenUpdating = False
 
    ' Define paths for source and destination workbooks
    sourceWorkbookPath = "https://.sharepoint.com/sites/DailyOrders?web=1"
    destinationWorkbookPath = "https://.sharepoint.com/sites/DailyOrders.xlsm?web=1"
 
    ' Open source workbook in read-only mode
    Set sourceWorkbook = Workbooks.Open(Filename:=sourceWorkbookPath, ReadOnly:=True)
    Set sourceSheet = sourceWorkbook.Sheets("Data")
 
    ' Get reference to source table and its header row
    Set sourceTable = sourceSheet.ListObjects("Data")
    Set sourceHeaderRow = sourceTable.HeaderRowRange
 
    ' Remove filters from source table
    sourceTable.AutoFilter.ShowAllData
 
    ' Get current date
    currentDate = Format(Now, "m.d.yyyy")
 
    ' Open destination workbook
    Workbooks.Open destinationWorkbookPath
    Set destinationWorkbook = Workbooks("DailyOrders.xlsm")
 
    ' Check if table with the same name exists, delete it if found
    For Each destinationSheet In destinationWorkbook.Sheets
        For Each destinationTable In destinationSheet.ListObjects
            If destinationTable.Name = "Data" Then
                Application.DisplayAlerts = False
                destinationTable.Delete
                Application.DisplayAlerts = True
                Exit For
            End If
        Next destinationTable
    Next destinationSheet
 
    ' Copy source table data including header row
    Set sourceTable = sourceSheet.ListObjects("Data")
    Set sourceHeaderRow = sourceTable.HeaderRowRange
    Set sourceDataRange = sourceTable.DataBodyRange
    Set sourceTableRange = Union(sourceHeaderRow, sourceDataRange)
    sourceTableRange.Copy
 
    ' Paste data into destination workbook
    Dim lastSheet As Worksheet
    Set lastSheet = destinationWorkbook.Sheets(destinationWorkbook.Sheets.Count)
    Set destinationSheet = destinationWorkbook.Sheets.Add(After:=lastSheet)
    destinationSheet.Name = "Data " & currentDate
    Set destinationStartCell = destinationSheet.Range("A1")
    destinationStartCell.PasteSpecial Paste:=xlPasteValues
    Set destinationTable = destinationSheet.ListObjects.Add(xlSrcRange, destinationStartCell.CurrentRegion, , xlYes)
    Set destinationHeaderRow = destinationTable.HeaderRowRange
    destinationHeaderRow.Value = sourceHeaderRow.Value
    Application.CutCopyMode = False
 
    ' Save changes in destination workbook
    destinationWorkbook.Save
    destinationWorkbook.Close
 
    ' Close source workbook without saving changes and hide it
    sourceWorkbook.Close SaveChanges:=False
 
    ' Turn on screen updating
    Application.ScreenUpdating = True
End Sub

 

[Jeffrey Mahoney]

It looks your code is good. Does the source WB have a macro running in the Private Sub Workbook_Open()? Some sort of timer?

 

[legalhustler]

It looks your code is good. Does the source WB have a macro running in the Private Sub Workbook_Open()? Some sort of timer?

Nope. The source WB is a .xlsx file and has no macros. The source WB just uses Power Query to pull data from a database. You can probably test the code yourself, just change the SharePoint source and destination references and you'll see that the source WB opens up after the codes finishes copying the "Data" sheet to the destination WB.

 

[Jeffrey Mahoney]

I've seen odd things, sometimes I just have to work around them. Try putting this line:
sourceWorkbook.Close SaveChanges:=False
Above these two lines:
destinationWorkbook.Save
destinationWorkbook.Close

OR
Save the source wb to a temporary folder and call it Dump.xlsx or something and then close it. This code will overwrite it every time. Maybe it's Sharepoint causing the problem.

 

[legalhustler]

I've seen odd things, sometimes I just have to work around them. Try putting this line:
sourceWorkbook.Close SaveChanges:=False
Above these two lines:
destinationWorkbook.Save
destinationWorkbook.Close

OR
Save the source wb to a temporary folder and call it Dump.xlsx or something and then close it. This code will overwrite it every time. Maybe it's Sharepoint causing the problem.

I did the top portion by changing the sequence of the code, and when the macro ran it didn't open the source WB but it did open a Excel workbook that has no gridlines and everything is grayed out (see attached image). When I click the X on the top right corner to close this workbook it doesn't ask me to save unlike the original version of the code. I did it a few times and got the same result. I can't store the files on a local folder, the files must be on SharePoint.

FYI - my macro is on the Destination workbook and while it's open I run it from there. The Source workbook is closed, but it then opens in read-only mode after the macro finishes.

 

[Jeffrey Mahoney]

I can't store the files on a local folder, the files must be on SharePoint.

No, I meant to save the SOURCE file to a local drive, not to actually use it, but to save it there in case closing while open from Sharepoint is the issue.

Your macro is in the Destination WB. That is the issue!

When you close the WB with the macro running and you have after the close statement, it must reopen to run the rest. Change the order so your destination WB close is the last thing

' Save changes in destination workbook
    destinationWorkbook.Save
    destinationWorkbook.Close
 
    ' Close source workbook without saving changes and hide it
    sourceWorkbook.Close SaveChanges:=False
 
    ' Turn on screen updating
    Application.ScreenUpdating = True

 

[legalhustler]

No, I meant to save the SOURCE file to a local drive, not to actually use it, but to save it there in case closing while open from Sharepoint is the issue.

Your macro is in the Destination WB. That is the issue!

When you close the WB with the macro running and you have after the close statement, it must reopen to run the rest. Change the order so your destination WB close is the last thing

' Save changes in destination workbook
    destinationWorkbook.Save
    destinationWorkbook.Close
 
    ' Close source workbook without saving changes and hide it
    sourceWorkbook.Close SaveChanges:=False
 
    ' Turn on screen updating
    Application.ScreenUpdating = True

Not sure how you want the code to look. This is the last thing I did as mentioned in my previous reply (see below). Can you show how the code should look like?

' Close source workbook without saving changes and hide it
    sourceWorkbook.Close SaveChanges:=False

' Save changes in destination workbook
    destinationWorkbook.Save
    destinationWorkbook.Close
 
    ' Turn on screen updating
    Application.ScreenUpdating = True

 

[Jeffrey Mahoney]

' Close source workbook without saving changes and hide it
    sourceWorkbook.Close SaveChanges:=False

    ' Turn on screen updating
    Application.ScreenUpdating = True

' Save changes in destination workbook
    destinationWorkbook.Save
    destinationWorkbook.Close

 

[legalhustler]

' Close source workbook without saving changes and hide it
    sourceWorkbook.Close SaveChanges:=False

    ' Turn on screen updating
    Application.ScreenUpdating = True

' Save changes in destination workbook
    destinationWorkbook.Save
    destinationWorkbook.Close

That resulted in the same thing as in my reply #5. The macro is in my destination workbook and after it finishes running it opens a Excel workbook that has no gridlines and everything is grayed out (as shown in the last attachment).

 

[Jeffrey Mahoney]

If your macro is in your destination WB then what is this? It looks like your code is not in your destination WB. If that is the case, then that change won't have any affect

' Open destination workbook
    Workbooks.Open destinationWorkbookPath
    Set destinationWorkbook = Workbooks("DailyOrders.xlsm")