"400" error

macropod · #2 11-04-2016, 06:09 PM

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.

Similar Threads
Thread	Thread Starter	Forum	Replies	Last Post
I keep getting an error when downloading email "An unknown error has occurred. 0x8004060"	dbassman	Outlook	0	07-15-2016 04:15 PM
Error: "Changes made were lost...reconnect with server", when switching "from" field	randhurrle	Outlook	2	02-25-2015 06:51 PM
Setup Error "this language is not supported installing office 2007"	Umsotho	Office	4	03-19-2014 03:11 PM
How to choose a "List" for certain "Heading" from "Modify" tool?	Jamal NUMAN	Word	2	07-03-2011 03:11 AM
Office 2007 "A File Error Has Occured" when saving with SmartArt	dcooper	Office	0	10-08-2010 01:47 AM