"400" error

[mklindquist0815]

I'm receiving a "400" error when trying to run one of my macros.

So I decided to step through my code and am getting the below error:



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

The error is happening right after this section of code

.Range("A1:G1").Delete

Here is the whole VBA.

Sub FormatSave()

'Convert to format desired
Dim WS1 As Worksheet
Dim Rand As Long
Set WS1 = Worksheets("N")
Dim lRow1, lRow2 As Long
Dim rcell As Range

'lRow1 = N.Range("A" & Rows.Count).End(xlUp).Row
'lRow2 = Y.Range("A" & Rows.Count).End(xlUp).Row

Set rcell = Columns(1).Find("Steps", LookIn:=xlValues, lookat:=xlWhole)
    If Not rcell Is Nothing Then
        Rows(rcell.Row).Resize(12).Delete  'CAN REPLACE .Hidden with .Delete if that suits you better
    End If
Set rcell = Nothing

If Not WS1 Is Nothing Then
With WS1

    ' Delete first row
    .Range("A1:G1").Delete
    
    ' CustomerID
    .Range("A:A").ColumnWidth = 7
    
    ' Amount
    .Range("B:B").ColumnWidth = 18
    
    ' Item Type
    .Range("C:C").ColumnWidth = 12
    
    ' Reference Nbr
    .Range("D:D").ColumnWidth = 30
    
    ' Accounting Date
    .Range("E:E").ColumnWidth = 8
    
    ' Term
    .Range("F:F").ColumnWidth = 4
    
    ' Reversal Ind
    .Range("G:G").ColumnWidth = 1

    

ESTFileDate = Format(Now, "MMDDYYYY")

' Save File as XLS
    ChDir "\\test\UWCOL\FA_files"
    .SaveAs Filename:= _
        "\\test\UWCOL\FA_files\DisbPos_" & ESTFileDate & ".xls", FileFormat:= _
        xlPrinter, CreateBackup:=False

' Save File as PRN
    ChDir "\\test\UWCOL\FA_files"
    .SaveAs Filename:= _
        "\\test\UWCOL\FA_files\DisbPos_" & ESTFileDate & ".prn", FileFormat:= _
        xlTextPrinter, CreateBackup:=False

' Save File as DAT
    ChDir "\\test\UWCOL\FA_files"
    .SaveAs Filename:= _
        "\\test\UWCOL\FA_files\DisbPos_" & ESTFileDate & ".dat", FileFormat:= _
        xlTextPrinter, CreateBackup:=False
        
End With

End If


With Sheet1

' Save File as XLS
    ChDir "\\test\UWCOL\FA_files"
    .SaveAs Filename:= _
        "\\test\UWCOL\FA_files\DisbAll_" & ESTFileDate & ".xls", FileFormat:= _
        xlPrinter, CreateBackup:=False

End With

End Sub

[macropod]

One possibility for the 400 error is a faulty 3rd-party addin.
The 1004 error would typically occur if, for example the worksheet didn't exist, but then you'd be getting that at the line:
Set WS1 = Worksheets("N")
On that basis, your test:
If Not WS1 Is Nothing Then
may as well be deleted, since the code would never get that far is there was no worksheet N.
I'd be inclined to replace all of:

Dim WS1 As Worksheet
Dim Rand As Long
Set WS1 = Worksheets("N")
Dim lRow1, lRow2 As Long
Dim rcell As Range

'lRow1 = N.Range("A" & Rows.Count).End(xlUp).Row
'lRow2 = Y.Range("A" & Rows.Count).End(xlUp).Row

Set rcell = Columns(1).Find("Steps", LookIn:=xlValues, lookat:=xlWhole)
    If Not rcell Is Nothing Then
        Rows(rcell.Row).Resize(12).Delete  'CAN REPLACE .Hidden with .Delete if that suits you better
    End If
Set rcell = Nothing

If Not WS1 Is Nothing Then
With WS1

with:

Dim rcell As Range
Const WSName As String = "N"
If SheetExists("Sheet3") = True Then
  With Worksheets(WSName)
    Set rcell = .Columns(1).Find("Steps", LookIn:=xlValues, lookat:=xlWhole)
    If Not rcell Is Nothing Then
        .Rows(rcell.Row).Resize(12).Delete  'CAN REPLACE .Hidden with .Delete if that suits you better
    End If

or:

Dim rcell As Range
Set rcell = .Columns(1).Find("Steps", LookIn:=xlValues, lookat:=xlWhole)
If Not rcell Is Nothing Then
  .Rows(rcell.Row).Resize(12).Delete  'CAN REPLACE .Hidden with .Delete if that suits you better
End If
Const WSName As String = "N"
If SheetExists("Sheet3") = True Then
  With Worksheets(WSName)

depending on whether rcell is supposed to exist on the sheet defined by WSName and, with either variant:

Function SheetExists(SheetName As String) As Boolean
SheetExists = False
On Error GoTo NoSuchSheet
If Len(Sheets(SheetName).Name) > 0 Then SheetExists = True
NoSuchSheet:
End Function

You could also get a 1004 error if any of the A1:G1 cells was protected.

Finally, you can delete all the lines with:
ChDir \\test\UWCOL\FA_files
as they're not needed.