macro stops on "selection.autofilter" line of code

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

It needs the code Rob.

--

HTH

RP
(remove nothere from the email address if mailing direct)


"Rob" <Rob@discussions.microsoft.com> wrote in message
news:CE4203F9-7EF7-403A-A47C-368CE1AB58F0@microsoft.com...
> 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

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" <Rob@discussions.microsoft.com> wrote in message
> news:CE4203F9-7EF7-403A-A47C-368CE1AB58F0@microsoft.com...
> > 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
>
>
>

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" <Rob@discussions.microsoft.com> wrote in message
> > news:CE4203F9-7EF7-403A-A47C-368CE1AB58F0@microsoft.com...
> > > 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
> >
> >
> >

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" <Rob@discussions.microsoft.com> wrote in message
news:BBDAC256-6CB2-473E-952C-286CC62500E2@microsoft.com...
> 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" <Rob@discussions.microsoft.com> wrote in message
> > > news:CE4203F9-7EF7-403A-A47C-368CE1AB58F0@microsoft.com...
> > > > 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
> > >
> > >
> > >

Hi Bob,
Thankyou for your offer of help but it appears that recreating the two
buttons from scratch has solved the problem. I don't know why, but I am happy
that the template is functional again.
My next problem is I don't know how to modify the thread to show it is
resolved/solution not required. Do I or an administrator do this?
If it's me, how do I do it?

Cheers
Rob

"Bob Phillips" wrote:

> 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" <Rob@discussions.microsoft.com> wrote in message
> news:BBDAC256-6CB2-473E-952C-286CC62500E2@microsoft.com...
> > 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" <Rob@discussions.microsoft.com> wrote in message
> > > > news:CE4203F9-7EF7-403A-A47C-368CE1AB58F0@microsoft.com...
> > > > > 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