Hello all & Happy New Year!
I am getting a run-time error as per the title of '-2147467259 (80004005)'
It says on the VB error an 'Automation error' and 'Unspecified error'.
About the script -
It is activated when a user selects a cell containing the words 'Raise Follow-up Report', which is a hyperlink which runs this code below of Followup_Confirm.
Now probably, this isn't the prettiest code so any help welcomed!
It is debugging on the line - ActiveWorkbook.SaveAs Range("A1").Value & ".xlsm", 52
The script creates a new worksheet and paste's certain information into it.
Then saves (above) as the cell value of A1.
The cell A1 is referencing another workbook with the file path in - could this be the issue?
I appreciate any help to fix this!
Thank you
I am getting a run-time error as per the title of '-2147467259 (80004005)'
It says on the VB error an 'Automation error' and 'Unspecified error'.
About the script -
It is activated when a user selects a cell containing the words 'Raise Follow-up Report', which is a hyperlink which runs this code below of Followup_Confirm.
Now probably, this isn't the prettiest code so any help welcomed!
Code:
Sub Followup_Confirm()
Dim Msg As String, Ans As Variant
Msg = "Confirm you would like to raise a Follow-up form"
Ans = MsgBox(Msg, vbYesNo)
Select Case Ans
Case vbYes
Application.DisplayAlerts = False
' Copy date and paste in cell on row
ActiveCell.FormulaR1C1 = "Form Raised"
ActiveCell.Offset(0, 1).Select
ActiveCell.FormulaR1C1 = "=R4C54"
Selection.Copy
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
Application.CutCopyMode = False
' Select the worksheet that is to be saved
Sheets("IIR_temp").Select
ActiveSheet.Copy ' Copies active sheet to a new workbook
Range("A2:BG16").Select
Selection.Copy
Range("A2").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
'Import CLS file to new workbook
With ActiveWorkbook.VBProject.VBComponents("ThisWorkbook").CodeModule
.DeleteLines StartLine:=1, Count:=.CountOfLines
.AddFromFile "\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\IIRSaveUpdate.txt"
End With
'Import BAS file to new workbook
Dim filename As String
filename = "\\dataserver\ManagementSystem\NYL-HSE Group\Accidents, Near Misses & Hazard Alerts\IIR_Exchange.bas"
Application.VBE.ActiveVBProject.VBComponents.Import (filename)
Application.DisplayAlerts = False
Application.CutCopyMode = False
[COLOR=#ff0000]ActiveWorkbook.SaveAs Range("A1").Value & ".xlsm", 52[/COLOR]
ActiveWorkbook.Close
Sheets("Register").Select
End Select
End Sub
It is debugging on the line - ActiveWorkbook.SaveAs Range("A1").Value & ".xlsm", 52
The script creates a new worksheet and paste's certain information into it.
Then saves (above) as the cell value of A1.
The cell A1 is referencing another workbook with the file path in - could this be the issue?
I appreciate any help to fix this!
Thank you