VBA macro to create a table between a filtered range upon closing

Question

I have hundreds of excels with data in them. It would take way too long to press insert > Table > ok -> Filter > number filter between range -1 and 1 for all of them.

Excel looks like:

Date    Client A    Client B    %   $        Other  Misc    Data    #
11/11/  Bob         Hond        33  $40      N/A    N/A      N/A    0
12/11/  Jack        Jase        3   $40      N/A    N/A      N/A    2

etc,,

UPDATED FINAL

Thanks Siddharth Rout!!

My Final code is below. I will continue to test it to ensure there are no bugs but I was able to do all I requested before.

Sub Auto_Close()
'
' Macro2 Macro
'

'

With Sheets("Sheet1")
    If Application.WorksheetFunction.CountA(.Cells) <> 0 Then
        lastRow = .Cells.Find(What:="*", _
                      After:=.Range("A2"), _
                      Lookat:=xlPart, _
                      LookIn:=xlFormulas, _
                      SearchOrder:=xlByRows, _
                      SearchDirection:=xlPrevious, _
                      MatchCase:=False).Row
    Else
        lastRow = 1
    End If
End With

    Range("A2").Select
    ActiveSheet.ListObjects.Add(xlSrcRange, , xlYes).Name = _
        "Table1"
    Range("Table1[#All]").Select
    ActiveSheet.ListObjects("Table1").Range.AutoFilter Field:=9, Criteria1:= _
        ">=VALUE", Operator:=xlAnd, Criteria2:="<=VALUE"
End Sub

I used this in combination with a VBSCRIPT and it worked like magic. Cheers

Sorry, thats how the excel looks. When I said Data I meant thats what the values in the cells of excel look like — , Jul 01 '17 at 02:06
No problem Jeroen Heier, just give me 5 minutes to get it out — , Jul 01 '17 at 07:53
I am not able to undertsand your request. If you could explain in simple terms what you want? I can see that you want to create table and you want to do it when closing the workbook... rest I am not too sure about — Siddharth Rout, Jul 01 '17 at 10:25
I want to create a Table between values when excel closes. Column A -> I. Row 1 -> Varies based on workbook. Make sense? — , Jul 01 '17 at 10:42
So from Row 1 to last row? If yes then see [THIS](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-vba/11169920#11169920). Find the last row and similarly find the last column and then make your table. — Siddharth Rout, Jul 01 '17 at 15:05
Also if you use "@" before my name and then type a message, I will get a direct ping else I will not know if you left a reply here — Siddharth Rout, Jul 01 '17 at 15:06
@SiddharthRout Thanks a million!!! I got it working with a dynamic range! Now I just have to create this macro to activate upon exit. A shame that other guy deleted his answer — , Jul 02 '17 at 01:09
@SiddharthRout Hmm It seems activate macro upon closing is not as ideal as I thought. Is there something like when A2 changes, activate macro. Will continue to look into it. — , Jul 02 '17 at 03:41
For your last comment you may want to see [THIS](https://stackoverflow.com/questions/13860894/ms-excel-crashes-when-vba-code-runs/13861640#13861640) — Siddharth Rout, Jul 02 '17 at 05:15
@SiddharthRout Thanks. I'll take a look at that now. Do you have know how to activate macro upon Save and also Close excel and save to new name in same folder? I've included how I believe its done in my post but am yet to get it working. — , Jul 02 '17 at 05:32
`[1]` Instead of closing and saving, you need to first do `.Save As` and then `.Close` Search the forums or MSDN and you will get lot of links `[2]` Why do you want to activate? `[3]` Read the last link again. There are lot of things required to be kept in mind when working with `Worksheet_Change` — Siddharth Rout, Jul 02 '17 at 05:36
@SiddharthRout Thanks heaps. Could the Vbscript attached be modified to save it to a new location as CSV possibly and it was working but stopped and I don't know why. I appreciate and I'm slowly getting closer to a solution. — , Jul 02 '17 at 06:45
@SiddharthRout Cheers dude. I got it fully working. Thanks for your help. — , Jul 02 '17 at 13:52

VBA macro to create a table between a filtered range upon closing

0 Answers0