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:
Code:
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:
Code:
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:
Code:
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:
Code:
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.