Hi all
Ive designed a workbook that starts off called "Engine Run Form" thatgoes back and forth between departments at work. I've written some vba that at a point in the workbook saves and exports the workbook to an email and fills in recipients, the email title and attaches the workbook as an .xlsm file. It then also opens the Save As box, points the save location to a specific folder ("X:\Airfield Operations\2023 Airfield Inspection and Data\Aircraft High Powered Engine Runs\NEW\Pending Runs\") and names the workbook from the content of two cells on a worksheet "GVIIT - 23-07-2023" for example. It also selects the file extension as .xlsm. The Save As box then waits for the user to confirm the save. This all works perfectly.
At the end of the form is another button with vba attached to it to copy data from a worksheet within the workbook, open another workbook, paste the data, save the data workbook, close it and then open the Save As box to save the master workbook again as earlier but in to a different folder, "X:\Airfield Operations\2023 Airfield Inspection and Data\Aircraft High Powered Engine Runs\NEW\Completed Runs\"
Again all of this works, the file is named right in the Save AS box, the Save As box points to the right path, and it set to save as .xlsm, but after the user confirms the save as details and presses save, the save as box closes and I get either an "Automation error" or the workbook crashed and doesn't save.
Can anyone see what could be causing this to fail as the Save as part of the code works perfectly in the earlier step on the form?
I'd really really appreciate any help - this is the last step on the workbook project.
Ive designed a workbook that starts off called "Engine Run Form" thatgoes back and forth between departments at work. I've written some vba that at a point in the workbook saves and exports the workbook to an email and fills in recipients, the email title and attaches the workbook as an .xlsm file. It then also opens the Save As box, points the save location to a specific folder ("X:\Airfield Operations\2023 Airfield Inspection and Data\Aircraft High Powered Engine Runs\NEW\Pending Runs\") and names the workbook from the content of two cells on a worksheet "GVIIT - 23-07-2023" for example. It also selects the file extension as .xlsm. The Save As box then waits for the user to confirm the save. This all works perfectly.
At the end of the form is another button with vba attached to it to copy data from a worksheet within the workbook, open another workbook, paste the data, save the data workbook, close it and then open the Save As box to save the master workbook again as earlier but in to a different folder, "X:\Airfield Operations\2023 Airfield Inspection and Data\Aircraft High Powered Engine Runs\NEW\Completed Runs\"
Again all of this works, the file is named right in the Save AS box, the Save As box points to the right path, and it set to save as .xlsm, but after the user confirms the save as details and presses save, the save as box closes and I get either an "Automation error" or the workbook crashed and doesn't save.
Can anyone see what could be causing this to fail as the Save as part of the code works perfectly in the earlier step on the form?
I'd really really appreciate any help - this is the last step on the workbook project.
VBA Code:
Sub copy()
Application.ScreenUpdating = False
Dim wbBook1 As Workbook
Dim wbBook2 As Workbook
Set wbBook1 = ThisWorkbook
Set wbBook2 = Workbooks.Open("\\Gatwick.Airport.Local\HomeDirs$\HDR2-02\Trevor\Profile\Desktop\New folder (2)\master log.xlsx")
Set copySheet = wbBook1.Worksheets("CopyData")
Set pasteSheet = wbBook2.Worksheets("Data")
Application.ScreenUpdating = False
copySheet.Range("A3:AF3").copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1).PasteSpecial xlValues
Application.CutCopyMode = False
Cells.Select
With Selection
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlGeneral
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
With Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.Orientation = 0
.AddIndent = False
.IndentLevel = 0
.ShrinkToFit = False
.ReadingOrder = xlContext
End With
Range("A3").Select
ActiveWorkbook.Save
MsgBox ("Data transferred and saved. Thank you")
ActiveWorkbook.Close
'ThisWorkbook.Save 'save current workbook in current name
Dim strFolder As String
Dim i As Long
Dim fname As String
Dim reqdate As String
'Find the position of the period in the file name
i = InStr(ActiveWorkbook.Name, ".")
fname = Range("C2")
reqdate = Range("C6").Text
'Create a default file name by concatenating the file name without the extention _
plus the current date and time, and plus the xlsm extention
Filename = Left(fname, i - 1) & " - " & (reqdate) & ".xlsm"
'Open Save As dialog to a default folder with default file name
With Application.FileDialog(msoFileDialogSaveAs)
.AllowMultiSelect = False
.FilterIndex = 2 '2 = xlsm
.InitialFileName = "X:\Airfield Operations\2023 Airfield Inspection and Data\Aircraft High Powered Engine Runs\NEW\Completed Runs\" & Filename
.InitialView = msoFileDialogViewDetails
If .Show = -1 Then strFolder = .SelectedItems(1) Else Exit Sub
.Execute
End With
End Sub
