Errorhandler VBA - help please

dwilson38550m

Board Regular
Joined
Nov 21, 2005
Messages
89
Hi,

I wonder if someone can help me with an error handler. I need to have an error handler which when the operation fails, points to cell O1 of tab called "Data Sheet" and saves the file to the location on cell O1 (eg C:\Testfile\Example.xls). Here is what I have so far - can anyone suggest anything? Thanks in advance.

VBA Code:
    Sheets("Data Sheet").Select
    Range("O1").Select
    future5 = ActiveCell
continue:      ActiveWindow.ActivateNext
    Application.DisplayAlerts = False
    Rem ActiveWorkbook.Close SaveChanges:=FALSE
   
    ActiveWorkbook.SaveAs Filename:=future5, _
        FileFormat:=xlNormal, Password:="", WriteResPassword:="", _
        ReadOnlyRecommended:=False, CreateBackup:=False
   
       
    Application.DisplayAlerts = True

    ActiveWorkbook.Close SaveChanges:=False
    Sheets("data sheet").Select
    Range("A1").Select
   
   
Errorhandler:
    If Err.Number = 91 Then
    Err.Clear
    GoTo continue
    End If
    If Err.Number = 1004 Then
   
    End If
'
    Range("A6").Select
End Sub
 

Some videos you may like

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

Dermot

Board Regular
Joined
Aug 11, 2006
Messages
74
Office Version
  1. 365
Platform
  1. Windows
Try something like this instead to trap errors in saving the workbook

VBA Code:
On Error Resume Next
ActiveWorkbook.SaveAs Filename:=future5
Select case Err.number 'trap certain error numbers
case 91
  ActiveWorkbook.SaveAs Filename:=sheets( "Data Sheet" ").range("O1")
  Exit Sub ' or whatever you want to do when there is an error
case 1004
  'do whatever you want here
End Select
On Error Goto 0
'carry on if no error
 

Watch MrExcel Video

Forum statistics

Threads
1,114,261
Messages
5,546,832
Members
410,759
Latest member
Bufnercash
Top