I am stumped.
I have a workbook that has been working fine for many months and has now thrown up an error.
The situation is that a userform opens a central file and saves a new record/updates an existing record then saves the file.
The file path/name is hard coded into the VBA.
The error is that even though the file opened is correct and the data copied over is correct, when I come to the activeworkbook.save line, it defaults to saving the file into my documents.
Has anyone ever encountered this issue?
I thought it was a variable that needed setting to nothing however this has not solved the issue.
Sub submit_record() Dim key As Long Dim answer As Integer Dim trans_date As Date Dim Cancel As Integer Dim CloseMode As Integer Application.DisplayAlerts = False Do tempstrD = InputBox("Please enter the Transaction Date") trans_date = tempstrD Loop While (Len(tempstrD) = 0) If UserForm1.booking_type_box.Value = "Hotel" Then Do tempStr = InputBox("Please enter the Conferma Reference") Conferma_ref = tempStr Loop While (Len(tempStr) = 0) Else End If answer = MsgBox("Is there a Freshdesk ticket associated with this payment?", vbYesNo + vbQuestion, "Freshdesk Ticket?") If answer = vbYes Then Do tempStr = InputBox("Please enter the associated Freshdesk ticket number") ticket_id = tempStr Loop While (Len(tempStr) = 0) Else End If Workbooks.Open Filename:="C:\Example\CENTRAL CC LOG 2016.xlsx", Password:="Password1" Sheets("2016").Activate Sheets("2016").Range("A4").Select Do If IsEmpty(ActiveCell) = False Then ActiveCell.Offset(1, 0).Select End If Loop Until IsEmpty(ActiveCell) = True Debug.Print ActiveCell.Row key = ActiveCell.Offset(-1, 0).Value + 1 ActiveCell = key ActiveCell.Offset(0, 1) = trans_date ActiveCell.Offset(0, 2) = UserForm1.supplier_name_box ActiveCell.Offset(0, 3) = UserForm1.supplier_ref_box ActiveCell.Offset(0, 4) = UserForm1.UK_amount_box ActiveCell.Offset(0, 5) = UserForm1.Int_amount_box ActiveCell.Offset(0, 6) = UserForm1.currency_type_box ActiveCell.Offset(0, 7) = UserForm1.traveller_name_box ActiveCell.Offset(0, 8) = UserForm1.trav_Date_box ActiveCell.Offset(0, 9) = UserForm1.Account_box ActiveCell.Offset(0, 10) = UserForm1.trip_num_box ActiveCell.Offset(0, 12) = UserForm1.consultant_name ActiveCell.Offset(0, 13) = UserForm1.Booker_name_box ActiveCell.Offset(0, 14) = UserForm1.reason_box ActiveCell.Offset(0, 11) = Conferma_ref ActiveCell.Offset(0, 18) = UserForm1.cards_box ActiveCell.Offset(0, 15) = ticket_id ActiveCell.Offset(0, 16) = UserForm1.booking_type_box ActiveCell.Offset(0, 17) = Format(trans_date, "MMM") Range("A1").Select 'EmptyClipboard Debug.Print ActiveWorkbook.Path & "\" & ActiveWorkbook.Name ActiveWorkbook.Save ActiveWorkbook.Close 'Application.DisplayAlerts = True End Sub
All help gratefully appreciated.