I need to stop a line of code from looping if filter not found

    • New

    Hi - i have a Source Data sheet that i need to split up into 28 diff sheets based on criteria- we're good so far

    then i used the filter function to take whats in the source sheet and put the right data into the right sheet - also good so far

    however, i then used AutoFilter to filter out a certain status but the code break if there is nothing to filter

    please excuse my very basic code, i am learning as i go

    but essentially, this same code below is repeated 28 times for all the tabs i create. basically, on the source data there wont always be all 28 of the different values, but i still need the tab to create (and be empty) if there is nothing for that value. but it breaks when it gets to filtering out the Complete because the tab is empty.



    Sheets.Add.Name = "SCI"

    Sheets("SCI").Select

    Range("A1").Value = "SCI"

    Range("A2").Formula2 = "=FILTER('SOURCE DATA'!A2:M10000,ISNUMBER(SEARCH(A1,'SOURCE DATA'!A2:A10000)))"

    Sheets("SCI").Cells.Copy

    Sheets("SCI").Cells.PasteSpecial Paste:=xlPasteValues

    ActiveSheet.UsedRange.RemoveDuplicates Columns:=7, Header:=xlYes

    ActiveSheet.UsedRange.AutoFilter Field:=13, Criteria1:="<>Complete"

    ActiveSheet.Range("C2", "C10000").NumberFormat = "mm/dd/yyyy"

    ActiveSheet.Range("L2", "L10000").NumberFormat = "mm/dd/yyyy"

    Sheets("Source Data").Select

    Range("A1").EntireRow.Copy

    Sheets("SCI").Select

    Range("A1").PasteSpecial

    Range("A1").EntireColumn.Delete

    ActiveSheet.UsedRange.EntireColumn.AutoFit

    • New

    You could test:


    Code
    If activesheet.usedrange.columns.count > 1 then
' rest of your processing code here

End If

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    Thanks 1
    • New

    that was perfect Rory ! thanks :)


    I was also wondering if there was an easy way to get it to look at the last row/column in the data set (which will change each week) without having to do the Dim Long as Range thing

    • New

    There's none of that in your code, so I'm not really sure what you are currently doing...

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

    • New

    If you make the source data into a table, your formula can refer to the table and will not need changing.

    Rory
    Theory is when you know something, but it doesn’t work. Practice is when something works, but you don’t know why. Programmers combine theory and practice: nothing works and they don’t know why

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login