400 Error / Run Time Error 1004

[Bench]

Hi,

I've written a macro which has been working perfectly for all but one user. The original spreadsheets have been in place for around a year however the Macro is new and the final users spreadsheet is new as well.

Whenever i try and run the below for this user only and on any PC i get a "400" error box pop up half way through. if i walk through the process i get the following message

Run-time error '1004':
Application-defined or Object-defined error

when it reaches this line

Range("A65536").End(xlUp).Offset(1, 0).Select

I can't make sense of it at all.

I read that it could be from copying and pasting into the new spreadsheet so i retried just typing in everything from scratch, the same error persists but only on this spreadsheet.

Any ideas? i'm pulling my hair out.

full macro

Sub Submit()
    Application.ScreenUpdating = False
'   Checks required fields are filled in
    Range("A99").End(xlUp).Select
    If IsEmpty(ActiveCell.Value) Then
    MsgBox ("Please Ensure All Fields Are Complete And Then ReSubmit")
    Exit Sub
        Else
            Range("A99").End(xlUp).Offset(0, 1).Select
            If IsEmpty(ActiveCell.Value) Then
            MsgBox ("Please Ensure All Fields Are Complete And Then ReSubmit")
            Exit Sub
            End If
                Range("A99").End(xlUp).Offset(0, 2).Select
                If IsEmpty(ActiveCell.Value) Then
                MsgBox ("Please Ensure All Fields Are Complete And Then ReSubmit")
                Exit Sub
                End If
                    Range("A99").End(xlUp).Offset(0, 3).Select
                    If IsEmpty(ActiveCell.Value) Then
                    MsgBox ("Please Ensure All Fields Are Complete And Then ReSubmit")
                    Exit Sub
                    End If
                        Range("A99").End(xlUp).Offset(0, 4).Select
                        If IsEmpty(ActiveCell.Value) Then
                        MsgBox ("Please Ensure All Fields Are Complete And Then ReSubmit")
                        Exit Sub
                        End If
                    Range("A99").End(xlUp).Offset(0, 5).Select
                    If IsEmpty(ActiveCell.Value) Then
                    MsgBox ("Please Ensure All Fields Are Complete And Then ReSubmit")
                    Exit Sub
                    End If
                Range("A99").End(xlUp).Offset(0, 7).Select
                If IsEmpty(ActiveCell.Value) Then
                MsgBox ("Please Ensure All Fields Are Complete And Then ReSubmit")
                Exit Sub
                End If
            Range("A99").End(xlUp).Offset(0, 8).Select
            If IsEmpty(ActiveCell.Value) Then
            MsgBox ("Please Ensure All Fields Are Complete And Then ReSubmit")
            Exit Sub
            End If
    End If
    
'   Open Data Log
    MyDataFile = "J:\Ben Sharpe\Private Clients\Data Log.xls"
    Set MyWorkbook = Workbooks.Open(MyDataFile)
'   Check to see if file is already open
    If MyWorkbook.ReadOnly Then
        ActiveWorkbook.Close
        MsgBox "Cannot update Log, someone currently using file.  Please ask user to exit and try again."
        Exit Sub
    End If
      
    Workbooks("Up selling Marc.xls").Sheets("Sep").Activate
    Range("A99").End(xlUp).Select
    ActiveCell.Resize(, 10).Copy
    Workbooks("Data Log.xls").Sheets("September").Activate
    
    Range("A65536").End(xlUp).Offset(1, 0).Select
    
    ActiveCell.PasteSpecial Paste:=xlPasteValues, _
    Operation:=xlNone, _
    SkipBlanks:=False, _
    Transpose:=False
    
    
    Range("A65536").End(xlUp).Select
    ActiveCell.Offset(0, 10).Select
    ActiveCell.FormulaR1C1 = "Upsell"
    
    ActiveWorkbook.Close SaveChanges:=True
    
    Workbooks("Up Selling Marc.xls").Sheets("Sep").Activate
    
    Range("A1").Select
    ActiveWorkbook.Save
    
    'Working in Office 2000-2010
    Dim OutApp As Object
    Dim OutMail As Object
    Dim strbody As String
    Set OutApp = CreateObject("Outlook.Application")
    Set OutMail = OutApp.CreateItem(0)
    strbody = "Hi," & vbNewLine & vbNewLine & _
              "Sucessful logging of Upsell Case " & Range("B99").End(xlUp) & vbCr & Range("C99").End(xlUp) & vbCr & Range("C99").End(xlUp).Offset(0, 4) & vbCr & Range("C99").End(xlUp).Offset(0, 6) & vbNewLine & _
            vbNewLine & "Thank You"
        
    On Error Resume Next
    With OutMail
        .To = "bens@email.co.uk"
        .BCC = ""
        .Subject = "Successful Submission of Upsell VALnet Ref: " & Range("B99").End(xlUp)
        .Body = strbody
        .Send   'or use .Display
    End With
    On Error GoTo 0
    Set OutMail = Nothing
    Set OutApp = Nothing
    
    SendKeys "%{s}", True 'send the email without prompts
    
    Application.ScreenUpdating = True
    MsgBox "Sucessfully Logged - Thank You"
End Sub

 

[JackDanIce]

What version of Excel is this new user using? Is the very last row A65536?

An alternative way of writing that particular line is:

Range("A" & Rows.Count).End(xlUp).Offset(1,0).Select

 

[pcc]

If you are using a version of Excel with 65536 lines, and the sheet is full, you will get this error

 

[Bench]

What version of Excel is this new user using? Is the very last row A65536?

An alternative way of writing that particular line is:

Range("A" & Rows.Count).End(xlUp).Offset(1,0).Select

They're using 2003, however another user started using the file that it outputs to and they use Excel 2000. I tried your code as above and get the same error still

 

[JackDanIce]

I'm not really familiar with VBA in Excel versions pre 2003.

Does this line on it's own work?

Range("A" & Rows.Count).End(xlUp).select

If it does then test:

Range("A" & Rows.Count).End(xlUp).select
ActiveCell.Offset(1,0).select

It's not great having lots of "select"'s but some of the above may help narrow down where the problem is occuring?

 

[Bench]

I'm not really familiar with VBA in Excel versions pre 2003.

Does this line on it's own work?

Range("A" & Rows.Count).End(xlUp).select

If it does then test:

Range("A" & Rows.Count).End(xlUp).select
ActiveCell.Offset(1,0).select

It's not great having lots of "select"'s but some of the above may help narrow down where the problem is occuring?

This code works but still getting the run-time error, not sure if it may have something to do with the paste-special?

 

[JackDanIce]

What is the line highlighted when the error message is given?

 

[Bench]

What is the line highlighted when the error message is given?

It doesn't actually highlight a line, just the 400 sign and stops just before the xlup bit.

I've deleted the code and re-pasted it in and it seems to be working so fingers crossed.

I'm gonna try submitting it from the 2000 user and then try it after that as i have a feeling that may be causing some problems

 

[Bench]

One further bit of info, just before it started working i added a module in, previously all the code was on the individual sheets, not sure if this would make any difference at all