| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro stops on "selection.autofilter" line of code
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 |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro stops on "selection.autofilter" line of code
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 |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
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 |
|
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro stops on "selection.autofilter" line of code
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" 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 |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
macro stops on "selection.autofilter" line of code
Rob,
I am on the newsgroups, there is no concept of marking it as solved here, it just disappears after time. -- HTH RP (remove nothere from the email address if mailing direct) "Rob" wrote in message ... 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" 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 |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Macro stops with "Large Amount of Data" Message | Excel Discussion (Misc queries) | |||
| VB Script: Code for "Sheets in selection" | Excel Discussion (Misc queries) | |||
| Fix Code: Select Case and "Contains" selection | Excel Programming | |||
| "Unload" selection after running macro? | Excel Programming | |||
| Selection.AutoFilter Field represented by "all" | Excel Programming | |||
Linear Mode
