Master Split - Crash using autofilter

Lukums

Board Regular
Joined
Nov 23, 2015
Messages
195
Hey guys,

Have a sheet with heaps of materials indicated by a machine ID - I'm doing a simple criteria search, which hides lines not relevant then copies and pastes this info to the corresponding sheet.
Issue is for some reason... and it wasn't before it's crashing constantly. The code works fine stepping through HOWEVER you can 100% see where it locks up being .autofilter

I used to select row... by row using IF (i) select row and paste but this is too timely can anyone see the issue?

Code:
Sub BargeSplit()


Sheets("MasterBOM").Activate


'Replace Punching FW with nothing
Cells.Replace What:="[FW]", Replacement:="", LookAt:=xlPart, _
        SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False




Dim BargeWS As Worksheet ''DESTINATION SHEET''
On Error Resume Next
Set TB = ActiveWorkbook
Set OB = ActiveWorkbook


erow = OB.Sheets("Barge").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0).Row '' DESTINATION SHEET''
TB.Activate
Sheets("MasterBOM").Range("H:Y").EntireColumn.Hidden = False ''SCHEDULER SHEET''
                    
                    With Sheets("MasterBOM").UsedRange
                        .AutoFilter
                        '''Barge = MACHINE ID'''
                         .AutoFilter field:=23, Criteria1:="Barge"
                        '''SEND THESE = Ability to be moved to MACHINE SHEET PAGES'''
                         .AutoFilter field:=25, Criteria1:="SEND THESE"
                         .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).Copy OB.Sheets("Barge").Cells(erow, 1)
                         .AutoFilter
                    End With
                '''REPAIR view sheet when columns are hidden/shown during selection process'''
Sheets("MasterBOM").Range("N:U").EntireColumn.Hidden = True
Sheets("MasterBOM").Range("V:Y").EntireColumn.Hidden = False


On Error GoTo 0


Call Ridge300Split


End Sub
 

Excel Facts

Pivot Table Drill Down
Double-click any number in a pivot table to create a new report showing all detail rows that make up that number

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396
try this line of code between the autofilter and the offset lines

.Cells(1, 1).CurrentRegion.Select
 

Dryver14

Well-known Member
Joined
Mar 22, 2010
Messages
2,396

ADVERTISEMENT

try commenting out the first autofilter, its a toggle so you turn it on then off again at the top of the macro
 

Lukums

Board Regular
Joined
Nov 23, 2015
Messages
195
try commenting out the first autofilter, its a toggle so you turn it on then off again at the top of the macro

omg... I'm so stupid... fixed.

I had 20 sub routines with .autofilter x3 per.
I removed all .autofilters each machine and left the very last one to fix the view and it works flawlessly...

So sorry nd thnk you for brain storming!
 

Watch MrExcel Video

Forum statistics

Threads
1,130,251
Messages
5,641,101
Members
417,194
Latest member
Excellent Excel

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top