VBA :FORMAT AS TABLE BASED ON ENTERED DATA

[Marty]

Hi all,

I tried to figure this out with no sucess.



I can give a range when I want to do my format as table, but my documents dont have always the same total of line. Sometimes i can have 150 lines, sometimes 20 lines. My Macro has been setup on 150 lines but i need to play with it each time. What is the macro formula so it can do the table based on the data on the line so no need to play with it all the time ? Like an autofit table.






I would like also know if there is way that end of columns "F" and "H" it could do a sum, again the total lines is not always the same, so it has to do the calculation based on the end selection of the table.


I have attached my DOC_SAMPLE document that you can try out. Macro name is PL_AX2

Any help is welcome and more than appreciated.

thanks.

[p45cal]

try changing to:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A$1").CurrentRegion, , xlYes).Name = "Table1"

[Marty]

try changing to:

ActiveSheet.ListObjects.Add(xlSrcRange, Range("A$1").CurrentRegion, , xlYes).Name = "Table1"

It did work and i thank you very much ! would you know how to chose from a table ?

I am looking for the format below with the black and white and the lines for the columns

16659

Would you know how i could get a sum for column F and H for each time i run this macro ?


many thanks

[Marty]

I was able to find the grid as wanted now, but for the calculation of the 1 sum of the column F and a another sum of columns H , it would be appreciated to know what would be the formula.

thanks again, it is appreciated.

[Marty]

After reading the older posts i was able to find what i was looking for, thanks to all who took the time to read me and help. i appreciate.
thanks

[p45cal]

replace part of the code with this:
Application.PrintCommunication = True 'existing line
With ActiveSheet.ListObjects.Add(xlSrcRange, Range("A1").CurrentRegion, , xlYes)
.Name = "Table1"
.ShowTotals = True
.ListColumns("F").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("G").TotalsCalculation = xlTotalsCalculationSum
.ListColumns("H").TotalsCalculation = xlTotalsCalculationNone
.TableStyle = "TableStyleMedium15"
End With
Range("Table1[#All]").Select 'existing line