macro stops on "selection.autofilter" line of code
Okay, I will hold back, but check this thread over the next couple of days.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Rob" wrote in message
...
Hi again,
When I used the file today I had to recreate the two macro buttons that
had
been deleted & after using the macros to process today's information they
were still there! :-)
I will see if it works again tomorrow but at this stage it appears the
problem has gone! My code temporarily (for 2 days) stopping at the
"selection.autofilter" line of code may remain an unsolved mystery!
However, if you have any spare time & feel like suggesting other
improvements to the code - please do.
Thanks for your time,
Rob
"Rob" wrote:
hi Bob
Here's the complete code, as mentioned it is probably quite inefficient
but
it did work until Monday. If you need the actual file I can provide a
version
but do not know how to attach it, can I email it directly to you?
Sub PasteFCOorROMacro()
'to clear old data (JIC)
Dim SheetIdentifier As String
SheetIdentifier = ActiveSheet.Name
Select Case SheetIdentifier
Case Is = "FCO's"
Call RemoveDataFromFCOsSheet
'To put FCO's on separate sheet
Sheets("FCO's").Select
Columns("AM:AR").Select
Selection.EntireColumn.Hidden = False
Range("a1").Select
Call CheckForAutoFilterAndRemove
Sheets("Creation of Lot # for Sale File").Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
Else
End If
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="=*FCO*", Operator:=xlAnd
Range("A65536").Select
Selection.End(xlUp).Select
Dim LastRow As Long
LastRow = ActiveCell.Row
If LastRow = 2 Then
Sheets("FCO's").Range("A3").Value = "There are no FCO's in today's
file."
Else
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("FCO's").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Replace sale file with FCO's on Sale
ActiveSheet.Unprotect 'seems to be needed
Range("A1").Select
Cells.Replace What:="Sale File, Dated:", Replacement:="FCO's on Sale
File" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Copy formatting & insert subtotals
'to identify last row on FCO sheet
Range("A65536").Select
Selection.End(xlUp).Select
LastRow = ActiveCell.Row
'to paste formatting down as many rows as needed
Range("AT3:AX3").Select
Selection.Copy
Range("AT3:AT" & LastRow).Select
ActiveSheet.Paste
Range("AT" & LastRow + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AK" & LastRow + 2).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
'to insert flexible subtotal
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R3C:R[-1]C)"
Selection.Style = "Comma"
'to copy subtotal into other columns
Selection.Copy
Range("AT" & LastRow + 2 & ":AU" & LastRow + 2).Select
Selection.PasteSpecial Paste:=xlPasteFormulas,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
'to copy formatting of rows above
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
Case Is = "RO's"
Call RemoveDataFromROsSheet
Call CheckForAutoFilterAndRemove
Sheets("Creation of Lot # for Sale File").Select
If ActiveSheet.AutoFilterMode = True Then
Selection.AutoFilter
Else
End If
Range("A2").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.AutoFilter
Selection.AutoFilter Field:=10, Criteria1:="=*RO*", Operator:=xlAnd
Range("A65536").Select
Selection.End(xlUp).Select
'Dim LastRow As Long
LastRow = ActiveCell.Row
If LastRow = 2 Then
Sheets("RO's").Range("A3").Value = "There are no RO's in today's
file."
Else
Range("A1").Select
Range(Selection, ActiveCell.SpecialCells(xlLastCell)).Select
Selection.SpecialCells(xlCellTypeVisible).Select
Selection.Copy
Sheets("RO's").Select
ActiveSheet.Paste
Application.CutCopyMode = False
'Replace sale file with RO's on Sale
ActiveSheet.Unprotect
Range("A1").Select
Cells.Replace What:="Sale File, Dated:", Replacement:="RO's on Sale
File" _
, LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
'Copy formatting & insert subtotals
'to identify last row on FCO sheet
Range("A65536").Select
Selection.End(xlUp).Select
LastRow = ActiveCell.Row
'to paste formatting down as many rows as needed
Range("AT3:AX3").Select
Selection.Copy
Range("AT3:AT" & LastRow).Select
ActiveSheet.Paste
Range("AT" & LastRow + 2).Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("AK" & LastRow + 2).Select
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
'to insert flexible subtotal
ActiveCell.FormulaR1C1 = "=SUBTOTAL(9,R3C:R[-1]C)"
Selection.Style = "Comma"
'to copy subtotal into other columns
Selection.Copy
Range("AT" & LastRow + 2 & ":AU" & LastRow + 2).Select
Selection.PasteSpecial Paste:=xlPasteFormulas,
Operation:=xlNone, _
SkipBlanks:=False, Transpose:=False
ActiveSheet.Paste
Application.CutCopyMode = False
'to copy formatting of rows above
With Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight = xlThin
.ColorIndex = xlAutomatic
End With
With Selection.Borders(xlEdgeBottom)
.LineStyle = xlDouble
.Weight = xlThick
.ColorIndex = xlAutomatic
End With
End If
End Select
'*** issue here - the "Selection.AutoFilter" line is deleting the "copy
formulae" macro button & stuffing the autofilter up
Sheets("Creation of Lot # for Sale File").Select
ActiveSheet.Unprotect
Range("A1").Select
Selection.AutoFilter
Range("A1").Select
Select Case SheetIdentifier
Case Is = "FCO's"
Sheets("FCO's").Select
Columns("AM:AR").Select
Selection.EntireColumn.Hidden = True
Rows("2:2").RowHeight = 63
Range("a1").Select
If LastRow = 2 Then
MsgBox "There are no FCO's on today's file, therefore please press
the
""To save as overnight file"" button.", vbOKOnly, "PLEASE CONTINUE"
Else
End If
Case Is = "RO's"
Sheets("RO's").Select
Rows("2:2").RowHeight = 63
Range("a1").Select
If LastRow = 2 Then
MsgBox "There are no RO's on today's file, therefore please press
the
""To save as overnight file"" button.", vbOKOnly, "PLEASE CONTINUE"
Else
End If
End Select
End Sub
thanks for your time,
Cheers
Rob
"Bob Phillips" wrote:
It needs the code Rob.
--
HTH
RP
(remove nothere from the email address if mailing direct)
"Rob" wrote in message
...
Hi everyone,
What could cause & how can I prevent "selection.autofilter" from
causing
my
macro to bomb out early?
Read on to see all the details I think may be relevant w/o the
actual
code,
although I can post that on request when I get to work tomorrow...
I have a long winded & probably quite an inefficient macro (built as
I
learn) which is intended to copy a visible range of data from one
filtered
(in multiple columns) 'Sheet1" to "Sheet2" in the same workbook.
This has
worked for approx the past 3 mths in excel 2002, accessed at work
via a
citrix server. However, for the past 2 days (recently the IT
outsourcing
has
been changed!) the macro now stops at the line
"selection.autofilter"
which
is used to remove the autofilter on "Sheet1". When I try to [F8]
through
the
macro it gets to this line and says something like "code can't enter
break
mode." & offers [debug] or [end].
As well as exiting the macro uncompleted, this recently occuring
error
results in two macro buttons on "Sheet1" being deleted/disappearing,
the
filter drop down arrows in all but 2 columns (maybe A & about col
AE)
being
removed. I have tried "application.screenupdating = true" & ditto
for
events
but the buttons don't come back & the "removed" filter arrows
remain.
Any suggestions on what could cause this line to bomb out & how to
prevent
it will be greatfully received.
Also,please let me know if the complete code would be useful.
Thanks in advance,
Rob
|