Retype Macro to run properly

[Ramadan2512]

I have recorded a Macro to re-arrange my table with color and to sort row by date Ascending but later I needed to add 03 rules to remove all filters in the table to show all data then to go to the last row in table and to save the workbook but it seems that they need to be set in a professional way to run smoothly and properly because it gives me an error in this area - so I need a help to do that please
here is my code

[
Sub Rearrange()

    Range("B8:K8").Select
    Selection.Copy
    Range("Table1").Select
    Selection.PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
    Application.CutCopyMode = False
    ActiveWorkbook.Worksheets("Data").ListObjects("Table1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Data").ListObjects("Table1").Sort.SortFields.Add( _
        Range("Table1[[#All],[Column10]]"), xlSortOnCellColor, xlAscending, , _
        xlSortNormal).SortOnValue.Color = RGB(146, 208, 80)
    With ActiveWorkbook.Worksheets("Data").ListObjects("Table1").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With
     ActiveSheet.ShowAllData
     ThisWorkbook.Save
    Selection.End(xlDown).Select
End Sub

]

 

[Submeg]

Hi @Ramadan2512, when you say "remove all filters", you mean make sure no filter is active, not completely remove the filter? If so:

Selection.AutoFilter

Will toggle the Filter on and off.

Then to go to the last row in table and to save the workbook​

Yep, you have those steps -

ThisWorkbook.Save
Selection.End(xlDown).Select

 

[Ramadan2512]

Hi @Ramadan2512, when you say "remove all filters", you mean make sure no filter is active, not completely remove the filter? If so:

Selection.AutoFilter

Will toggle the Filter on and off.


Yep, you have those steps -

ThisWorkbook.Save
Selection.End(xlDown).Select

thanks for your reply - in my table there are filters that I use sometine to search data so I need the marco when clicked to show all the table data not only filtered rows so I used (ActiveSheet.ShowAllData) then to save the workbook and finally to go to the last row to let me enter you data

 

[Submeg]

thanks for your reply - in my table there are filters that I use sometine to search data so I need the marco when clicked to show all the table data not only filtered rows so I used (ActiveSheet.ShowAllData) then to save the workbook and finally to go to the last row to let me enter you data

Looks like when you combine those three, you have it covered! The only other thing I could think would be to turn off screenupdating so it all happens in the blink of an eye.

 

[Ramadan2512]

I get a debug error

 

[Submeg]

I get a debug error

On which line? What error?

 

[Ramadan2512]

On which line? What error?

On which line? What error?

ActiveSheet.ShowAllData

 

[Alex Blakenburg]

Try:

ActiveWorkbook.Worksheets("Data").ListObjects("Table1").AutoFilter.ShowAllData

 

[Alex Blakenburg]

If you add a bit more code it will handle the situation where the filter buttons have been turned off on the table:

    With ActiveWorkbook.Worksheets("Data").ListObjects("Table1")
        If .ShowAutoFilter = True Then
            .AutoFilter.ShowAllData
        End If
    End With

 

[Ramadan2512]

If you add a bit more code it will handle the situation where the filter buttons have been turned off on the table:

    With ActiveWorkbook.Worksheets("Data").ListObjects("Table1")
        If .ShowAutoFilter = True Then
            .AutoFilter.ShowAllData
        End If
    End With


[/QUOTE]