Visual Basic error: 400

peejay

Board Regular
Joined
Jul 11, 2003
Messages
83
Hi
I have a macro which had been working fine, which had the following code:
With Range("Contact")
.Locked = False
.Copy
.PasteSpecial Paste:=xlPasteValues
.Interior.ColorIndex = 36
End With

where the range "Contact" is on the active sheet, and the active sheet is unprotected.

For some reason, the macro started throwing up an error box, titled 'Microsoft Visual Basic', showing only '400' (with OK & Help buttons).

I've fixed the problem, changing the 1st two lines to the following:

Range("Contact").Select
With Selection

but, I'd be interested in understanding why this has occurred.

Thanks,
PJ
 

Excel Facts

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
400 errors are, by definition, unspecified and (for all intents and purposes) mysterious. Your code should not fail here, as far as I can tell. You may even find that changing it back, it will now work again. The only way I know of to troubleshoot 400 errors is to step through the code line by line - and watch for the moment when it crashes.

From on outside perspective, the fact that you are unlocking a cell in order to do something with it suggests that there's an issue with worksheet protection. You say the sheet is unprotected - but if so, why must the cell be unlocked? I suspect there are other factors at play - though I can't say what or how, or why your code fix has made a difference.

Another try, without selection, might be:
Code:
Dim r As Range
Set r = Range("Contacts")
With r
    '// code
End With

I'm sure this is unsatisfactory as a response. If you're still interested in why, then give more details - what's this about with unlocking the cell, what's in the range and what's the purpose of the code? Is it in a larger procedure and if so what else is going on? Is the sheet being used with protection, and how? Is it a shared workbook? etc. etc. ! Also, are you sure this is the line with the problem? Have you stepped through the code?
 
Upvote 0
Hi Joe, Thank you for the quick reply. I tried the code but it came back as a error 400. I am running excel 2013. I cant tell exactly where the problem was, but it occurred after I selected the columns.

Thank you
 
Upvote 0
Sub SaveInvoiceWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "C:\Documents\Invoice" & Range("E4").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub

i am getting code 400 when i run the programme
 
Upvote 0
Sub SaveInvoiceWithNewName()
NewFN = "C:\Documents\Invoice\" & Range("E4").Value & ".xlsx"

I would suggest starting a new thread, but on a brief glance, you're probably missing a backslash where indicated above

Caleeco
 
Upvote 0
I would suggest starting a new thread, but on a brief glance, you're probably missing a backslash where indicated above

Caleeco

i have added the backslash and still getting the same problem.

Sub NextInvoice()
Range("E4").Value = Range("E4").Value + 1
Range("B13:D26").ClearContents
End Sub


Sub SaveInvoiceWithNewName()
Dim NewFN As Variant
' Copy Invoice to a new workbook
ActiveSheet.Copy
NewFN = "C:\Documents\Invoice" & Range("E4").Value & ".xlsx"
ActiveWorkbook.SaveAs NewFN, FileFormat:=xlOpenXMLWorkbook
ActiveWorkbook.Close
NextInvoice
End Sub
 
Upvote 0
Hello. I dont know if u have the answer for the error 400 but i want to share somthn on this. I was getting the same error for some reason on the worksheet When i process or apply a formula to the combined cells.
Change the combined cells to normal or modified the formula where u used. It works for me.
 
Upvote 0

Forum statistics

Threads
1,212,932
Messages
6,110,748
Members
448,295
Latest member
Uzair Tahir Khan

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top