I"m getting the following error on my VBA:
Run time error '9': Subscript out of Range.
I'm sure it has something to do with the fact that for the data that I"m dealing with there is not a WS2 which holds "Y" because this data doesn't have Y values. I Need to figure out how to have the process ignore if there is no WS2 or WS1 to not error out.
Sub FormatSave()
'Convert to format desired
Dim WS1, WS2 As Worksheet
Dim Rand As Long
Set WS1 = Worksheets("N")
Set WS2 = Worksheets("Y")
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 Or Not WS1 Is Empty 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 "\FA_files"
.SaveAs Filename:= _
"\FA_files\DisbPos_" & ESTFileDate & ".xls", FileFormat:= _
xlPrinter, CreateBackup:=False
End With
End If
If Not WS2 Is Nothing Or Not WS2 Is Empty Then
With WS2
' 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 "\FA_files"
.SaveAs Filename:= _
"\FA_files\DisbNeg_" & ESTFileDate & ".xls", FileFormat:= _
xlPrinter, CreateBackup:=False
End With
End If
With Sheet1
' Save File as XLS
ChDir "\FA_files"
.SaveAs Filename:= _
"\FA_files\DisbAll_" & ESTFileDate & ".xls", FileFormat:= _
xlPrinter, CreateBackup:=False
End With
End Sub
Bookmarks