VBA NameChange only occurs after the code ends

[GvdM]

Hi i'm writing to you from the Netherlands (for the first time) so you might see some workbook or worksheet names that might mean nothing to you. I'm using the following code to change the name of a workbook and save it to my Google drive (G. The code works fine up until the point i want to close the workbook with the New Name. The code doesn't recognize the New Name and I see why. Viewing the open workbooks it still shows the Old Name but once i activate that workbook it does show the New Name. I did a test using "Exit Sub" after the code taking care of the name change and then the problem does not occur, the names changes the way it should. I tried different codes but i can't find the solution. I know part of the code can be witten differently but that is not the problem. Hope you can help. Thanks in advance.

Sub Save_output_Webshop()

Application.ScreenUpdating = False

ChDir _
"G:\.shortcut-targets-by-id\1RvhmroiHIq3qAxupdEuUYPwIZ_G5-0Vp\Verwerking cursussen"
ActiveWorkbook.SaveAs Filename:="G:\.shortcut-targets-by-id\1RvhmroiHIq3qAxupdEuUYPwIZ_G5-0Vp\Verwerking cursussen\Excel Webshop" & " " & Format(Date, ddmmmyyy), FileFormat:=52

Cells.Select
Selection.Copy

Workbooks.Open Filename:= _
"G:\.shortcut-targets-by-id\1RvhmroiHIq3qAxupdEuUYPwIZ_G5-0Vp\Verwerking cursussen\Overzicht gegevens cursussen WIK.xlsm"

Cells.Select
Worksheets("Excel Dump Webshop").Activate
Cells.Select
ActiveSheet.Paste
Range("A1").Select
ActiveWorkbook.Save
Workbooks("Overzicht gegevens cursussen WIK.xlsm").Close SaveChanges:=True

'Here is the point where I get the Error as the open Workbook (called Dump) hasn't got the same name as the name below
Workbooks("Excel Webshop" & " " & Format(Date, ddmmmyyy)).Close SaveChanges:=True

End sub

 

[6StringJazzer]

You have a syntax error here and the code will not compile. You need quotes around the format spec:

ActiveWorkbook.SaveAs Filename:="G:\.shortcut-targets-by-id\1RvhmroiHIq3qAxupdEuUYPwIZ_G5-0Vp\Verwerking cursussen\Excel Webshop" & " " & Format(Date, "ddmmmyyy"), FileFormat:=52

And here also

Workbooks("Excel Webshop" & " " & Format(Date, "ddmmmyyy")).Close SaveChanges:=True

So I don't see how it's possible that you are getting a runtime error.

Workbooks must refer to the full file name include the extension:

Workbooks("Excel Webshop" & " " & Format(Date, "ddmmmyyy") & ".xlsm").Close SaveChanges:=True

That may fix your problem.

However, I would rewrite the code to use objects to refer to workbooks and sheets and then reference them explicitly.