Results 1 to 3 of 3

Thread: AutoFilter Working But Causing Data Glitch On Another Sheet

  1. #1

    AutoFilter Working But Causing Data Glitch On Another Sheet

    Hello,

    I have the following code to insert a line, populate the cells and then filter the cells in a-z order. This works but every time I run it the whole range of data gets put onto another sheet but when I exit the workbook and come back in its normal. I can only think it a glitch as my code is messy. Any idea?

    Code:
    ' INSERT NEW CARRIER AND RATES FOR FUEL AND LOLO
    With ActiveWorkbook.Worksheets("Rates Matrix")
    .Range("I5:M5").Insert (xlDown)
    .Range("I5").Value = AddSupplier.TextName.Value
    .Range("J5").Value = AddSupplier.TextFuel.Value
    .Range("K5").Value = AddSupplier.Text20ft.Value
    .Range("L5").Value = AddSupplier.Text40ft.Value
    .Range("M5").Value = AddSupplier.Text40hc.Value
    End With
    
    'FILTER THE TABLE A - Z TO PUT NEW SUPPLIER IN ORDER
    With ActiveWorkbook.Worksheets("Rates Matrix").Range("I2").AutoFilter
        ActiveWorkbook.Worksheets("Rates Matrix").AutoFilter.Sort.SortFields.Clear
        ActiveWorkbook.Worksheets("Rates Matrix").AutoFilter.Sort.SortFields.Add Key _
            :=Range("I2:I28"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption _
            :=xlSortNormal
        With ActiveWorkbook.Worksheets("Rates Matrix").AutoFilter.Sort
            .Header = xlYes
            .MatchCase = False
            .Orientation = xlTopToBottom
            .SortMethod = xlPinYin
            .Apply
        End With
    End With
    
    With ActiveWorkbook.Worksheets("Rates Matrix").Range("I2").AutoFilter
    End With
    
    AddSupplier.Hide
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    https://www.reddit.com/r/windowsxp/comments/pexq9q/comment/k81ybvj/?utm_source=reddit&utm_medium=web2x&context=3
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgxYgiEZuS9I3xkjJv54AaABAg
    https://www.youtube.com/watch?v=bs-urI_o8jo&lc=UgyBACXgNY4j_cHgH5J4AaABAg.9oTkVdzfqfm 9wlhQrYJP3M
    ttps://www.youtube.com/watch?v=LP9fz2DCMBE
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg
    https://www.youtube.com/watch?v=LP9fz2DCMBE&lc=UgzbPgJUMCztIOQDym14AaABAg. 9wdo_rWgxSH9wdpcYqrvp8
    ttps://www.youtube.com/watch?v=bFxnXH4-L1A
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxuODisjo6cvom7O-B4AaABAg.9w_AeS3JiK09wdi2XviwLG
    https://www.youtube.com/watch?v=bFxnXH4-L1A&lc=UgxBU39bTptFznDC1PJ4AaABAg
    ttps://www.youtube.com/watch?v=GqzeFYWjTxI
    https://www.youtube.com/watch?v=GqzeFYWjTxI&lc=UgwJnJDJ5JT8hFvibt14AaABAg
    https://www.youtube.com/channel/UCnxwq2aGJRbjOo_MO54oaHA
    Last edited by DocAElstein; 11-06-2023 at 04:11 PM.

  2. #2

  3. #3
    Administrator Excel Fox's Avatar
    Join Date
    Mar 2011
    Posts
    1,401
    Rep Power
    10
    Try this

    Code:
    With Worksheets("Rates Matrix")
            .Range("I5:M5").Insert (xlDown)
            .Range("I5").Value = AddSupplier.TextName.Value
            .Range("J5").Value = AddSupplier.TextFuel.Value
            .Range("K5").Value = AddSupplier.Text20ft.Value
            .Range("L5").Value = AddSupplier.Text40ft.Value
            .Range("M5").Value = AddSupplier.Text40hc.Value
            'FILTER THE TABLE A - Z TO PUT NEW SUPPLIER IN ORDER
            .AutoFilterMode = False
            .Range("I2:I" & .Cells(.Rows.Count, "I").End(xlUp).Row).Sort Key1:=.Range("I2"), Order1:=xlAscending, Header:=xlYes
        End With
    A dream is not something you see when you are asleep, but something you strive for when you are awake.

    It's usually a bad idea to say that something can't be done.

    The difference between dream and aim, is that one requires soundless sleep to see and the other requires sleepless efforts to achieve

    Join us at Facebook

Similar Threads

  1. Replies: 4
    Last Post: 02-03-2014, 07:01 PM
  2. Replies: 8
    Last Post: 01-07-2014, 06:06 PM
  3. Replies: 2
    Last Post: 09-30-2013, 03:40 PM
  4. Replies: 14
    Last Post: 06-24-2013, 06:17 PM
  5. Do Not AutoFilter If Data Does Not Exist
    By xander1981 in forum Excel Help
    Replies: 14
    Last Post: 10-15-2012, 06:46 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •